Thanks Mike for your feedback. Unfortunately the HANDLER statement has the same limitation, i.e. it doesn't allow you to specify range criteria for index key parts. You have to specify constants in the index_name (...) specifier.
--- mos <[EMAIL PROTECTED]> wrote: > At 08:47 PM 2/22/2005, you wrote: > >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] > > __________________________________ Do you Yahoo!? Yahoo! Mail - 250MB free storage. Do more. Manage less. 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]
