MySQL Forums
Forum List  »  Optimizer & Parser

multiple index is used only partially
Posted by: Miguel Balsevich
Date: September 08, 2007 03:37PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
multiple index is used only partially
4397
September 08, 2007 03:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.