mysql indexes and optimizations, Using where; using temporary; using filesort

Question

Which Tables and columns should have indexes? I have an index on flow_permanent_id and entry_id, but it seems to be using filesort?

What can I do to optimize this?

mysql> explain SELECT COUNT(*) AS count_all, entry_id AS entry_id FROM `votes` WHERE `votes`.`flow_permanent_id` = '4fab490cdc1c82cfa800000a' GROUP BY entry_id;
+----+-------------+-------+------+----------------------------------+----------------------------------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys                    | key                              | key_len | ref   | rows | Extra                                        |
+----+-------------+-------+------+----------------------------------+----------------------------------+---------+-------+------+----------------------------------------------+
|  1 | SIMPLE      | votes | ref  | index_votes_on_flow_permanent_id | index_votes_on_flow_permanent_id | 74      | const |    1 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+----------------------------------+----------------------------------+---------+-------+------+----------------------------------------------+
1 row in set (0.00 sec)

Answer 1

To avoid the filesort, you'll want a composite index on (flow_permanent_id, entry_id) so that MySQL can use the index for both the WHERE and the GROUP BY.

Answer 2

First of all - use COUNT(1) instead of COUNT( * ), in some cases that may improve performance. Never use COUNT(*).

Second: it looks like you index is used, it's listed in the 'key' column of EXPLAIN output. Your "GROUP BY" is the thing that's causing filesort. Usually it's ORDER BY that's the culprit, but I've seen GROUP BY can do that as well.

Hope this helps.