我需要索引哪些字段?
问题描述:
当我运行一个查询:我需要索引哪些字段?
select categories.name as CatName, items.name as ItemName, item_options.price as Price from categories
left join items on categories.id = items.category_id
left join item_options on items.id = item_options.item_id
Where categories.takeawayID = 55276
它需要很长时间才能得到结果:273行中的一组(3分14.56秒)
它看起来像我需要的字段添加索引?但哪一个?
我尝试添加使用SELECT查询说明,我不明白是什么意思:
mysql> Explain select categories.name as CatName, items.name as ItemName, item_options.price as Price from categories
-> left join items on categories.id = items.category_id
-> left join item_options on items.id = item_options.item_id
-> Where categories.takeawayID = 55276;
+----+-------------+--------------+------+-------------------------------------------+---------------+---------+-----------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+-------------------------------------------+---------------+---------+-----------------------+--------+-------------+
| 1 | SIMPLE | categories | ALL | NULL | NULL | NULL | NULL | 14086 | Using where |
| 1 | SIMPLE | items | ref | category_id_2,category_id_3,category_id_4 | category_id_2 | 4 | menu.categories.id | 9 | |
| 1 | SIMPLE | item_options | ALL | NULL | NULL | NULL | NULL | 197401 | |
+----+-------------+--------------+------+-------------------------------------------+---------------+---------+-----------------------+--------+-------------+
答
作为一个经验法则,你应该对任何处于join
,where
,group by
或order by
添加索引部分。
这通常意味着名称中包含id
的所有东西都应该获得索引。
WAIT ...服务器崩溃了吗?我怀疑这是一个索引问题。关于你的OP你应该添加索引到categories.id和item_options.item_id – Khez 2011-04-08 11:36:26
你没错,它没有崩溃.... 273行集(3分钟14.56秒) – user622378 2011-04-08 11:43:45
添加2个索引,然后[优化表](http:///dev.mysql.com/doc/refman/5.1/en/optimize-table.html)。然后告诉我结果^^ – Khez 2011-04-08 11:48:58