开发者

MySql join after select - Slow query

I have 100,000 rows, and this query can sometimes take over 2 seconds. I'm trying to optimize it and had success with indexing the 'created' field with DESC. I'm trying to further optimise this query and am wondering if this query actually joins the 'tracks' on all 100,000 rows rather than just the 12 I actually need. Could this be causing a slower query?

Query:

SELECT `p`.`id` as performance_id, `p`.`performers`, `t`.`name` as track_name, `p`.`location`, `p`.`fms_id`
FROM (`performances` p)
JOIN `tracks` t ON `p`.`track` = `t`.`id`
WHERE (p.status = 1 OR (p.status != 2 && p.flagged < 3))
AND `p`.`prop` IN ('1', '2', '3开发者_开发技巧', '4', '5', '6', '8', '10', '11', '13') 
AND `p`.`track` IN ('17', '9', '5', '15', '2', '3', '8', '6', '12', '4', '1') 
AND `p`.`type` IN ('1', '0', '2') 
ORDER BY `p`.`created` desc
LIMIT 0, 12

Explain:

1   SIMPLE  p   index   track,prop,flagged,status,type  created_desc    5   NULL    239 Using where
1   SIMPLE  t   eq_ref  PRIMARY PRIMARY 4   database_name.p.track   1   Using where


Use temp table for list of tracks and join that temp table with main query instead of using 'IN' clause. Generally, avoid IN clauses, as the number of items within the list goes up the query plan might change.

Creating index on 'track' column of performances table would give the desired behavior (query won't join the 'tracks' on all 100,000 rows.)

http://apps.ycombinator.com/item?id=2206406

http://dbaspot.com/sybase/240012-plan-change-clause-number-set-elements-print.html

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜