I read "How My SQL Optimizes Order By"
(http://dev.mysql.com/doc/mysql/en/order-by-optimization.html),
and I'm aware of its severe limitation due to the
one-index-per-table rule.
However, even when I follow all the roles, I'm still
getting filesort instead of using the index order.
So I created an index as follows:
CREATE INDEX IX_MyTable on MyTable (ColA, ColB, ColC,
ColD DESC)
Then I run the following query:
SELECT ColA
FROM MyTABLE
WHERE ColB = 'CONSTANT' AND ColB = 1 AND ColC = 'C'
ORDER BY ColdD DESC
This is obviously a covered query by the index
IX_MyTable. MySQL isn't supposed to touch the table.
But MySQL insists on filesort! (I can this that int he
Extra column of the EXPLAIN command).
Why?
I'm abiding by all the rules that should let MySQL use
the index order instead of perform an expensive quick
sort on a large memory buffer.
I appreciate your feedback!
__________________________________
Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search.
http://info.mail.yahoo.com/mail_250
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
