Re: How MySQL Uses Indexes

[Mola Ji]

Thank you for your explanation, but I still do not understand, why does not mysql use index when necessary, should I force the use of index manually?
below the simple example I tested

Online database:
my table t
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

count(*):5253

EXPLAIN SELECT *
FROM t
WHERE o > '2013-05-04 '
AND i < '2013-05-11';

result:
1 row in set (0.09 sec)

id => 1
select_type => SIMPLE
table => t
type => ALL
possible_keys => a
key => NULL
key_len => NULL
ref => NULL
rows => 5875
Extra => Using where


Now with local database:

The same table t but on local

count(*):1103

EXPLAIN SELECT *
FROM t
WHERE o > '2013-05-04 '
AND i < '2013-05-11';

id => 1
select_type => SIMPLE
table => t
type => range ****
possible_keys => a ****
key => a
key_len => 3
ref => NULL
rows => 84
Extra => Using where

Finally with FORCE_INEX on remote database,
EXPLAIN SELECT *
FROM t
FORCE INDEX ( a )
WHERE o > '2013-05-04 '
AND i < '2013-05-11';
I obtain:
id => 1
select_type => SIMPLE
table => t
type => range
possible_keys => a
key => a
key_len => 3
ref => NULL
rows => 1174
Extra => Using where

Conclusion:
Mysql does not use index automatically on my remote database! why?
However, the test performed with FORCE INDEX does not reflect an improvement in performance, even worse
I conclude that mysql optimizes the query for the best performance, with or without an index? is it true?