Posted by Vadim

MySQL does not always make a right decision about indexes usage.
Condsider a simple table:

PLAIN TEXT
SQL:
  1. CREATE TABLE `t2` (
  2. `ID` int(11) DEFAULT NULL,
  3. `ID1` int(11) DEFAULT NULL,
  4. `SUBNAME` varchar(32) DEFAULT NULL,
  5. KEY `ID1` (`ID1`)
  6. ) ENGINE=MyISAM DEFAULT CHARSET=latin1

SELECT COUNT(*) FROM t2;
250001 (V1)

SELECT COUNT(*) FROM t2 WHERE ID1=1;
83036 (V2)
(execution time = 110 ms)

That is index selectivity by condition (ID1=1) is V2/V1 = 0.3321 or 33.21%

It is said (e.g. book "SQL Tuning") if selectivity over 20% then a full table scan is preferable than an index access.
As far as I know Oracle alway chooses a full table scan if selectivity over 25%.

What with MySQL:

PLAIN TEXT
SQL:
  1. mysql> EXPLAIN SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1;
  2. +----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
  3. | id | select_type | TABLE | type | possible_keys | KEY | key_len | ref | rows | Extra |
  4. +----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
  5. | 1 | SIMPLE | t2 | ref | ID1 | ID1 | 5 | const | 81371 | USING WHERE |
  6. +----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+

That is MySQL will use index for this query.

Let's compare the execution time with index access and with table scan:

SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1 - 410 ms
SELECT COUNT(SUBNAME) FROM t2 IGNORE INDEX (ID1) WHERE ID1=1 - 200 ms

As you see the table scan is faster by 2 times.

Consider more extremal case: selectivity ~95%:
SELECT cnt2 / cnt1 FROM (SELECT count(*) cnt1 FROM t2) d1, (SELECT count(*) cnt2 FROM t2 WHERE ID1=1) d2;
0.9492 = 94.92%

Explain still claims MySQL will use index.

Execution time:
SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1 - 1200 ms
SELECT COUNT(SUBNAME) FROM t2 IGNORE INDEX (ID1) WHERE ID1=1 - 260 ms

That is table scan is faster by 4.6 times.

Why does MySQL choose index access?
MySQL doesn't calculate index selectivity, just estimates count of logical input/output operations, and for
our case count of Logical I/O for index access is less than for table scan.

So be careful with indexes, they help in not all cases.