Tuning ORDER and GROUP BY
GROUP BY
, ORDER BY
, and certain group functions
(MAX
, MIN
, etc.) may require that data be sorted
before being returned to the user. You can detect that a sort is
required from the Using filesort
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=Using filesort
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.
Creating an Index to Avoid a Sort
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)
MYSQL can use that index to avoid the sort operation shown in
Example 21-19. Example 21-20 shows the
output when the index exists; note the absence of the Using filesort
tag and that the
Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.