MySQL Index and Sorting Mechanism: Filesort and Index Sort Deep Dive

Two Sorting Methods Overview

filesort Sorting Method

Used when index cannot be used for sorting:

  1. Data retrieval phase
  2. Sort processing phase
  3. Result return phase

index Sorting Method

Utilizing the ordered nature of the index itself:

  • Directly uses index
  • No additional overhead
  • Significant performance advantage

Conditions

  • ORDER BY clause must exactly match index column order
  • Sort direction must be consistent with index definition

filesort Algorithm Explained

Two-pass Sorting

  1. First disk scan: Only reads sorting fields and row pointers
  2. Sort phase: Sorts in sort buffer
  3. Second disk scan: Table lookup to read complete data

Single-pass Sorting

  1. Single disk scan: Reads all columns at once
  2. Memory sorting
  3. Result return

Optimization Suggestions

-- Avoid using SELECT *
SELECT id, name, create_time FROM large_table ORDER BY create_time DESC;

-- Increase sort buffer size
SET sort_buffer_size = 4 * 1024 * 1024;

EXPLAIN Analysis

Using filesort

Indicates file sort operation is used.

Using index

Indicates query uses covering index, the ideal situation.

Examples

-- Can use index
EXPLAIN select id from wzk_user order by id;

-- Uses filesort
EXPLAIN select id from user_info order by username;

Relationship Between Index and Sorting

Situations Where Index Cannot Be Used for Sorting

  1. Inconsistent sort direction
  2. Field order mismatch
  3. Mixed use of ASC and DESC
  4. Contains non-indexed fields

ASC DESC

  • ASC: Ascending order
  • DESC: Descending order