After we know MySQL filesort algorithms, we can optimize MySQL order by through adjust the system variables and write better sql queries. The methods are as follows:
- In order to make MySQL to use indexes (index sort) rather than an extra sorting phase (file sort), we can build the appropriate indexes and write the appropriate sql queries.
- MySQL will use the modified filesort algorithm if the total size of the extra columns in the sort tuple does not exceed the value of the max_length_for_sort_data system variable. This algorithm can be much more efficient, so we can increase the variable value if we have enough space.
- MySQL might use much more temporary storage space for a filesort than we expect, because it allocates a fixed-size record for each tuple it will sort. These records are large enough to hold the largest possible tuple, including the full length of each VARCHAR column. So we need to make N of VARCHAR(N) only as large as necessary when we design schema.
- Use less RAM per row by declaring columns only as large as they need to be to hold the values stored in them. For example, CHAR(16) is better than CHAR(200) if values never exceed 16 characters.
- MySQL will uses the original filesort algorithm when any of the required columns is a BLOB or TEXT column (even those not used for the ORDER BY). We can use SUBSTRING() to convert such columns to types that can work with the modified filesort algorithm. When MySQL has to sort on such columns, it uses only a prefix and ignores the remainder of the values. This is because it has to allocate a fixed-size structure to hold the values and copy the prefix from external storage into that structure. We can specify how large this prefix should be with the max_sort_length variable.
- Increase the sort_buffer_size variable value (Used for both filesort algorithms).
- Increase the read_rnd_buffer_size variable value (Used for the original filesort algorithm).
- Change the tmpdir system variable to point to a dedicated file system with large amounts of free space. The variable value can list several paths that are used in round-robin fashion; we can use this feature to spread the load across several directories.
- If we need only a specified number of rows from a result set, use a LIMIT clause in the query, rather than fetching the whole result set and throwing away the extra data (MySQL optimizes LIMIT Queries since MySQL 5.6).