Philip, I recommend using the MySQL STRAIGHT JOIN and USE INDEX clauses to manually force the best plan.
You could also use innodb_table_monitor to check that the key value set cardinality estimates are approximately right for the tables h, pt, and p. But to put it the other way, how could the optimizer know beforehand the cardinality of the h,p partial join versus the pt,p partial join? Regards, Heikki Innobase Oy --- See http://www.innodb.com for the latest news about InnoDB Order commercial MySQL/InnoDB support at https://order.mysql.com/ >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
