Actually with the query below it does avoid filesort, but once I use anything other than the equal operator (e.g. ColC > 5), it reverts back to filesort.
Any thoughts? --- "Homam S.A." <[EMAIL PROTECTED]> wrote: > 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] > > __________________________________ Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more. 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]
