Even if I used ColA in the query, it still uses filesort if any keypart uses something other than an equal operator, like a range, IN operator, IS NOT NULL, IfNull(), etc.
Rearranging the composite index to make the sort column the first one won't help because:
1) It's not part of the WHERE clause, so MySQL will have to scan the whole index to find matches on the other key parts.
2) The query optimizer won't use it (reporting NULL in the possible keys), even if I used FORCE INDEX (). It'd rather use a singlular index created on ColD instead.
In addition, if I took ColD and put it in another table, it won't use it for sorting unless I'm sorting on the join column AND ColD.
For example, this query will use filesort instead of the order of an index created on Y (ColA, ColD):
SELECT X.* FROM X JOIN Y ON X.ColA = Y.ColB ORDER BY Y.ColD
It will only use the index order if I included both keys of the index in the query:
SELECT X.* FROM X JOIN Y ON X.ColA = Y.ColB ORDER BY Y.ColA, Y.ColD
But this is obviously not what I want.
This is a huge problem with multi-million-row tables because I'm gonna have to live with either table scans or quick sorts of large memory buffers, and both of which are evil and performance/scalability killers.
How did you guys solve this sorting problem?
It's a severe limitation of MySQL. I know that 5.x could use more than one index per table and could solve this problem, but it's still in Alpha stage, too risky for production use.
Try looking at Handler http://dev.mysql.com/doc/mysql/en/handler.html which creates a cursor into a MISAM/Innodb table. It will traverse the table using the index without sorting and without creating huge buffers. It is a bit difficult to understand at first but it is fast. Remember to fetch 100 to 1000 rows at a time. You don't want to fetch just 1 row from each call.
Mike
--- Mike OK <[EMAIL PROTECTED]> wrote:
> 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 > > > > > >
__________________________________ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
