Why is mysql using filesort when i order my selected rows with ORDER BY x?
My table looks likes this:
CREATE TABLE `test` (
`sdf` varchar(100) NOT NULL,
`sdf33` varchar(100) NOT NULL,
KEY `sdf_2` (`sdf`),
FULLTEXT KEY `sdf33` (`sdf33`),
FULLTEXT KEY `sdf` (`sdf`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
When running
EXPLAIN SELECT *
FROM `test`
ORDER BY sdf
mysql says it's using filesort, why? What do I need to change for it to not use filesort?
The FULLTEXT index does not preseve ordering and can't be used for ORDER BY.
But even if you had a BTREE index on sdf, it most probably would not be used too, since it's generally more expensive to to the table lookups in a loop than scan the table sequentially and sort it.
The index would be of use for ORDER BY / LIMIT:
CREATE INDEX ix_test_sdf ON test (sdf);
SELECT *
FROM test
ORDER BY
sdf
LIMIT 10
The LIMIT has a certain threshold after which the optimizer would prefer a filesort.
You can force the optimizer to use the index:
SELECT *
FROM test FORCE INDEX (ix_test_sdf)
ORDER BY
sdf
if you really don't want to have a filesort.
Your fulltext index on sdf can't be used for an ORDER BY operation (they can only be used for MATCH() ... AGAINST queries) which is why it's using a filesort on the table.
The solution would be to add another regular index on sdf in addition to the fulltext index.
