5

Does MySQL choose an execution plan for a given query taking into account what indexes are available, or does it first choose an execution plan and then uses indexes if they are available?

Motivation: I want to decide which indexes would be useful. I have a number of typical queries. So I can imagine two possible strategies:

  • If the plans do not depend on available indexes: see the execution plans of those queries and add indexes useful for these plans, or
  • If the plans depend on available indexes: add all possible indexes (a lot!), see the plans for the queries, and remove unused indexes.

My DB is not very large, so I can afford playing with indexes. I currently use InnoDB, but I can switch to MyISAM or another if needed.

1 Answer 1

4

MySQL's optimizer looks only at what indexes are available.

There is an exception in 5.6: If you have a subqueries such as FROM ( SELECT ... ) JOIN ( SELECT ... ) ..., there are no indexes on the temporary tables that are created. This used to lead to terrible performance. Now, the optimizer will try out various indexes, and create the best one for the tmp table. I suspect it only looks for single-column indexes, not "compound" indexes

InnoDB is getting the most attention these days. MyISAM does not have any tricks up its sleeve.

Try out my Cookbook for creating the best index, given a SELECT. It may save you some false starts.

13
  • I've looked at your cookbook, looks very promising. Why don't you convert it in a real program? Say, online: the user pastes several queries on your webpage and gets a list of recommended indexes (maybe with some additional constraints). Or don't you know of such a tool? Or doesn't MySQL do it? Looks pretty trivial: make EXPLAIN believe that there are all possible indexes (a finite number anyway) and see how it would act, then actually make those indexes. Can't be done in MySQL? Is it a good idea to make all indexes, run EXPLAIN, and delete those unused? Commented Mar 21, 2015 at 3:58
  • Yeah, I am pondering a web page. I haven't found a parser for SELECTs, so I have to write that first. I can't use EXPLAIN without having the table and sample data. Even then, the distribution of the data can make a difference. And then come different versions, with different syntax, etc.
    – Rick James
    Commented Mar 21, 2015 at 4:43
  • There are tools that try to discover which indexes are "unused". They are flawed because they look at which indexes are used over some period of time. But they can miss that important job that runs once a week and must have this otherwise unused index.
    – Rick James
    Commented Mar 21, 2015 at 4:45
  • If my UI tries to connect to your database to do then EXPLAIN, then there are all sorts of firewall, connection, security, etc issues. I consider that to be a non-starter. I could "give" you a stored procedure that you have to install and run. That's what I do with pivot and lat/lng. (See the links at the bottom of that blog.)
    – Rick James
    Commented Mar 21, 2015 at 4:48
  • And thanks for the favorable comment about my cookbook. My ego an I appreciate it.
    – Rick James
    Commented Mar 21, 2015 at 4:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.