Well, answering my own email, what I thought was a bug is not one at all. I was mistaken in thinking that MySQL paid any attention to the WHERE conditions when optimizing the join order beyond determining which keys are used for the join, correct?
This is really terrible for queries like mine where the query could be sped up by orders of magnitude if the join optimizer would just determine which table in the join to scan and which to do the key lookup on based on a more intelligent estimation of the number of rows from each table. It would have to go beyond looking at what keys are used in the join (since each of the tables in my query can be looked up by the same key) and account for the WHERE conditions placed on the tables in the join. Is there sufficient metadata to estimate rows coming from a table based on conditions placed on the attributes of that table? Where is it? Has anyone ever thought of coding this? Can anyone give me a place to start? eric. On Thu, Oct 18, 2001 at 10:40:43AM -0500, Eric wrote: > I am sending this again as I am desperate for some help and believe > this to be a signifigant bug if it actually is one...which it seems to > be. See below for examples. > > What is quite puzzling is MySQL's estimation of the number of rows > from each of the self-joins. The conditions on alias queryTable0 > actually refer to 1582 rows, and the conditions on alias queryTable1 > refer to 39 rows. Notice in the EXPLAIN below that when I flip around > the join order, MySQL thinks that 1152 (which is its estimation for > 1582) rows are coming from queryTable1, whereas with the original join > order, it thought 1152 rows were coming from queryTable0...this seems > like a bug to me since the conditions on those two aliases are the > same between the two queries. Only the "FROM index queryTable0, index > queryTable1" is flipped to "FROM index queryTable1, index queryTable0". > > SELECT DISTINCT queryTable0.num, queryTable0.value, queryTable1.value > FROM > index queryTable0, index queryTable1 WHERE > queryTable0.path=24 AND queryTable0.type="E" AND > queryTable1.path=27 AND queryTable1.type="E" AND > queryTable0.num=queryTable1.num AND > queryTable0.nvalue > 0.0 AND queryTable0.nvalue <= 900000.0 AND > queryTable1.nvalue > 140.0 AND queryTable1.nvalue <= 200.0; > > >+-------------+------+----------------------+------------+---------+------------+--------+------+-----------------------------+ > | table | type | possible_keys | key | key_len | > ref | rows | Extra | > >+-------------+------+----------------------+------------+---------+------------+--------+------+-----------------------------+ > | queryTable0 | ref | pathndx,numndx | pathndx | 4 | > const | 1152 | where used; Using temporary | > | queryTable1 | ref | pathndx,numndx | numndx | 4 | > queryTable0.num | 53 | where used | > >+-------------+------+----------------------+------------+---------+------------+--------+------+-----------------------------+ > 2 rows in set (0.01 sec) > > On Wed, Oct 17, 2001 at 04:04:21PM +0300, Michael Widenius wrote: > > We have done some modifications to optimizer in 4.0, but nothing that > > should affect this. > > > > What is the output from EXPLAIN if you swap the tables ? > > EXPLAIN of query with "FROM index queryTable1, index queryTable0": > > >+-------------+------+----------------------+------------+---------+--------------------+------+-----------------------------+ > | table | type | possible_keys | key | key_len | > ref | rows | Extra | > >+-------------+------+----------------------+------------+---------+--------------------+------+-----------------------------+ > | queryTable1 | ref | pathndx,numndx | pathndx | 4 | > const | 1152 | where used; Using temporary | > | queryTable0 | ref | pathndx,numndx | numndx | 4 | > queryTable1.num | 53 | where used | > >+-------------+------+----------------------+------------+---------+--------------------+------+-----------------------------+ > 2 rows in set (0.01 sec) > > > What is the output from "show create table 'index'" > > CREATE TABLE is: > > CREATE TABLE `index` ( > `indexnum` int(10) unsigned NOT NULL auto_increment, > `parent` int(10) unsigned NOT NULL default '0', > `path` int(10) unsigned NOT NULL default '0', > `type` char(1) NOT NULL default '', > `tagname` int(10) unsigned NOT NULL default '0', > `atrname` int(10) unsigned NOT NULL default '0', > `num` int(10) unsigned NOT NULL default '0', > `nvalue` double default NULL, > `value` mediumtext, > PRIMARY KEY (`indexnum`), > KEY `parentndx`(`parent`), > KEY `pathndx`(`path`), > KEY `tagnamendx`(`tagname`), > KEY `atrnamendx`(`atrname`), > KEY `numndx`(`num`), > ) TYPE=MyISAM MAX_ROWS=3153600000 PACK_KEYS=1 > > -- > _____ _ > | ____|(_) http://ir.iit.edu/~ej > | _| | | Page me via ICQ at > | |___ | | http://wwp.mirabilis.com/19022931 > |______/ | or by mailing [EMAIL PROTECTED] > |__/ > -- _____ _ | ____|(_) http://ir.iit.edu/~ej | _| | | Page me via ICQ at | |___ | | http://wwp.mirabilis.com/19022931 |______/ | or by mailing [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
