Re: Fails to use range index in outer joined table

[Fredrik Andersson]

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.