CODEWITHSUNDEEP
mysqlsql
endyourif
endyourif

Reputation: 2202

Unexpected empty result set using limit with order by

I have a table with millions of records and I've been running this query once a day for over 500 days and just recently it stopped working.

The query looks as follows:

SELECT id FROM table
ORDER BY column1 desc, column2 desc, column3 desc
LIMIT 522, 1

This returns an empty result set. However, if I change the limit to anything less than 500 it works fine.

If I remove the limit and do a COUNT(id) with the order by, it returns a count of the multi-million row table.

Upvotes: 0

Views: 280

Answers (3)

Andrew
Andrew

Reputation: 7788

It is really difficult to diagnose these issues remotely and very often you can find more info in MySQL logs -- these are very helpful diagnosing abnormal behavior.

Also, try running your query like this:

SELECT id FROM
(SELECT id FROM table
ORDER BY column1 desc, column2 desc, column3 desc) r
LIMIT 522, 1

Upvotes: 0

endyourif
endyourif

Reputation: 2202

Not sure exactly how it occurred, but after optimizing the table it began working again...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I can only imagine some sort of time-out or error occurring. The order by and limit should be returning rows, if they are there.

First, check to see if you are getting an error. If you are calling from an application, this requires checking the error status.

For a time-out, you might consider adding an index on table(column1 desc, column2 desc, column3 desc, id). This should radically improve the performance of the query.

Upvotes: 1

Related Questions