MySQL Forums
Forum List  »  Optimizer & Parser

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

Create code for the affected table:

CREATE TABLE `table2` (
  `date` date NOT NULL default '0000-00-00',
  `adid` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`date`,`adid`),
  KEY `test` (`adid`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `table1` (
  `id` int(11) NOT NULL auto_increment,
  `userid` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `userid` (`userid`),
) ENGINE=MyISAM AUTO_INCREMENT=37087 DEFAULT CHARSET=latin1


Here are the results:

SELECT a.id 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;

mysql> SHOW STATUS LIKE 'handler_read%';
+-----------------------+----------+
| Variable_name         | Value    |
+-----------------------+----------+
| Handler_read_first    | 0        | 
| Handler_read_key      | 21       | 
| Handler_read_next     | 10994369 | 
| Handler_read_prev     | 0        | 
| Handler_read_rnd      | 0        | 
| Handler_read_rnd_next | 0        | 
+-----------------------+----------+
6 rows in set (0.00 sec)




SELECT a.id 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;

mysql> SHOW STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     | 
| Handler_read_key      | 21    | 
| Handler_read_next     | 2000  | 
| Handler_read_prev     | 0     | 
| Handler_read_rnd      | 0     | 
| Handler_read_rnd_next | 0     | 
+-----------------------+-------+
6 rows in set (0.00 sec)

But, when I impose the condition adid=46, please not that I return a much smaller dataset (there are other matching values except 46).
But the strange thing is that the range part of the index is not used at all in the first case.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Fails to use range index in outer joined table
1044
May 27, 2011 01:51AM


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.