CODEWITHSUNDEEP
mysqlsql
apscience
apscience

Reputation: 7243

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

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.

Upvotes: 0

Views: 839

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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.

Upvotes: 1

Luis Carvalho
Luis Carvalho

Reputation: 101

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.

Upvotes: 0

juergen d
juergen d

Reputation: 204746

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.

Upvotes: 4

Related Questions