Re: Not using index?

[Dr. Frank Ullrich]

Good Morning!

Sasha Pachev wrote:

Keith Thompson wrote:

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? 

I think it



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.


I don't agree. To me it looks like the join-Query needs to be supported 
by a concatenated index on t2 (id,x2) because if I remember correctly 
MySQL can use not more than 1 index per table.
I guess t1 would be the driving table (we didn't actually see the 
explain, didn't we?) so t2.id must be indexed to get the joined rows of 
t2 quickly. And then? Poor MySQL, x2 is not indexed and it has already 
used one index on t2, so this becomes slow in the end. That depends also 
on how many rows from t1 will be joined with rows from t2 via the id 
column but the number must be huge.

Another way would be to make t2 the driving table ("... t2 STRAIGHT_JOIN 
t1 ..." ) so that the ~10000 rows in the t2 result set (which will be 
built quickly via t2.x2 indexed lookups) will be joined very quickly via 
t1.id (the primary key). The only reason for this query to be slow could 
be the "sending data" stage.

Regards,
Frank.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]