Analysis of MySQL Index Usage Cases and Best Practices

Preparation: create a test table and insert sample data.

drop table if exists test;
create table test(
  id int primary key auto_increment,
  c1 varchar(10),
  c2 varchar(10),
  c3 varchar(10),
  c4 varchar(10),
  c5 varchar(10)
) ENGINE=INNODB default CHARSET=utf8;

insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

Creating indexes and analyzing their usage across multiple cases using EXPLAIN output.

Case 1 shows that changing the order of index columns does not affect the EXPLAIN result for constant equality queries because MySQL optimizer rewrites the query, though writing SQL in index order is recommended.

Case 2 demonstrates that when a range condition appears, the index column to the right of the range becomes ineffective, as reflected by increased key_len.

Case 2.1 confirms that if the range is on the last indexed column (c4), all preceding columns are still used.

Case 2.2 illustrates that a range on the leftmost column (c1) causes the whole index to be ignored, leading to a full table scan; a covering index can mitigate this.

Cases 3‑4 explore how index order interacts with ORDER BY and how swapping columns can trigger Using filesort.

Case 5 shows that breaking the left‑most prefix (c4) makes the index unusable for GROUP BY, resulting in only the first column being used.

Overall summary emphasizes the best‑left‑prefix rule, the impact of range conditions, order‑by considerations, and common pitfalls such as Using temporary and Using filesort.