How to use EXPLAIN to * predict MySQL query performance?

Question

I help maintain a program that is essentially a read-only friendly interface for a large and complex MySQL database - the program creates special SELECT queries from the user's login, sends queries to the database, receives the results, processes them and displays them well to the user.

I would like to add some form of reasonable / heuristic prediction for the expected performance of the constructed query - sometimes users inadvertently make queries that will inevitably take a very long time (because they will return huge result sets or because they “go against the grain” of how the database is indexed ), and I would like to show the user some "somewhat reliable" information / guess how long the request will take. It doesn’t have to be perfect if it doesn’t become so bad and often because of the fuss with reality that it causes the effect of a “wail of the wolf” when users learn to ignore it ;-) Based on this information, the user can decide to go for coffee (if the score is 5-10 minutes), go to dinner (if it’s 30-60 minutes), kill the request and try something else (perhaps more stringent restrictions on the information they request), etc. etc.

I am not very familiar with the MySQL EXPLAIN statement - I see a lot of information about how to use it to optimize a query or DB schema, indexing, etc., but not much about how to use it for my more limited purpose - just make a forecast, taking the database as the given one (of course, if the forecasts are reliable enough, I can eventually switch to their use and choose between alternative forms that the request may require, but, what for the future: at the moment, I would very happy just to to show performance for users for the above purposes).

Any pointers ...?

Answer 1

EXPLAIN will not give you any indication of how long the request will take. At best, you can use it to guess which of the two queries may be faster, but if one of them is clearly poorly written, then even that will be very difficult.

You should also know that if you use subqueries, even starting EXPLAIN can be slow (almost as slow as the query itself in some cases).

As far as I know, MySQL provides no way to estimate the time it takes to complete a query. Could you record the time that each request needs to be completed and then build an estimate based on the history of past similar queries?

Answer 2

I think that if you want to create something reliable enough from this, you should build a statistical model from the table sizes and broken components of the EXPLAIN result, correlated with the query processing time. Trying to build a query runtime predictor based on thinking about the contents of EXPLAIN will simply spend too little time giving embarrassingly bad results before it is refined to an indefinite usefulness.

Answer 3

MySQL EXPLAIN has a column named Key . If there is something in this column, this is a very good sign, it means that the query will use the index.

Queries that use indexes are usually safe to use, since they were probably intended by the database designer when he designed the database.

However

There is another field called Extra . This field sometimes contains the text using_filesort .

This is very bad. This literally means that MySQL knows that the query will have a result set larger than the available memory, and therefore will begin to replace the data on the disk to sort it.

Conclusion

Instead of predicting the request time, just look at these two indicators. If using_filesort request, deny the user. And depending on how strictly you want to be, if the request does not use any keys, you should also deny it.

More on MySQL EXPLAIN Statement Results