Re: Indexing: on WHERE columns or ORDER BY columns?

[Daniel Nichter]

Including col3 in the index will avoid the filesort. Here is an example:
-- Before with only an index on (col1, col2) ...
mysql> EXPLAIN SELECT * FROM foo WHERE col1 = 10 and col2 = 100 ORDER BY col3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: foo
type: ref
possible_keys: col1_2
key: col1_2
key_len: 10
ref: const,const
rows: 1
Extra: Using where; Using filesort

-- And after with an index on (col1, col2, col3) ...
mysql> CREATE INDEX col1_2_3 ON foo (col1, col2, col3);
Query OK, 100000 rows affected (0.43 sec)
Records: 100000 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT * FROM foo WHERE col1 = 10 and col2 = 100 ORDER BY col3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: foo
type: ref
possible_keys: col1_2,col1_2_3
key: col1_2_3
key_len: 10
ref: const,const
rows: 1
Extra: Using where