Are the MySQL query performance benefits of indices retained if a subset of the index columns are used in a query?

Question

Do I get to keep the performance and efficiency advantages of having an index setup for multiple columns on a MySQL table if I run a SELECT statement that queries some subset of those columns in the index?

So, if I have an index setup on columns A, B and C but my statement only queries for columns A and B, is that the same as having no index setup at all. Do I need to have another index setup exclusively for A and B to gain any performance benefits with queries?

Answer 1

See this answer: https://stackoverflow.com/a/20939127/2520738

Basically:

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

So basically, yes, if your index reads A, B, C from left to right, you can search on A, A and B, A and B and C. If you don't have single column indexes on B or C then no index will be used when they are searched individually.

Answer 2

Short answer to a generic question: It's depends

Long answer:

The DB build the explain plan based on the statistics of the table. basically the DB engine estimates how much it "effort" it takes for every operation the two main factors in this case are the indexed data size and distribution of the indexed data.

Data distribution If the first two columns data granularity is low (a few possible value for example values column A stands for gender column B stands for age) then there is a good chance that the optimizer will prefer to read the entire table rather then using the index. ** At this case adding an index only on A,B won't be useful either**

** Indexed data size ** Another factor is the size of data in column C. the size of data in column C effects directly on the index size. since reading the index tree also requires IO the bigger the index the so is the cost. lets assume that the data in column C is comment and the average comment size is 500 chars. the data may have lot's of possible values but the index is going to be a very large index. This may also cause the DB to prefer reading the entire table rather then using the index. ** At this case adding an index on A,B is useful **