----- Original Message ----- From: "Homam S.A." <[EMAIL PROTECTED]> To: <[email protected]> Sent: February 22, 2005 8:12 PM Subject: Avoiding filesort #2
> 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] > > > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.300 / Virus Database: 266.1.0 - Release Date: 2005-02-18 > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
