Indexes in MySQL

June 2, 2006

Author

Vadim Tkachenko

Insight for DBAs

Share this Post:

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

CREATE TABLE `t2` (
`ID` int(11) default NULL,
`ID1` int(11) default NULL,
`SUBNAME` varchar(32) default NULL,
KEY `ID1` (`ID1`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `t2` (

`ID` int(11) default NULL,

`ID1` int(11) default NULL,

`SUBNAME` varchar(32) default NULL,

KEY `ID1` (`ID1`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

SELECT COUNT(*) FROM t2

1	SELECT COUNT(*) FROM t2

;
250001 (V1)

SELECT COUNT(*) FROM t2 WHERE ID1=1

1	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:

mysql&gt; EXPLAIN SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1;
+----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
| 1 | SIMPLE | t2 | ref | ID1 | ID1 | 5 | const | 81371 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+

mysql> EXPLAIN SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1;

+----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+

+----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+

| 1 | SIMPLE | t2 | ref | ID1 | ID1 | 5 | const | 81371 | Using where |

+----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+

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

1	SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1

– 410 ms

SELECT COUNT(SUBNAME) FROM t2 IGNORE INDEX (ID1) WHERE ID1=1

1	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;

1	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

1	SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1

– 1200 ms

SELECT COUNT(SUBNAME) FROM t2 IGNORE INDEX (ID1) WHERE ID1=1

1	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.