Reputation: 507
MySQL : Ver 14.12 Distrib 5.0.51b, for redhat-linux-gnu (x86_64) using EditLine wrapper
sql_1:
SELECT SQL_NO_CACHE COUNT(*) FROM `ting_song_info`;
result :
+----------+
| COUNT(*) |
+----------+
| 2637447 |
+----------+
1 row in set (0.42 sec)
explain :
+----+-------------+----------------+-------+---------------+-------------------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+-------------------+---------+------+---------+-------------+
| 1 | SIMPLE | ting_song_info | index | NULL | total_listen_nums | 4 | NULL | 2769410 | Using index |
+----+-------------+----------------+-------+---------------+-------------------+---------+------+---------+-------------+
The sql_1 use the key total_listen_nums
.
Then appoint the key.
sql_2 :
SELECT SQL_NO_CACHE COUNT(*) FROM `ting_song_info` USE KEY(`album_id`);
result :
+----------+
| COUNT(*) |
+----------+
| 2637447 |
+----------+
1 row in set (5.21 sec)
explain:
+----+-------------+----------------+-------+---------------+----------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+----------+---------+------+---------+-------------+
| 1 | SIMPLE | ting_song_info | index | NULL | album_id | 8 | NULL | 2769410 | Using index |
+----+-------------+----------------+-------+---------------+----------+---------+------+---------+-------------+
The key_len of total_listen_nums is shoter than album_id.
Is this why sql_1 use total_listen_nums?
Upvotes: 3
Views: 125
Reputation: 211750
In this case I believe the "key length" refers to the size of the key in bytes. A primary key on an INT
column requires 4 bytes. As the primary key is always included in any secondary indexes, you end up with a key for INT
+ INT
, or 4 + 4 bytes to yield 8.
I'm not entirely sure why this number would be a concern for you. There is considerably more overhead in the index structure itself than the inconsequential number of bytes needed to represent the key.
I'm not sure why you would be using a force index operation on a simple count. MySQL does not make much use of these indexes for counts as far as I know, unlike newer versions of Postgres. I believe this has something to do with how the MVCC for InnoDB is implemented.
Remember the number of rows in a table in a transactional database is not always easy to quantify.
Upvotes: 2