MYSQL Optimizing LIMIT Queries

If you want to specify the number of data rows for the query, use the LIMIT clause in the query statement instead of getting all the data row result sets and then removing the useless data.

MySQL sometimes optimizes queries with the Limit keyword without having a clause :

1: If you select very few row datasets with the limit clause, MySQL uses the index instead of the full table scan.

2: If a combination of limit and order by queries is used,MySQL stops the sort when it satisfies limit limits, not all data sort. If you use index to sort, the process is fast, but if you go to filesort, all result sets that match that query (except limit) are fetched, before the limit clause data row is satisfied. Most (all) of the data is sorted (that is, all rows of data that meet the criteria are found, and then the first few data that satisfies the limit are found and then stopped).

3: An order by query with and without limit return sets may be in a different order , as described below:

4: If you combine the limit and distinct keywords, MySQL stops immediately when it finds the only data row for Row_count.

5: In some cases, thegroup by is able to read the order of the key, at which point the limit Row_count can restrict the group by value that does not have to be computed.

6: When the client receives the specified number of rows, the query is interrupted unless sql_calc_found_rows is used.

7:limit 0 Returns the empty set directly, which can be used to check whether the query is legitimate.

8: When the service uses a temporary table, the limit clause is used to calculate how much space is required .

When there are many rows in the order by column that have the same value , MySQL server returns the row data in any order, in other words, with no certainty in the order of the sort results for the non-order by column .

Mysql> SELECT *  fromRatingsORDER  bycategory;+----+----------+--------+|Id|Category|Rating|+----+----------+--------+|  1 |        1 |    4.5 ||  5 |        1 |    3.2 ||  3 |        2 |    3.7 ||  4 |        2 |    3.5 ||  6 |        2 |    3.5 ||  2 |        3 |    5.0 ||  7 |        3 |    2.7 |+----+----------+--------+

Mysql> SELECT *  fromRatingsORDER  byCategory LIMIT5;+----+----------+--------+|Id|Category|Rating|+----+----------+--------+|  1 |        1 |    4.5 ||  5 |        1 |    3.2 ||  4 |        2 |    3.5 ||  3 |        2 |    3.7 ||  6 |        2 |    3.5 |+----+----------+--------+

If you want to determine the order in which the set will be returned (preferably with a unique column) when the limit is not used, as follows: If the ID is unique, you can use this:

Mysql> SELECT *  fromRatingsORDER  bycategory, id;+----+----------+--------+|Id|Category|Rating|+----+----------+--------+|  1 |        1 |    4.5 ||  5 |        1 |    3.2 ||  3 |        2 |    3.7 ||  4 |        2 |    3.5 ||  6 |        2 |    3.5 ||  2 |        3 |    5.0 ||  7 |        3 |    2.7 |+----+----------+--------+

Mysql> SELECT *  fromRatingsORDER  byCategory, ID LIMIT5;+----+----------+--------+|Id|Category|Rating|+----+----------+--------+|  1 |        1 |    4.5 ||  5 |        1 |    3.2 ||  3 |        2 |    3.7 ||  4 |        2 |    3.5 ||  6 |        2 |    3.5 |+----+----------+--------+

SELECT  from ORDER  by [DESC] [M,] N

if the number of orders n is exactly in the sort buffer, then the service can avoid file merging and treat the sort buffer as a priority queue :

1: Scan the table and insert the selected rows into the queue, if the queue is full and the last one is removed.

2: The first n rows are then returned, and if there is a skip m, then the M-row is skipped, and then the N-Row records are returned.

previously used processing methods :

1: Scan the table, repeat the following steps until the end

2: Enter select row until sort buffer is full.

3: Write the first n rows to buffer, and then merge the first n rows into the file .

4: sorts the merged files and returns the first n rows .

the cost of scanning the table is the same as the queue and file merge, so the optimizer chooses it according to other costs :

1: The queue method uses many CPUs to insert into the queue .

2: merging files will use IO to read and write files, cpu to sort .

The optimizer balances between the number of rows and the different value N.

MYSQL Optimizing LIMIT Queries