Geek asks and answers

Mysql indexes and optimizations, use where; use of temporary; using filesort

What tables and columns should have indexes? I have an index on flow_permanent_id and entry_id, but does it seem to use 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)
+3
source share
2 answers

To avoid file management, you will need a composite index ( flow_permanent_id, entry_id) so that MySQL can use the index for both WHERE and GROUP BY.

+7
source

First of all, use COUNT (1) instead of COUNT (*), in some cases this can improve performance. Never use COUNT (*).

-: , , "" EXPLAIN. "GROUP BY" - , ort. ORDER BY, , , GROUP BY .

, .

+1

More articles:


All Articles