Optimize sorting query

Question

i couldn't find a way to optimize the following Query:

SELECT * 
FROM tbl 
WHERE type='51' AND `start`<='2012-01-19' 
ORDER BY end DESC 
LIMIT 5

I've tried by indexing each column in a separate index (type,start,end), and all of them in the same index, but MySQL keeps telling me that needs to do a filesort

Is this query just impossible to optimize?

How many rows are in the table?

Matt Fenwick
– Matt Fenwick

2012-01-19 19:39:51 +00:00
Commented Jan 19, 2012 at 19:39 — Matt Fenwick
– Matt Fenwick, Commented Jan 19, 2012 at 19:39
The table contains about 40.000 Rows

jmserra
– jmserra

2012-01-19 19:45:18 +00:00
Commented Jan 19, 2012 at 19:45 — jmserra
– jmserra, Commented Jan 19, 2012 at 19:45

zerkms · Accepted Answer · 2012-01-19 19:58:27Z

1

Yes, as long you have range comparison in WHERE and sort by another field - mysql cannot use index for sorting.

It could if you had WHERE type='51' ANDstart='2012-01-19' ORDER BY end DESC or WHERE type='51' ANDstart<= '2012-01-19' ORDER BY start DESC

http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html -- and here is a chapter relevant to your problem

edited Jan 19, 2012 at 19:58

answered Jan 19, 2012 at 19:44

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cdeszaq · Accepted Answer · 2012-01-19 19:43:03Z

0

It depends greatly on the column types and what's all in the table, but all you should really need are indexes on type, start, and end columns.

For an extra boost, you can make an index across type and start.

answered Jan 19, 2012 at 19:43

cdeszaq

31.4k27 gold badges123 silver badges176 bronze badges

1 Comment

zerkms Over a year ago

Separate indexes won't help. Mysql can only use one index at a time

DaveShaw · Accepted Answer · 2012-01-19 20:56:04Z

0

My first thought is (*) is expanding into rows that are not indexed.

If not, you will most certainly benefit from a Multiple-Column Index.

I would experiment and learn to see how to create the order of columns in the index definition with the lowest cardinality (approx unique combinations) of the columns.

edited Jan 19, 2012 at 20:56

DaveShaw

52.9k18 gold badges118 silver badges142 bronze badges

answered Jan 19, 2012 at 19:48

Jacob Sanford

1346 bronze badges

3 Comments

zerkms Over a year ago

"I would experiment and learn to see how to create the order of columns with the lowest cardinality (approx unique combinations) of the columns." --- and it has nothing to do with original question

Jacob Sanford Over a year ago

Would you have preferred my response to be "No, it is not impossible to optimize" : only answering the 'actual' question?

zerkms Over a year ago

I have already given an answer. And your answer contains irrelevant information. Yes, it is better to say just NO instead of giving advices that aren't useful in any way

jmserra · Accepted Answer · 2012-01-20 07:23:15Z

0

Thanks for all your answers, it made me learn a lot about the issue.

And finally i got it! Looks like this is actually possible to Optimize, or at least to remove that using filesort in the EXPLAIN sentence.

Here's the indexes i used:

    KEY `start` (`start`),
    KEY `typeend` (`type`,`end`)

Now executing:

    EXPLAIN  SELECT * 
    FROM tbl 
    WHERE type='51' AND `start`<='2012-01-19' 
    ORDER BY end 
    DESC LIMIT 5

Leads to:

SIMPLE  tbl ref start,type,typeend  typeend 5   const   19  Using where

answered Jan 20, 2012 at 7:23

jmserra

1,3065 gold badges18 silver badges34 bronze badges

2 Comments

newtover Over a year ago

How long does in take now compared to the time in the question?

jmserra Over a year ago

Well, the timing is almost the same because here we're dealing with a really small amount of data (40K rows), the whole table sizes less than 3MB, but i thought it was a great example to ask, because i have many more cases like this with bigger and complex tables, and now i'll be able to start dealing with them

newtover · Accepted Answer · 2012-01-20 07:38:41Z

0

I would recommend the following:

add index (type, end, start)

rewrite the query:

SELECT *
FROM (
  SELECT id -- `id` is the primary key
  FROM tbl
  WHERE type='51' AND `start`<='2012-01-19' 
  ORDER BY end DESC
  LIMIT 5) as ids
JOIN tbl
USING (id); -- `id` is the primary key

answered Jan 20, 2012 at 7:38

newtover

32.3k11 gold badges89 silver badges89 bronze badges

Collectives™ on Stack Overflow

Optimize sorting query

5 Answers 5

Comments

1 Comment

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related