Here is the script to create table:
CREATE DATABASE IF NOT EXISTS sailors;
USE sailors;
DROP TABLE IF EXISTS reserves;
DROP TABLE IF EXISTS sailors;
DROP TABLE IF EXISTS boats;
CREATE TABLE sailors
(
sid INTEGER NOT NULL ,
sname VARCHAR(20) NOT NULL ,
rating INTEGER NULL ,
age DECIMAL(5,2) NULL,
CONSTRAINT sailors_pk PRIMARY KEY (sid)
);
CREATE TABLE boats
(
bid INTEGER NOT NULL ,
bname VARCHAR(20) NOT NULL ,
color VARCHAR(10) NOT NULL ,
CONSTRAINT boats_pk PRIMARY KEY (bid)
);
CREATE TABLE reserves
(
sid INTEGER NOT NULL ,
bid INTEGER NOT NULL ,
day DATE NOT NULL ,
CONSTRAINT reserves_pk PRIMARY KEY (sid,bid,day)
, foreign key (sid) references sailors (sid)
, foreign key (bid) references boats (bid)
);
INSERT INTO sailors VALUES(22, 'Dustin', 7, 45.0);
INSERT INTO sailors VALUES(29, 'Brutus', 1, 33.0);
INSERT INTO sailors VALUES(31, 'Lubber', 8, 55.5);
INSERT INTO sailors VALUES(32, 'Audy', 8, 25.5);
INSERT INTO sailors VALUES(58, 'Rusty', 10, 35.5);
INSERT INTO sailors VALUES(64, 'Horatio',7, 35.0);
INSERT INTO sailors VALUES(71, 'Zorba', 10, 16.0);
INSERT INTO sailors VALUES(74, 'Horatio',9, 35.0);
INSERT INTO sailors VALUES(85, 'Art', 3, 25.5);
INSERT INTO sailors VALUES(95, 'Bob', 3, 63.5);
INSERT INTO boats VALUES(101, 'Interlake', 'blue');
INSERT INTO boats VALUES(102, 'Interlake', 'red');
INSERT INTO boats VALUES(103, 'Clipper', 'green');
INSERT INTO boats VALUES(104, 'Marine', 'red');
INSERT INTO boats VALUES(105, 'Clipper', 'white');
INSERT INTO reserves VALUES(22, 101, DATE '2012-10-10');
INSERT INTO reserves VALUES(22, 102, DATE '2012-10-10');
INSERT INTO reserves VALUES(22, 103, DATE '2012-08-10');
INSERT INTO reserves VALUES(22, 104, DATE '2012-07-10');
INSERT INTO reserves VALUES(31, 102, DATE '2012-10-11');
INSERT INTO reserves VALUES(31, 103, DATE '2012-06-11');
INSERT INTO reserves VALUES(31, 104, DATE '2012-12-11');
INSERT INTO reserves VALUES(64, 101, DATE '2012-05-09');
INSERT INTO reserves VALUES(64, 102, DATE '2012-08-09');
INSERT INTO reserves VALUES(74, 103, DATE '2012-08-09');
Here is the script with the EXPLAIN statement and the result of it.
EXPLAIN SELECT * FROM sailors ORDER BY rating;
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, sailors, , ALL, , , , , 10, 100.00, Using filesort
Now I am adding indexes and expect the EXPLAIN statement result to change afterwards:
CREATE INDEX rating ON sailors(rating);
CREATE INDEX age ON sailors(age);
Now I run the EXPLAIN statement and see that I changed nothing:
EXPLAIN SELECT * FROM sailors ORDER BY rating;
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, sailors, , ALL, , , , , 10, 100.00, Using filesort
Why did nothing changed in the EXPLAIN statement? What I expected is some performance improvement.
Using a secondary index (such as INDEX(rating)) involves bouncing between the index BTree and the data BTree. This was determined to be slower than simply reading all the data, then sorting it. The cutoff varies, but is somewhere around 20% of the table -- that is, if you had a WHERE clause that filtered down to less than 20% of the data (and both the WHERE and the ORDER BY could be handled together), then the Optimizer would probably use the index.
The term "filesort" is a misnomer. It does not necessarily involve a "file" -- it is often done in RAM.