mysql> explain  SELECT menu_name FROM menu_links WHERE expanded != 0 GROUP BY menu_name;
+----+-------------+------------+-------+---------------+------------------------+---------+------+------+--------------------------+
| id | select_type | table      | type  | possible_keys | key                    | key_len | ref  | rows | Extra                    |
+----+-------------+------------+-------+---------------+------------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | menu_links | index | NULL          | menu_plid_expand_child | 106     | NULL | 5214 | Using where; Using index | 
+----+-------------+------------+-------+---------------+------------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> alter table menu_links add index expanded (expanded);
Query OK, 5069 rows affected (0.74 sec)
Records: 5069  Duplicates: 0  Warnings: 0

mysql> explain  SELECT menu_name FROM menu_links WHERE expanded != 0 GROUP BY menu_name;
+----+-------------+------------+-------+---------------+----------+---------+------+------+----------------------------------------------+
| id | select_type | table      | type  | possible_keys | key      | key_len | ref  | rows | Extra                                        |
+----+-------------+------------+-------+---------------+----------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | menu_links | range | expanded      | expanded | 2       | NULL |    2 | Using where; Using temporary; Using filesort | 
+----+-------------+------------+-------+---------------+----------+---------+------+------+----------------------------------------------+

Comments

robertdouglass’s picture

robertdouglass commented
Issue tags: +Performance

subscribbe

irakli’s picture

irakli commented

Using good-old DISTINCT instead of Group By can further optimize by removing the "filesort":

mysql> explain  SELECT DISTINCT menu_name FROM menu_links WHERE expanded != 0;
+----+-------------+------------+-------+---------------+----------+---------+------+------+------------------------------+
| id | select_type | table      | type  | possible_keys | key      | key_len | ref  | rows | Extra                        |
+----+-------------+------------+-------+---------------+----------+---------+------+------+------------------------------+
|  1 | SIMPLE      | menu_links | range | expanded      | expanded | 2       | NULL |   27 | Using where; Using temporary | 
+----+-------------+------------+-------+---------------+----------+---------+------+------+------------------------------+
1 row in set (0.00 sec)
david strauss’s picture

david strauss
he/him
commented

The index should be on (expanded, menu_name), and the query should be rewritten to use greater than, less than, or equality instead of "!=". Agreed on the use of DISTINCT. If we do all that, we should eliminate the temp table, too.

multiplextor’s picture

multiplextor commented
Status: Active » Closed (won't fix)

Closed. The reason: expired.

multiplextor’s picture

multiplextor commented
Issue summary: View changes

added code tags so you can read this