Mysql query optimisation - 50k+ Rows

I'm querying 50k+ rows with this query and it is taking 1.2759 seconds. What do you think is the best way to optimise it. The data updates every second or so but I could cache it for say 20 seconds. I've been looking into memcached for this, but is there a way to optimize this query? There is already indexes on most of the columns.

SELECT `p`.`id` as performance_id, `p`.`performers`, `t`.`name` as track_name, `p`.`location`, `p`.`es_id`
FROM (`performances` p)
JOIN `users` u ON `p`.`user_id` = `u`.`id`
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', '11', '13') 
AND `p`.`track` IN ('5', '15', '2', '3', '8', '6', '12', '4', '1') 
AND `p`.`type` IN ('1', '0', '2') 
ORDER BY `p`.`created` desc
LIMIT 12 

Update: So here is the output from my EXPLAIN plan.

+----+-------------+-------+--------+----------------------------------------+---------+---------+----------------------------------+-------+-----------------------------+
| id | select_type | table | type   | possibl开发者_Python百科e_keys                          | key     | key_len | ref                              | rows  | Extra                       |
+----+-------------+-------+--------+----------------------------------------+---------+---------+----------------------------------+-------+-----------------------------+
|  1 | SIMPLE      | p     | range  | user_id,track,prop,flagged,status,type | status  | 2       | NULL                             | 27440 | Using where; Using filesort |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY,id                             | PRIMARY | 3       | staging.p.user_id                |     1 | Using index                 |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY                                | PRIMARY | 4       | staging.p.track                  |     1 | Using where                 |
+----+-------------+-------+--------+----------------------------------------+---------+---------+----------------------------------+-------+-----------------------------+
3 rows in set (0.00 sec)

MySQL can only use a single index at each stage of the query. You have many single-column indexes, but only one of them will be used for your query. To better make use of indexes, try adding a multi-column index. As an example, you could try this 4-column index:

(status, type, prop, track)

Both the columns you include in your index and the order of them will affect the speed of the query. It's hard to tell the best order without knowing the distribution of your data, so feel free to experiment a bit. You can add more than one index, run the query to see which index was actually used, then remove the other unused indexes.

Do what duffymo said, explain, and create indexes. This will do 99% of the stuff you need If you want to make it even faster you can also do this:

1. Enable query cache

   query_cache_size = 268435456

   query_cache_type=1

   query_cache_limit=1048576

2. You may also increase the table cache size

Run EXPLAIN PLAN on your query and look for table scans. If you find one, think about adding indexes.