ORDER BY, and certain group functions
MIN, etc.) may require that data be sorted
before being returned to the user. You can detect that a sort is
required from the
tag in the
Extra column of the
EXPLAIN statement output, as shown
in Example 21-19.
SELECT * FROM customers ORDER BY contact_surname, contact_firstname Explain plan ------------ ID=1 Table=customers Select type=SIMPLE Access type=ALL Rows=101999 Key= (Possible= ) Ref= Extra=
If there is sufficient memory, the sort can be performed without having to write intermediate results to disk. However, without sufficient memory, the overhead of the disk-based sort will often dominate the overall performance of the query.
There are two ways to avoid a disk-based sort:
Create an index on the columns to be sorted. MySQL can then use the index to retrieve the rows in sorted order.
Allocate more memory to the sort.
These approaches are described in the following sections.
If an index exists on the columns to be sorted, MySQL can use the index to avoid a sort. For instance, suppose that the following index exists:
CREATE INDEX i_customer_name ON customers(contact_surname, contact_firstname)