Hi
First, I am pretty sure that what the manual says is that MySQL only
USES one index per request, not one index per table. I would try adding an
index that starts with ColC (and maybe only ColC). Your index starts with
ColA but you do not use it in your WHERE portion of the statement. This
could confuse the index selection process and have MySQL decide to use no
index in some situations. Mike
----- Original Message -----
From: "Homam S.A." <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
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]
