Skip navigation links
8.2.1.4 How to Avoid Full Table Scans »
Section Navigation      [Toggle]

8.2.1.3. Optimizing LIMIT Queries

If you need only a specified number of rows from a result set, use a LIMIT clause in the query, rather than fetching the whole result set and throwing away the extra data.

MySQL sometimes optimizes a query that has a LIMIT row_count clause and no HAVING clause:

  • If you select only a few rows with LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan.

  • If you use LIMIT row_count 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. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set.

  • When combining LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count unique rows.

  • In some cases, a GROUP BY can be resolved by reading the key in order (or doing a sort on the key) and then calculating summaries until the key value changes. In this case, LIMIT row_count does not calculate any unnecessary GROUP BY values.

  • As soon as MySQL has sent the required number of rows to the client, it aborts the query unless you are using SQL_CALC_FOUND_ROWS.

  • LIMIT 0 quickly returns an empty set. This can be useful for checking the validity of a query. When using one of the MySQL APIs, it can also be employed for obtaining the types of the result columns. (This trick does not work in the MySQL Monitor (the mysql program), which merely displays Empty set in such cases; instead, use SHOW COLUMNS or DESCRIBE for this purpose.)

  • When the server uses temporary tables to resolve the query, it uses the LIMIT row_count clause to calculate how much space is required.

As of MySQL 5.6.2, the optimizer more efficiently handles queries (and subqueries) of the following form: 

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

That type of query is common in web applications that display only a few rows from a larger result set. For example: 

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;

The sort buffer has a size of sort_buffer_size. If the sort elements for N rows are small enough to fit in the sort buffer (M+N rows if M was specified), the server can avoid using a merge file and perform the sort entirely in memory by treating the sort buffer as a priority queue:

  • Scan the table, inserting the select list columns from each selected row in sorted order in the queue. If the queue is full, bump out the last row in the sort order.

  • Return the first N rows from the queue. (If M was specified, skip the first M rows and return the next N rows.)

Previously, the server performed this operation by using a merge file for the sort:

  • Scan the table, repeating these steps through the end of the table:

    • Select rows until the sort buffer is filled.

    • Write the first N rows in the buffer (M+N rows if M was specified) to a merge file.

  • Sort the merge file and return the first N rows. (If M was specified, skip the first M rows and return the next N rows.)

The cost of the table scan is the same for the queue and merge-file methods, so the optimizer chooses between methods based on other costs:

  • The queue method involves more CPU for inserting rows into the queue in order

  • The merge-file method has I/O costs to write and read the file and CPU cost to sort it

The optimizer considers the balance between these factors for particular values of N and the row size.

Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices

User Comments

Posted by Ian Storms on August 11 2006 7:44pm[Delete] [Edit]

When one is using a LIMIT attribute in their query ,
it's often fallowed by an OFFSET. These two togeather
are often used in pagination ( paging of results ) as in.

select * from thetable limit 10 offset 0

To find out how many rows would be available if one had not
used the LIMIT / OFFSET, one would alter the statement
above to look like this

select SQL_CALC_FOUND_ROWS * from thetable limit 10 offset 0

The return from both commands looks the same, so to get the
row count you need to issue a fallowup query

select FOUND_ROWS()

Posted by Ian Storms on April 21 2007 10:20pm[Delete] [Edit]

So one wants to search for a particular record/row in
the database table and calculate an initial offset
value to feed into the typical pagination routines.

While there may be better ways to do this , what I cobbled
together was this little bit of MySql syntax that seems to work just fine on my little 40,000 row table.

set @row=-1;select foo.Row,foo.company_name from (select @row:= @row+1 AS Row ,company_name from TheCompanyDatabaseTable ) AS foo where foo.company_name='whatever';

Given the fact I searched in vein for this answer , I figured I'd post the answer I hacked up so it will
be here the next time I need it.

Posted by Manthan Shah on August 27 2010 10:16am[Delete] [Edit]

It is also used when there is looping of MYSQL "SELECT" queries and sub-queries are returning more than 1 rows

Example
SELECT xml.resource_id,xml.identifier,xm.member_id,xm.username,DATE(registertime) as member_since,
(SELECT country_id FROM xmember_profile WHERE member_id=xm.member_id)as country_id,
(SELECT shopping_for FROM xmember_profile WHERE member_id=xm.member_id) as shopping_for FROM `xmember` xm
INNER JOIN xmember_location xml ON xm.member_id=xml.member_id WHERE
(SELECT country_id FROM xmember_profile WHERE member_id=xm.member_id)="99"

will throw an error while

SELECT xml.resource_id, xml.identifier, xm.member_id, xm.username,DATE(registertime) as member_since,
(SELECT country_id FROM xmember_profile WHERE member_id=xm.member_id LIMIT 1) as country_id,
(SELECT shopping_for FROM xmember_profile WHERE member_id=xm.member_id LIMIT 1) as shopping_for FROM `xmember` xm
INNER JOIN xmember_location xml ON xm.member_id=xml.member_id
WHERE (SELECT country_id FROM xmember_profile WHERE member_id=xm.member_id LIMIT 1)=99

will work

Add your own comment.