Hi!
>>>>> "Heikki" == Heikki Tuuri <[EMAIL PROTECTED]> writes:
Heikki> Monty,
Heikki> there is an SQL optimizer deficiency if a two-column index is used.
Heikki> (Added later: actually not, see the end of this email.)
Heikki> Ryan does the following query, for which there is a very good
Heikki> index on (parentmsgid, clean). But from the printout below we
Heikki> see that though MySQL uses the right key, it does not communicate
Heikki> the value of the second column to InnoDB (i.e., key_len is only 4, the
Heikki> length of an integer).
Heikki> The query:
Heikki> select * from messages where clean=0 and parentmsgid=0;
Heikki> should return 5 rows.
Heikki> Another strange thing is that when InnoDB returns rows to MySQL
Heikki> which MySQL apparently sieves away because 'clean' is '1' in them,
Heikki> the query seems to take hours. It should not take that long to sieve
Heikki> away maybe 1 million rows. I looked at the 45 MB long printout
Heikki> Ryan sent and and apparently InnoDB was not in a loop, it just sent
Heikki> a very large number of rows to MySQL, since parentmsgid = 0 in
Heikki> most rows. clean is '0' in only a few rows.
Heikki> I remember that a user complained 2 months ago on the mailing list
Heikki> about the same deficiency in the SQL optimizer when using 2-column
Heikki> indexes.
Heikki> Now I found the solution! For the following query the optimizer
Heikki> gives a 2-column search key:
Heikki> select * from messages where clean = '0' and parentmsgid = 0;
Heikki> Ok, Ryan, you can use the above formulation. The reason why
Heikki> the optimizer does not use the second column in the first query
Heikki> is that it probably considers type conversion '0' -> 0 a function,
Heikki> and generally optimizers ignore conditions like
Another reason is that anything in 'clean' that is not a number will be
regarded as 0. For example
clean='a' and parentmsgid=0 will also match the query.
In other words, MySQL can't use the whole index on this query, even if
it wanted.
Heikki> Well, mystery solved :). Except that why MySQL uses hours to
Heikki> sieve away the extraneous rows in the first query.
To be able to answer the question why:
SELECT COUNT(*) FROM table1 WHERE col1=0 and col2=0
Is slow, I would need to get an EXPLAIN for this query.
Heikki> Regards,
Heikki> Heikki
Heikki> http://www.innodb.com
<cut>
Regards,
Monty
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
