MySQL Innodb Key Length by Secondary Indexes
MySQL: Ver 14.12. Distribute 5.0.51b, for redhat-linux-gnu (x86_64) using EditLine skins
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 uses a key total_listen_nums
.
Then assign 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_value total_listen_nums is the arrow than the album_id.
Is this why sql_1 uses total_listen_nums?
source to share
In this case, I think "key length" refers to the size of the key in bytes. The primary key in a column INT
requires 4 bytes. Since the primary key is always included in any secondary indexes, you end up with a key for INT
+ INT
or 4 + 4 bytes to get 8.
I'm not entirely sure why this number would be a problem for you. There is significantly more overhead in the index structure itself than the negligible number of bytes required to represent the key.
I am not sure why you are using the force index operation for simple counting. As far as I know, MySQL doesn't use these indexes very well for counting, unlike later versions of Postgres. I believe it has something to do with how MVCC is implemented for InnoDB.
Remember that the number of rows in a table in a transactional database is not always easy to quantify.
source to share