multiple index is used only partially
Hello all,
Got this issue with the optimizer that refuses to use ALL columns on a multiple index. I've tried to rule out a bug (none seem applicable) so maybe it's something obvious I'm missing.
Summary:
The optimizer refuses to use ALL columns on a multiple index, even when in theory it could use them and the query WOULD do MUCH better with ALL columns.
How to repeat:
This is a ultra-simplified test case that samplifies the problem even with minimal data (on the production server there are PLENTY more rows)
-- SERVER INFO:
5.0.45-community-nt-log MySQL Community Edition (GPL)
-- TABLES (2)
create table test_rel(
lhs_id int unsigned not null
, rhs_id int unsigned not null
, primary key (lhs_id,rhs_id)
);
create table test_item(
id int unsigned not null auto_increment
, regtime datetime not null
, rel_id int unsigned not null
, primary key (id)
, index rel_regtime(rel_id,regtime)
);
-- SAMPLE DATA
insert into test_rel(lhs_id,rhs_id) values (1,2);
insert into test_rel(lhs_id,rhs_id) values (1,3);
insert into test_item(rel_id,regtime) values (2,'2007-05-01');
insert into test_item(rel_id,regtime) values (2,'2007-05-02');
insert into test_item(rel_id,regtime) values (2,'2007-05-03');
insert into test_item(rel_id,regtime) values (3,'2007-05-01');
insert into test_item(rel_id,regtime) values (3,'2007-05-02');
insert into test_item(rel_id,regtime) values (3,'2007-05-03');
-- Show optimizer when it has got both rel_id and regtime needed for "rel_regtime" INDEX on "test_item" TABLE:
<code>
EXPLAIN SELECT i.* from test_item i where i.rel_id=2 AND i.regtime>='2007-05-02';
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
| 1 | SIMPLE | i | range | rel_regtime | rel_regtime | 12 | NULL | 1 | Using where |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
-- Nice: rel_regtime INDEX is used, all columns (key_len=12)
</code>
-- Now, show optimizer when it has got both rel_id and regtime needed for "rel_regtime" INDEX on "test_item" TABLE, but is JOINED with another table:
EXPLAIN SELECT i.* from test_rel r inner join test_item i on (i.rel_id=r.rhs_id AND i.regtime>='2007-05-02') WHERE r.lhs_id=1;
+----+-------------+-------+------+---------------+-------------+---------+--------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------------+---------+--------------+------+-------------+
| 1 | SIMPLE | r | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| 1 | SIMPLE | i | ref | rel_regtime | rel_regtime | 4 | icl.r.rhs_id | 1 | Using where |
+----+-------------+-------+------+---------------+-------------+---------+--------------+------+-------------+
-- What?: rel_regtime INDEX *is* used but key_len=4, why is it no using the '2007-05-02' constant on the regtime field?
-- This happens even if regtime is integer, a timestamp, whatever..
-- What am I missing?
-- Thanks!
Edited 1 time(s). Last edit at 09/08/2007 03:38PM by Miguel Balsevich.