Retrieving only a fixed number of rows in MySQL

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?

8,804 10 10 gold badges 56 56 silver badges 67 67 bronze badges asked Apr 6, 2012 at 19:29 5,540 5 5 gold badges 22 22 silver badges 23 23 bronze badges You can use LIMIT clause to select a set of rows.I don't understand whats your confusion? Commented Apr 6, 2012 at 19:31

From the documentation, if I understood right, LIMIT only applies once the result set has been built by the SELECT query. So the SELECT query goes and retrieves all matching rows from a table and once all matching rows have been retrieved only the number of rows specified in the LIMIT clause are returned. I was wondering if there was a way for the SELECT query to only retrieve the number of rows that I want it to.

Commented Apr 6, 2012 at 19:34

6 Answers 6

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 .

answered Apr 6, 2012 at 19:46 Keith Randall Keith Randall 23.2k 3 3 gold badges 36 36 silver badges 54 54 bronze badges

Thanks Keith. Well my SELECT query is something like this- select SQL_NO_CACHE * from readings_dev_1 where column between '2012-03-30' and '2012-04-06'. Will the query be smart enough to stop after 5000 rows if I was to place a LIMIT clause on the query?

Commented Apr 6, 2012 at 19:48

If I am not doing an ORDER BY on this select query, will I still need an index on this column? I understand the need for an index on a column if I am ordering it so that the query is smart enough to stop after 5000 rows. However, in my case I don't need an ORDER on the rows returned. So am I right in assuming that I won't need an index on that column and the query will still do the smart thing and stop after 5000 rows?

Commented Apr 6, 2012 at 19:59

It will still stop after 5000 rows, but it might need to scan a lot more rows than that from your table to find ones that satisfy your range condition. (That's a problem with or without the LIMIT. You'll want an index unless most of your rows satisfy the range condition.)

Commented Apr 6, 2012 at 20:05

True. That's okay. But if it knows to stop after 5000 rows, that's good enough for me. Thanks a lot Keith. Your response has helped a lot.

Commented Apr 6, 2012 at 20:19

 SELECT * FROM `your_table` LIMIT 0, 5000

This will display the first 5000 results from the database.

 SELECT * FROM `your_table` LIMIT 1001, 5000

This will show records from 1001 to 6000 (counting from 0).

51 4 4 bronze badges answered Apr 6, 2012 at 19:32 13.5k 37 37 gold badges 97 97 silver badges 159 159 bronze badges

Complexity of such query is O(LIMIT) (unless you specify order by).

It means that if 10000000 rows will match your query, and you specify limit equal to 5000, then the complexity will be O(5000).

answered Apr 6, 2012 at 19:33 Jarosław Gomułka Jarosław Gomułka 4,955 19 19 silver badges 25 25 bronze badges

That's a succinct way of putting it. Thanks. I was under the impression that SELECT would still be O(1000000) if I had that many matching rows, but then LIMIT would restrict the size of the result set returned to me. Thanks.

Commented Apr 6, 2012 at 19:37

@Jarosław Gomułka is right
If you use LIMIT with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. In either case, after the initial rows have been found, there is no need to sort any remainder of the result set, and MySQL does not do so. if the set is not sorted it terminates the SELECT operation as soon as it's got enough rows to the result set.

answered Apr 6, 2012 at 19:54 374 3 3 silver badges 7 7 bronze badges

The exact plan the query optimizer uses depends on your query (what fields are being selected, the LIMIT amount and whether there is an ORDER BY) and your table (keys, indexes, and number of rows in the table). Selecting an unindexed column and/or ordering by a non-key column is going to produce a different execution plan than selecting a column and ordering by the primary key column. The later will not even touch the table, and only process the number of rows specified in your LIMIT.

answered Apr 6, 2012 at 19:48 727 9 9 silver badges 22 22 bronze badges

Each database defines its own way of limiting the result set size depends on the database you are using.

While the SQL:2008 specification defines a standard syntax for limiting a SQL query, MySQL 8 does not support it.

Therefore, on MySQL, you need to use the LIMIT clause to restrict the result set to the Top-N records:

SELECT title FROM post ORDER BY id DESC LIMIT 50

Notice that we are using an ORDER BY clause since, otherwise, there is no guarantee which are the first records to be included in the returning result set.