Optimizing a SQL query by only looking in the last X rows (Not simply LIMIT)

Question

I would like to perform a SQL query across a very large table. However, I only need to look through the last 100,000 rows. Currently:

SELECT * FROM bigtable WHERE columna='foobar' ORDER BY id DESC LIMIT 10

I would like to do the same but make it only look in the last X ids.

EDIT: Selecting 100k rows first and then selecting it from that is not a solution. This selects the full previous 100k rows, even if more than 10 of the needed rows have been found. That makes it slower.

See also: [mysql select from n last rows](http://stackoverflow.com/q/573646/1935077) — Petr R., Aug 07 '13 at 10:44
@gladoscc you can do something like this in sql server select top 100000 columnname from bigtable where columna='foobar' order by id desc — zxc, Aug 07 '13 at 10:51
@Petr R.: That doesn't work in my case because it selects the full 100k rows even if it would have already found 10 without the limit. What I need is to be able to time out a query. — apscience, Aug 07 '13 at 10:53

juergen d · Answer 1 · 2013-08-07T10:48:37.123

4

Try

select * from 
(
  SELECT * 
  FROM bigtable 
  ORDER BY id DESC
  LIMIT 100000
) x
where columna = 'foobar' 
order by id desc
limit 10

SQLFiddle demo

And if your query is slow then add an index on columna.

edited Aug 07 '13 at 10:48

answered Aug 07 '13 at 10:43

juergen d

201,996
37
293
362

This query does exactly what you want. Read it again more carefully and give it a try – Bojangles Aug 07 '13 at 10:46
You edited the query, but this is actually slower.. Because usually it does not need to look through the full 100000 rows. – apscience Aug 07 '13 at 10:51
Then limit it to smaller amount of rows. It's called late row lookups, you can google it to read how and why it works. It's the only way to make it efficient, unless you know values of primary keys you need to inspect so you can limit the range of index being looked at. Also, make sure you configured your MySQL properly because if you run default config - no amount of query optimization will help you. – N.B. Aug 07 '13 at 11:26

score 1 · Answer 2 · answered Aug 07 '13 at 12:37

1

Create a composite index on bigtable(columna, id).

MySQL should be smart enough to seek to the value in columna in the index and the sort by the second key. This is explained in the documentation.

answered Aug 07 '13 at 12:37

Gordon Linoff

1,242,037
58
646
786

I am doing a order by on id, not columna. – apscience Aug 08 '13 at 07:13
@gladoscc . . . You are doing filtering on `columna` and ordering on `id`. That is why MySQL would use this index for both. – Gordon Linoff Aug 08 '13 at 11:07

score 0 · Accepted Answer · answered Aug 07 '13 at 11:26

In a database you cannot do a query that checks the "last x rows". A relational database does not guarantee that the rows are physically stored in a specific order. And therefore SQL will not allow you to express that. If you can translate that into an actual constraint based on the data contained in the rows then that would be possible to achieve.

Taking your example, the worst operation the database has to do is the sort of the full result set before returning the data. This is regardless of the limit clause, because only after you run through all the rows and sorted them do you know which rows have the highest ids.

However, if there is an index with columna and id, by that order, the database engine should use the index, which is sorted, to go through the rows much faster, resulting in a faster response time.

I have added an index with columna and id by that order, however the performance is still quite slow. Is there a way to make the query time out and return whatever it has after X seconds? — apscience, Aug 10 '13 at 04:53

Optimizing a SQL query by only looking in the last X rows (Not simply LIMIT)

3 Answers3

SQLFiddle demo