A query that took less than a second under 3.23.44 started taking minutes when we upgraded to 4.0.3. The original query involved multiple tables including one merge table containing 10 subtables and millions of rows, but I've trimmed it down to a manageable test case where the merge table has only one subtable and 10 rows.
In the test case (see SQL below), there are three EXPLAIN statements. The first is for the problem query, from the merge table. Note that it incorrectly uses the index named 'files' even though the primary index included both fields from the WHERE clause. The second EXPLAIN shows the same query performed on the MyISAM table directly, without the merge. There the correct index is chosen. The third EXPLAIN shows a similar query on the merge table but selecting only one file_code value rather than a range. There the correct index is chosen again. We can work around the problem by adding "USE INDEX (PRIMARY)" to the queries, but this does look like a bug in the query optimization for 4.0. I hope this helps in tracking it down. ----- Begin SQL ----- CREATE TABLE files_snapshot_pm_01 ( fileset_id tinyint(3) unsigned NOT NULL default '0', file_code varchar(32) NOT NULL default '', fileset_root_id tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (fileset_id,file_code), KEY files (fileset_id,fileset_root_id) ) TYPE=MyISAM; INSERT INTO files_snapshot_pm_01 VALUES (2, '0000000111', 1), (2, '0000000112', 1), (2, '0000000113', 1), (2, '0000000114', 1), (2, '0000000115', 1), (2, '0000000116', 1), (2, '0000000117', 1), (2, '0000000118', 1), (2, '0000000119', 1), (2, '0000000120', 1); CREATE TABLE files_snapshot_pm ( fileset_id tinyint(3) unsigned NOT NULL default '0', file_code varchar(32) NOT NULL default '', fileset_root_id tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (fileset_id,file_code), KEY files (fileset_id,fileset_root_id) ) TYPE=MRG_MyISAM UNION=(files_snapshot_pm_01); # This query, with the merge table, incorrectly uses the # 'files' index: EXPLAIN SELECT * FROM files_snapshot_pm WHERE fileset_id = 2 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1\G # This query uses the primary index, as it should: EXPLAIN SELECT * FROM files_snapshot_pm_01 WHERE fileset_id = 2 AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1\G # This query, with the merge table again but selecting a # specific file_code value rather than a range, uses the # correct index: EXPLAIN SELECT * FROM files_snapshot_pm WHERE fileset_id = 2 AND file_code = '0000000115' LIMIT 1\G ----- End SQL ----- -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.org --------------------------------------------------------------------- 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
