Re: Slow Performance / Index not used

[Horst Pralow]

Just as a follow-up:

Here are two results which seem to prove Rick's statments:

mysql> explain select count(*) from repository force index (name);
+----+-------------+------------+-------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | repository | index | NULL | name | 768 | NULL | 34019897 | Using index |
+----+-------------+------------+-------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from repository force index (name);
+----------+
| count(*) |
+----------+
| 17593472 |
+----------+
1 row in set (9 min 3.62 sec)

mysql> explain select count(*) from repository force index (PRIMARY);
+----+-------------+------------+-------+---------------+---------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+------+----------+-------------+
| 1 | SIMPLE | repository | index | NULL | PRIMARY | 8 | NULL | 34022095 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from repository force index (PRIMARY);

+----------+
| count(*) |
+----------+
| 17595637 |
+----------+
1 row in set (50 min 53.13 sec)

As we see, the first query being forced to use a secondary key (which is far from being optimal) executes much faster then the second one using PRIMARY.