SELECT p.*
FROM item p JOIN item_category c ON p.ItemCategory=c.Code
LEFT JOIN item_category r ON c.Pid=r.Code
LEFT JOIN item_category g ON r.Pid=g.Code
WHERE (p.ItemCategory='10002' OR c.Pid='10002' OR r.Pid='10002')
ORDER BY p.Description LIMIT 0,15;
表信息:
item: PRIMARY KEY (SN),ItemCategory字段建有索引,数据量:3014641笔
item_category: PRIMARY KEY(Code),Pid字段建有索引,数据量:98笔
问题:
上述SQL执行时间超36秒!!!
经排查,WHERE 条件
(1) 如果采用 WHERE (p.ItemCategory='10002' ),查询结果秒出;
(2) 如果采用 WHERE (p.ItemCategory='10002' OR c.Pid='10002'),查询耗时不足1秒;
(1) 如果采用 WHERE (p.ItemCategory='10002' OR c.Pid='10002' OR r.Pid='10002'),查询耗时超36秒。
请教SQL优化之道!