CODEWITHSUNDEEP
mysqlmyisam
Marwelln
Marwelln

Reputation: 29413

MyISAM table is using filesort with ORDER BY, why?

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?

Upvotes: 0

Views: 603

Answers (3)

Quassnoi
Quassnoi

Reputation: 425471

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.

Upvotes: 3

Eric Petroelje
Eric Petroelje

Reputation: 60508

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.

Upvotes: 0

prcvcc
prcvcc

Reputation: 2230

add an index on sdf. and run the query again.

Upvotes: -1

Related Questions