How MySQL optimizes LIMIT

How MySQL optimizes LIMIT

In some cases, when you use LIMITRow_countWithout HAVING,MySQLQueries are processed in different ways.

· If you use LIMIT to select only some rowsMySQL selectionWhen you perform a full table scan, it will use indexes in some cases.

· If you use LIMITRow_countAnd order,MySQLOnce the first sorting result is foundRow_countRows. The sorting ends, rather than sorting the entire table. If the index is used, it will be very soon. If you must sort files (filesort), you must select all rows that match the query without the LIMIT clause, and find 1stRow_countMost of the rows must be sorted. In either case, once a row is found, the other parts of the result are not sorted, and MySQL is not sorted.

· When combined with LIMITRow_countAnd DISTINCT,MySQLOnce foundRow_countAnd it will stop.

· In some cases, group by can be solved BY reading the key sequentially (or sorting the key), and then calculating the summary until the value of the keyword changes. In this case, LIMITRow_countNo unnecessary group by values will be calculated.

· As long as MySQL has sent the required number of lines to the customer, it will discard the query, unless you are using SQL _CALC_FOUND_ROWS.

· LIMIT 0 always returns an empty set quickly. This is useful for checking the query validity. When using the MySQL API, it can also be used to obtain the column type of the result column. (This technique does not work in MySQL Monitor. Only Empty set is displayed. show columns or DESCRIBE should be used ).

· Use LIMIT when the server uses a temporary table for queryRow_countClause to calculate the amount of space required.