MySQL manual version 5.0.20-MySQL optimization (4) (1) (2)

To increase the order by speed, you must first check whether MySQL can use indexes instead of additional sorting procedures. If you cannot use an index, follow these policies:

Increase the value of sort_buffer_size.

Increase the value of read_rnd_buffer_size.

Modify tmpdir to point to a dedicated File System with a lot of available space. If you use MySQL 4.1 or update, this option allows multiple paths to use the loop format. Each path is separated by a colon (':') on Unix, and a semicolon (';') on Windows, NetWare, and OS/2 (';'). This feature can be used to evenly distribute loads to several directories. Note: These paths must be directories distributed on different physical disks, rather than different directories on the same physical disk.

BY default, MySQL also sorts all group by col1, col2,... queries, just like order by col1, col2,... queries. If it explicitly contains an order by clause with the same field list, MySQL will not lose the speed when optimizing it, Because sorting will always happen. If a query contains group by, but you want to avoid overhead of sorting results, you can use order by null to cancel sorting. For example:

Insert into foo

SELECT a, COUNT (*) FROM bar group by a order by null;

7.2.10 how does MySQL optimize LIMIT?

In some cases, MySQL uses LIMIT row_count but does not use HAVING.

The query will be processed differently:

If you only use LIMIT to retrieve a few records, MySQL sometimes uses indexes, but more often it is a full table scan.

If LIMIT row_count and order by are used together, MySQL will stop sorting after finding row_count records, rather than sorting the entire table.