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] |__/ --------------------------------------------------------------------- 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
