MySQL Index for Group By / Order By

Question

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

Answer 1

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!