--- mos <[EMAIL PROTECTED]> wrote: > At 05:12 AM 2/23/2005, Homam S.A. wrote: > >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. > > > > Are you saying you can't reference column values > from another table to do a > table join? > On large tables we routinely don't use a table joins > in MySQL and opt > instead to write our own table sync procedures to go > through each table and > synchronize the rows as we move through the primary > table (they have an > index in common). This is much faster than doing > table joins. >
The joins aren't just for synching tables, but mostly for lookups. Sometimes the join interestion result is huge (multi-million), depending on how loose the user criteria is, and this result needs to be sorted to pull out the top several thousands, where the user can page through a web interface. I'm not sure if it's more efficient to take in a multi-million row result from the first table, manually join it with a second table, sort the result, and page through the top few thousands. I was hoping that I could take advantage of MySQL's index pres-sorting to do so, but obviously, in many cases, I can't. I can do so just fine with other servers, like MS SQL Server. > If you're not doing table joins, then why can't you > just build your Handler > command using a programming language (PHP, Perl, > etc) and insert whatever > constants you want? This is one of the options I will look at for the next release. It seems with MySQL you have dive in and tinker with its plumbing to get the most out of it. > > Mike Thanks! > > > >--- 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] > > __________________________________ 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]
