Missing index in menu_links table [#371521]

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

Comment #1

robertDouglass CreditAttribution: robertDouglass commented 12 February 2009 at 11:15

Issue tags:

+Performance

subscribbe

Comment #2

irakli CreditAttribution: irakli commented 12 February 2009 at 12:07

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)

Comment #3

David Strauss

he/him

CreditAttribution: David Strauss commented 20 February 2009 at 05:52

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.