Philip,
InnoDB does not give an exact number of rows
to the query analyzer. A problem is that
in a multiversioned database different transactions
will see a different number of rows in the table,
and a single count of rows cannot be kept.
It is an eternal problem in query optimization.
Errors in estimates will toggle query plans
to another, and some queries will run faster,
others slower.
The estimation algorithm in InnoDB tends to
underestimate the number of rows in big index
trees or subtrees. I changed the code now so that
it multiplies the estimate by 2 if the tree height
is > 1. The change is already in the upcoming
3.23.41. I do not know if this change will toggle
your query plan.
A better solution is to make more dives into the
tree to estimate the number of rows. Currently
only two dives are made, at the sides of the
index tree. This is on my TODO list, but
improvement in join optimization will not be that
big because errors in other factors are bigger:
for example, if the table fits in the buffer pool,
a search down the index tree runs in about 10
microseconds, while a search which requires
a disk seek takes 10 milliseconds.
Another solution is to implement ANALYZE
TABLE.
A 1000-fold error in query time estimates
is very common in SQL databases.
The classic trick to guide the query optimizer
of SQL is to put a column inside a function.
For example,
WHERE col1 = 'asadsd' AND col2 = 5;
can be changed to
WHERE SUBSTRING(col1, 0, 128) = 'asadsd' AND col2 + 0 = 5;
Then MySQL cannot use the WHERE condition,
and the query plan may toggle. In your case
there was an index 'running'. Maybe you can
prevent its use this way?
Regards,
Heikki
http://www.innodb.com
Copied message:
....................
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 shiftback.
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 InnoDBtables):
+-------+--------+--------------------------+---------+---------+-----------
---------- \
+------+-----------------------------+ | 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
