CODEWITHSUNDEEP
mysql
bradley
bradley

Reputation: 776

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

Upvotes: 0

Views: 493

Answers (1)

Bala
Bala

Reputation: 4547

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

Upvotes: 1

Related Questions