Retrieving only a fixed number of rows in MySQL

mysql sql-limit
Sandman picture Sandman · Apr 6, 2012 · Viewed 61.8k times · Source

I am testing my database design under load and I need to retrieve only a fixed number of rows (5000)

I can specify a LIMIT to achieve this, however it seems that the query builds the result set of all rows that match and then returns only the number of rows specified in the limit. Is that how it is implemented?

Is there a for MySQL to read one row, read another one and basically stop when it retrieves the 5000th matching row?

Answer

Keith Randall picture Keith Randall · Apr 6, 2012

MySQL is smart in that if you specify a LIMIT 5000 in your query, and it is possible to produce that result without generating the whole result set first, then it will not build the whole result.

For instance, the following query:

SELECT * FROM table ORDER BY column LIMIT 5000

This query will need to scan the whole table unless there is an index on column, in which case it does the smart thing and uses the index to find the rows with the smallest column.

Related questions

How does MySQL process ORDER BY and LIMIT in a query?

I have a query that looks like this: SELECT article FROM table1 ORDER BY publish_date LIMIT 20 How does ORDER BY work? Will it order all records, then get the first 20, or will it get 20 records and order them by …

Read More
Is there a way to "limit" the result with ELOQUENT ORM of Laravel?

Is there a way to "limit" the result with ELOQUENT ORM of Laravel? SELECT * FROM `games` LIMIT 30 , 30 And with Eloquent ?

Read More
MySQL offset infinite rows

I would like to construct a query that displays all the results in a table, but is offset by 5 from the start of the table. As far as I can tell, MySQL's LIMIT requires a limit as well as an …

Read More