Index Usage in MySql InnoDb

Question

I wonder that the index usage in Mysql InnoDb Engine.

below is simplified example.

1. fields
   a , b , c , d

2. indexes

• a(pk)
• (a, b, d) (composite index)

3. Query
   Select ... Where a = 3 And b >= 5 And c = 4 Order by d Limit 10;

Q.1 in this case, how long index is used? (a , b ?) (a, b , d?)

Q.2 How can i optimise the index for this case?

Q.3 Is it okay to include a primary key (clustered index) in secondary indexes? Or is it better to compare the primary key field at the end of the WHERE clause?

Thanks.

Answer 1

Q1

Run your horses. Probably no index will be used for this query, but you can get the execution plan and verify.

Q2

The following index can improve performance. Again, verify the execution plan:

create index ix1 on t (a, c, b);

You can even try the "covering index":

create index ix1 on t (a, c, b, d);

For little bit of extra performance (at the cost of a heavier index).

Q3

In InnoDB the PK is always included in all secondary indexes. That's not the case with MyISAM, but this is not your use case.

Answer 2

It is rare that having the PK at the beginning of a secondary index is of any use. The one case where it might be useful is if all the columns needed by the entire SELECT are included in index. Your example was not that case. (Cf: "covering index")

The order of clauses AND'd together in WHERE does not matter. The order of columns in an INDEX does matter.

Aside from "covering", no index will completely optimize Where a = 3 And b >= 5 And c = 4 Order by d Limit 10. See Index Cookbook for an algorithm for finding the optimal index. As already suggested: (a,c,b) or (c,a,b).

(a, c, b, d) will help only if it is "covering". (What is in SELECT ...??

(a, b, d) may run faster, but it is risky. If the 10 desired rows are found soon, it will be fast. But if there are not a full 10 rows in the table, it will be a slow table scan.

"Using filesort" is a consequence of ORDER BY and/or GROUP BY. An index may have been useful, but the query could not have been completed using only the index.

Yes, the PK column(s) are always included in an InnoDB secondary key. But they will be tacked onto the end. Keep in mind that the order in the INDEX is important, and, as my cookbook indicates, = columns need to be listed first in the index definition.

"how can I cover WHERE and ORDER BY clause by using an index?" -- One more thing to finish answering that. Although a "covering" index may "cover" both clauses, it can't prevent the "filesort" because of the mixture of a "range" test (<) and ORDER BY.