CODEWITHSUNDEEP
mysqlcountindexinggroup-byfilesort
JZ.
JZ.

Reputation: 21877

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

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)

Upvotes: 3

Views: 1059

Answers (2)

Oleg K
Oleg K

Reputation: 51

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.

Upvotes: 1

Ami
Ami

Reputation: 1254

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.

Upvotes: 7

Related Questions