I've got a table with roughly 10000 rows that's being joined with
several other tables Ten-thousand rows isn't a whole lot, but it makes
a difference. With MyISAM, the query is optimized perfectly, exactly
how I would expect it to be optimized given our indexing scheme, but
when I switch the table type to InnoDB, it shifts the query to a much
more inefficient one. Sometimes, given enough database usage, internal
numbers must shift around, because it reverts to the old optimization
and we gain about 10% free CPU.
It appears that it has to do with the table information. While 'SHOW
TABLE STATUS' is supposed to give inaccurate data for InnoDB tables
concerning things like row count, I would expect the optimizer to still
get the correct information. However, the inefficient query searches
through all the rows in this 10000 row table because it thinks it only
has to look through half of them (which, if that were true, *would*
yield a more efficient query).
Is this a known issue, or is EXPLAIN returning the wrong information?
On the rare occasion the query explanation reverts to its old plan, we
get a significant reduction in resources, but I can't *make* it shift
back.
Here is the query explanation with the 10000 row table as a MyISAM
table (table p is the 10000 row table):
+-------+--------+--------------------------+---------+---------+---------------------+------+-----------------+
| table | type | possible_keys | key | key_len | ref
|| rows | Extra |
+-------+--------+--------------------------+---------+---------+---------------------+------+-----------------+
| h | ref | PRIMARY,active | active | 4 | const
|| 20 | Using temporary |
| p | ref | deleted,hid,running,ptid | hid | 4 | h.hid
|| 378 | where used |
| pt | eq_ref | PRIMARY | PRIMARY | 4 | p.ptid
|| 1 | where used |
| pth | eq_ref | PRIMARY | PRIMARY | 4 | p.pid
|| 1 | |
| sl | eq_ref | PRIMARY | PRIMARY | 4 | p.pid
|| 1 | |
| pdc | ref | PRIMARY | PRIMARY | 4 | p.pid
|| 13 | Using index |
| sd | eq_ref | PRIMARY | PRIMARY | 4 | pdc.dep_pid
|| 1 | |
| plf | eq_ref | PRIMARY | PRIMARY | 4 | p.pid
|| 1 | |
| ptt | eq_ref | PRIMARY | PRIMARY | 4 | p.ptid
|| 1 | |
| wft | eq_ref | PRIMARY | PRIMARY | 36 | pt.logtype,plf.type
|| 1 | |
+-------+--------+--------------------------+---------+---------+---------------------+------+-----------------+
Here it is with the 10000 row table as an InnoDB table (the only thing
that has changed is the table type of p; all other tables are InnoDB
tables):
+-------+--------+--------------------------+---------+---------+---------------------+------+-----------------------------+
| table | type | possible_keys | key | key_len | ref
|| rows | Extra |
+-------+--------+--------------------------+---------+---------+---------------------+------+-----------------------------+
| p | ref | deleted,hid,running,ptid | running | 1 | const
|| 4100 | where used; Using temporary |
| pt | eq_ref | PRIMARY | PRIMARY | 4 | p.ptid
|| 1 | where used |
| h | eq_ref | PRIMARY,active | PRIMARY | 4 | p.hid
|| 1 | where used |
| pth | eq_ref | PRIMARY | PRIMARY | 4 | p.pid
|| 1 | |
| sl | eq_ref | PRIMARY | PRIMARY | 4 | p.pid
|| 1 | |
| pdc | ref | PRIMARY | PRIMARY | 4 | p.pid
|| 13 | Using index |
| sd | eq_ref | PRIMARY | PRIMARY | 4 | pdc.dep_pid
|| 1 | |
| plf | eq_ref | PRIMARY | PRIMARY | 4 | p.pid
|| 1 | |
| ptt | eq_ref | PRIMARY | PRIMARY | 4 | p.ptid
|| 1 | |
| wft | eq_ref | PRIMARY | PRIMARY | 36 | pt.logtype,plf.type
|| 1 | |
+-------+--------+--------------------------+---------+---------+---------------------+------+-----------------------------+
Thank you in advance for any insights,
Philip
* Philip Molter
* DataFoundry.net
* http://www.datafoundry.net/
* [EMAIL PROTECTED]
---------------------------------------------------------------------
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
