MySQL Forums
Forum List  »  Optimizer & Parser

Fails to use range index in outer joined table
Posted by: Fredrik Andersson
Date: May 25, 2011 10:01AM

I feel the indexer fails to use as much of the key as it could when I'm doing a range query in a left joined table.

If I do:
EXPLAIN
SELECT value from table1 a
LEFT JOIN table2 t ON t.date between '2011-05-24' and '2011-05-26'AND t.adid=a.id
WHERE a.userid=6;

it gives

+----+-------------+-------+------+--------------------------+------------------+---------+-----------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+--------------------------+------------------+---------+-----------------+-------+-------------+
| 1 | SIMPLE | a | ref | PRIMARY,userid_campid_id | userid_campid_id | 4 | const | 20 | Using index |
| 1 | SIMPLE | t | ref | PRIMARY,x | x | 4 | speedyads2.a.id | 11133 | Using where |
+----+-------------+-------+------+--------------------------+------------------+---------+-----------------+-------+-------------+

It uses the x index, but only 4 bytes (i.e. only the adid).

If I do the same thing but replace the ON condition with a constant adid:
EXPLAIN
SELECT value from table1 a
LEFT JOIN table2 t ON t.date between '2011-05-24' and '2011-05-26'AND t.adid=46
WHERE a.userid=6;

I get

+----+-------------+-------+-------+------------------+------------------+---------+-------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------+------------------+---------+-------+------+--------------------------------+
| 1 | SIMPLE | a | ref | userid_campid_id | userid_campid_id | 4 | const | 20 | Using index |
| 1 | SIMPLE | t | range | PRIMARY,x | x | 7 | NULL | 47 | Using where; Using join buffer |
+----+-------------+-------+-------+------------------+------------------+---------+-------+------+--------------------------------+

Now it can use 7 bytes of the key, i.e. it manages to use the date portion of the index too.

WHY can't MySQL do a range index lookup on table2 when adid comes from table1?
Am I doing something stupid?

mysql> desc table2;
| date | date | NO | PRI | 0000-00-00 | |
| adid | int(11) unsigned | NO | PRI | 0 | |
| region | tinyint(3) unsigned | NO | PRI | 0 | |
| value | int(11) unsigned | NO | | 0 | |
There are two indexed: the primary key (date,adid,region) and also x(adid,date) (for testing)

table1 contains nothing special, except that it introduces the non-constant adid value in the statement above.

I have run optimize table on all involved tables.
I run community server 5.5.12 on Linux x86_64.

Thank you for any help!

Options: ReplyQuote


Subject
Views
Written By
Posted
Fails to use range index in outer joined table
3361
May 25, 2011 10:01AM


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.