Re: Slow Performance / Index not used
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.
Subject
Views
Written By
Posted
2813
August 28, 2008 08:46AM
1766
August 28, 2008 11:12PM
1825
August 29, 2008 03:43AM
Re: Slow Performance / Index not used
1731
August 29, 2008 05:00AM
1664
August 30, 2008 12:31AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.