Retrieving only a fixed number of rows in MySQL

Question

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?

You can use LIMIT clause to select a set of rows.I don't understand whats your confusion? — Teja, Apr 06 '12 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. — Sandman, Apr 06 '12 at 19:34

score 28 · Accepted Answer · answered Apr 06 '12 at 19:46

28

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 06 '12 at 19:46

Keith Randall

22,422
1
32
53

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? – Sandman Apr 06 '12 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? – Sandman Apr 06 '12 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.) – Keith Randall Apr 06 '12 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. – Sandman Apr 06 '12 at 20:19

score 9 · Answer 2 · edited May 15 '19 at 17:44

9

 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).

edited May 15 '19 at 17:44

Stanislaw Romanski

3
2

answered Apr 06 '12 at 19:32

Teja

11,878
29
80
137

score 3 · Answer 3 · answered Apr 06 '12 at 19:33

3

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 06 '12 at 19:33

Jarosław Gomułka

4,727
16
24

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. – Sandman Apr 06 '12 at 19:37

score 3 · Answer 4 · answered Apr 06 '12 at 19:54

@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.

score 0 · Answer 5 · answered Apr 06 '12 at 19:48

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.

Vlad Mihalcea · Answer 6 · 2021-01-21T14:50:19.030

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.

Retrieving only a fixed number of rows in MySQL

6 Answers6

Linked

Related