On Mon, Dec 17, 2001 at 10:03:58PM -0600, Philip Molter wrote:
: Is there any upcoming fix for this recurring problem? The table
: handler is just giving poor data to the optimizer and the optimizer
: is making bad decisions because of it. It appears to come and go,
: depending on data that is in the table, what's been done, etc.
Here's some specific data, because writing e-mails isn't too bright
a thing to do late in the evening after struggling with a problem
for the better part of the night.
The crux of all this (for us, anyway) revolves around three tables
in our join sequence: percept, hosts, and perceptType.
percept.hid => hosts.hid (INNER)
percept.ptid => perceptType.ptid (INNER)
Other tables are joined in via LEFT JOINS, but they shouldn't (and
don't) affect the optimization because they're just being joined
in for ancillary data. So the three table structure is what I'm
most concerned with. Everything is keyed properly, and this query
optimizes correctly 100% of the time under MyISAM tables, and, for
that matter, under PostgreSQL (which this is also being designed
to run under, although Pg is worse for overall performance).
So here's some row counts from the data:
mysql> select count(*) from hosts; => 38
mysql> select count(*) from hosts where active=1; => 31
mysql> select count(*) from perceptType; => 26
mysql> select count(*) from perceptType
where runinterval is not null; => 12
mysql> select count(*) from percept; => 11305
mysql> select count(*) from percept where deleted=0; => 10647
mysql> select count(*) from percept p, hosts h
where p.hid=h.hid and h.active=1 and
p.deleted=0; => 9064
mysql> select count(*) from percept p, perceptType pt
where p.ptid=pt.ptid and
pt.runinterval is not null and
p.deleted=0; => 939
mysql> select count(*)
from percept p, perceptType pt, hosts h
where p.hid=h.hid and p.ptid=pt.ptid and
h.active=1 and
pt.runinterval is not null and
p.deleted=0; => 816
Here are some explains. These were taken literally 30 seconds
apart as I wrote this e-mail and the optimization switched.
Here is the EXPLAIN for a good match:
mysql> explain select count(*) from percept p, perceptType pt, hosts h where
p.hid=h.hid and p.ptid=pt.ptid and h.active=1 and pt.runinterval is not null and
p.deleted=0;
+-------+--------+------------------+---------+---------+---------+------+-------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
| |
+-------+--------+------------------+---------+---------+---------+------+-------------------------+
| pt | index | PRIMARY,ptid | ptid | 7 | NULL | 26 | where used;
|Using index |
| p | ref | deleted,hid,ptid | ptid | 4 | pt.ptid | 412 | where used
| |
| h | eq_ref | PRIMARY,active | PRIMARY | 4 | p.hid | 1 | where used
| |
+-------+--------+------------------+---------+---------+---------+------+-------------------------+
3 rows in set (0.00 sec)
Here is the EXPLAIN for a bad match:
mysql> explain select count(*) from percept p, perceptType pt, hosts h where
p.hid=h.hid and p.ptid=pt.ptid and h.active=1 and pt.runinterval is not null and
p.deleted=0;
+-------+--------+------------------+---------+---------+--------+------+-------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
| |
+-------+--------+------------------+---------+---------+--------+------+-------------------------+
| h | index | PRIMARY,active | active | 4 | NULL | 38 | where used;
|Using index |
| p | ref | deleted,hid,ptid | hid | 4 | h.hid | 502 | where used
| |
| pt | eq_ref | PRIMARY,ptid | PRIMARY | 4 | p.ptid | 1 | where used
| |
+-------+--------+------------------+---------+---------+--------+------+-------------------------+
3 rows in set (0.00 sec
Given everything seen here, is there any way to understand why
these poor decisions are being made? Not only is the first query
information clearly better, but the underlying table data (row
counts, etc.) clearly shows it to be better. I can't fathom how
InnoDB is passing the optimizer information that is this far off.
* Philip Molter
* Texas.net Internet
* http://www.texas.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
