CODEWITHSUNDEEP
mysqlinnodb
bloody numen
bloody numen

Reputation: 507

MySQL Innodb key length on secondary indexes

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

Answers (1)

tadman
tadman

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

Related Questions