OK,
The structure:
CREATE TABLE IF NOT EXISTS `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`i` date NOT NULL,
`o` date NOT NULL,
PRIMARY KEY (`id`),
KEY `a` (`o`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
The Request:
SELECT i, o
FROM `t`
WHERE o > '2013-04-05'
AND i < '2013-04-11'
I test the request with two tables
First table:
4269 records with o above '2013-04-05'
6930 records with o less than '2013-04-05'
total records: 11199
id: 1
select_type: SIMPLE
table: t
type: ALL
possible_keys: a
key: NULL
key_len: NULL
ref: NULL
rows: 10929
Extra: Using where
Second table:
1269 records with o above '2013-04-05'
6930 records with o less than '2013-04-05'
total records: 8199
The result:
id: 1
select_type: SIMPLE
table: t
type: range
possible_keys: a
key: a
key_len: 3
ref: NULL
rows: 1267
Extra: Using where
Where records above '2013-04-05' is important, mysql does not use index?
if I use FORCE INDEX(a) with the first table, I have the result below:
id: 1
select_type: SIMPLE
table: t
type: range
possible_keys: a
key: a
key_len: 3
ref: NULL
rows: 5655
Extra: Using where
In my table, usually record are distributed so that 70% come before the date 'o'
test online:
http://internet-setup.net/mysql_perf.php