Re: Very slow sort on Order By for MySQL query.

[Rick James]

This is another example of where 'prefix' indexing is next to worthless.

Can you change these
`fld1` varchar(1024) NOT NULL,
KEY `fld1` (`fld1`(1000)),
to these?
`fld1` varchar(1000) NOT NULL,
KEY `fld1` (fld1),

EXPLAIN SELECT ...
will demonstrate the differences.

Suppose you have
fld VARCHAR(2)
INDEX(fld(1))
and values (in this order in the 1-char index)

fld = ab, index entry = a
fld = bd, index entry = b
fld = cx, index entry = c
fld = cm, index entry = c
fld = ca, index entry = c
fld = de, index entry = d

and were doing
SELECT fld FROM tbl ORDER BY fld LIMIT 3;
It would have to read at least 5 rows to get the right values (ab, bd, ca). But deciding that it could finish after 5 rows instead of the entire table is rather complex, so it does not have that optimization.