I've got a query that's not using an index and I don't understand why. Here's a stripped down version of what the tables look like:
create table t1 ( id int(10) unsigned not null auto_increment, x1 date not null, ... 8 other small, fixed fields primary key (id), key search_x1 (x1) ) type=innodb;
create table t2 ( id int(10) unsigned not null, x2 bigint(20) unsigned default null, ... 10 other small, fixed fields key join_t1 (id), key search_x2 (x2) ) type=innodb;
Table t1 has about 60 million rows and t2 about 70 million. Cardinality for x1 and x2 values is high, with no more than maybe 10,000 hits for x2 values and 20,000 for x1 values.
These queries run very quickly (usually subsecond):
select somefields from t1 where x1 = somedate select somefields from t2 where x2 = somenumber select somefields from t1, t2 where t1.id = t2.id and t1.x1 = somedate
This query runs painfully slow (usually several minutes):
select somefields from t1, t2 where t1.id = t2.id and t2.x2 = somenumber;
Using EXPLAIN, the first three queries above all report "using where; using index" for search_x1 or search_x2, whereas this last query only reports "using where". Why would this query not use the search_x2 index?
Suggestions?
Looks like an optimizer bug, very possibly InnoDB specific. Try ALTER TABLE TYPE=MyISAM for the affected tables to see if it makes a difference - if it does, this enough evidence that it's a bug, and Heikki (the InnoDB developer) will be very interested in fixing it.
-- Sasha Pachev Create online surveys at http://www.surveyz.com/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
