Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For some reason, this query:

EXPLAIN SELECT * FROM biz as b INNER JOIN listings as l ON b.id = l.biz_id
  WHERE l.pub_id = 14 AND b.cat_id=310 ORDER BY l.level DESC, l.random DESC LIMIT 5;

says it would use a filesort:

+----+-------------+-------+--------+---------------+------------------+---------+----------------------------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys | key              | key_len | ref                                    | rows | Extra                           |
+----+-------------+-------+--------+---------------+------------------+---------+----------------------------------------+------+---------------------------------+
|  1 | SIMPLE      | b     | ref    | [...]         | cat_id           | 4       | const                                  | 1160 | Using temporary; Using filesort |
|  1 | SIMPLE      | l     | ref    | [...]         | biz_id           | 4       | b.id                                   |    1 | Using where                     |
+----+-------------+-------+--------+---------------+------------------+---------+----------------------------------------+------+---------------------------------+

I have tried setting up several indexes on listings that include biz_id, pub_id, level and random, but no need to bore you with my failings; what would be the proper index to use to avoid a filesort in this query? Or is it impossible?

share|improve this question
    
I'm using MySQL 5 –  mltsy May 28 '13 at 15:48
    
Only solution i found to do this is to create a temporary view i database and use that view as a table. creating view .. create or replace view [view name] [cols][query] –  user30221 Nov 6 '13 at 12:55
    
Hm, have you tried a composite index on (pub_id, level, random, biz_id)? –  ypercube Nov 6 '13 at 12:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.