MySQL Manual Version 5.0.20-MySQL Optimization (4) (1) (2)_PHP Tutorial

To improve the speed of ORDER BY, you must first see if MySQL can use indexes instead of additional sorting processes. If you can't use indexes, you can try following the following strategy:

Increase the value of sort_buffer_size.

Increase the value of read_rnd_buffer_size.

Modify tmpdir so that it points to a dedicated file system with a lot of free space. If using MySQL 4.1 or newer, this option allows multiple paths in a circular format. Paths are separated by colons (':') on Unix and semicolons (';') on Windows, NetWare and OS/2. You can use this feature to evenly distribute the load among several directories. Note: These paths must be directories distributed on different physical disks, not different directories on the same physical disk.

By default, MySQL will also sort all GROUP BY col1, col2, ... queries, just like ORDER BY col1, col2, ... queries. If you explicitly include an ORDER BY clause with the same field list, MySQL does not lose speed when optimizing it because the sorting will always occur. If a query includes GROUP BY but wants to avoid the overhead of sorting the results, you can cancel the sorting by using ORDER BY NULL. For example:

INSERT INTO foo

SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

7.2.10 How MySQL optimizes LIMIT

In some cases, MySQL will do something different when encountering a query that uses LIMIT row_count but does not use HAVING

Processing:

If you only use LIMIT to get a few records, MySQL sometimes uses an index, but more usually it does a full table scan.

If LIMIT row_count is used with ORDER BY, MySQL will stop sorting after finding row_count records instead of sorting the entire table.