mysql如何分组索引_分组依据/排序的MySQL索引

See query below. What index should I create on the table so the query will use the index and avoid using temporary and filesort? I've tried many different combinations of indices and read advice here, but I can't seem to figure it out. My explain either says Using Where (no index), or Using Where Using Temporary, Using Filesort

Here is a simplified query. All columns are Integers.

SELECT c1, Sum(c2)

FROM table

WHERE c3 IS NOT NULL

AND c4 = 2011

AND c5 = 0

AND c6 In (6,9,11)

GROUP BY c1

解决方案

This should help you. Rewrite your query as follows:

SELECT c1, Sum(c2)

FROM table

WHERE c4 = 2011

AND c5 = 0

AND c6 In (6,9,11)

AND c3 IS NOT NULL

GROUP BY c1

Now create a composite index on columns (c4, c5, c6) with the columns IN THAT ORDER. The columns in your index should appear in the same order as the columns in your WHERE clause. Otherwise the index will not work. The selectivity of this index is narrow enough that a filesort on the temporary table (for the group by) should be fast.

The reason to move c3 to the end of the query is the following. As an example, let us assume that c3 can take values between 0 and 100 (or it can be NULL). If you run a "IS NOT NULL" query, then Mysql needs to traverse almost all of the B-Tree index except for the edges that correspond to NULL. Therefore, MySQL decides that a full table scan is an easier option than walking through all the different paths in the index. On the other hand, you will see that if your query was an "IS NULL" and your index was (c3, c4, c5, c6) then Mysql will infact use this index. This is because in this case Mysql only needs to traverse the part of the index tree corresponding to the NULL value.

The kind of indexes MySQL needs is very much dependent on the query in question. Creating indexes on all the columns, as @louis suggested, is NOT a good idea!