Unnecessary MySQL indexes
Warning! This is the old Hercules bugtracker archive, and may not reflect the current state of Hercules. The current bugtracker is on GitHub Issues.
Issue information
Issue ID
#5803
Status
Invalid
Severity
None
Started
Hercules Elf Bot
May 24, 2012 8:23
Last Post
Hercules Elf Bot
Jun 1, 2012 9:23
Confirmation
Hercules Elf Bot - May 24, 2012 8:23
Originally posted by [b]akrus[/b]
Hi,
Some of the indexes in MySQL database are useless.
Thanks to Percona's Toolkit
ALTER TABLE `guild_position` DROP INDEX `guild_id`;
ALTER TABLE `hotkey` DROP INDEX `char_id`;
ALTER TABLE `skill_homunculus` DROP INDEX `homun_id`;
Could someone commit this into upstream?
Hi,
Some of the indexes in MySQL database are useless.
Thanks to Percona's Toolkit
ALTER TABLE `guild_position` DROP INDEX `guild_id`;
ALTER TABLE `hotkey` DROP INDEX `char_id`;
ALTER TABLE `skill_homunculus` DROP INDEX `homun_id`;
Could someone commit this into upstream?
Hercules Elf Bot - May 24, 2012 8:31
Originally posted by [b]Kenpachi[/b]
An index is never unnecessary. And furthermore this is not a bug. If you don't like it for yiour server, change it.
-> Invalid
An index is never unnecessary. And furthermore this is not a bug. If you don't like it for yiour server, change it.
-> Invalid
Hercules Elf Bot - May 24, 2012 12:29
Originally posted by [b]akrus[/b]
Actually those indexes are duplicates and already exist in PRIMARY KEY. It's not a bug, but it's an unnecessary database overhead.
Here's a proof:
[code]mysql> EXPLAIN SELECT `guild_id` FROM `rathena`.`guild_position` USE INDEX (`guild_id`) WHERE `guild_id` = 1;
+----+-------------+----------------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | guild_position | ref | guild_id | guild_id | 4 | const | 76 | Using index |
+----+-------------+----------------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)[/code]
[code]mysql> EXPLAIN SELECT `guild_id` FROM `rathena`.`guild_position` IGNORE INDEX (`guild_id`) WHERE `guild_id` = 1;
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | guild_position | ref | PRIMARY | PRIMARY | 4 | const | 76 | Using index |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)[/code]
As you can see, primary key is doing the same (as it's left part is already an index for `guild_id` the second one is just the same, and is not used by default).
Actually those indexes are duplicates and already exist in PRIMARY KEY. It's not a bug, but it's an unnecessary database overhead.
Here's a proof:
[code]mysql> EXPLAIN SELECT `guild_id` FROM `rathena`.`guild_position` USE INDEX (`guild_id`) WHERE `guild_id` = 1;
+----+-------------+----------------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | guild_position | ref | guild_id | guild_id | 4 | const | 76 | Using index |
+----+-------------+----------------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)[/code]
[code]mysql> EXPLAIN SELECT `guild_id` FROM `rathena`.`guild_position` IGNORE INDEX (`guild_id`) WHERE `guild_id` = 1;
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | guild_position | ref | PRIMARY | PRIMARY | 4 | const | 76 | Using index |
+----+-------------+----------------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)[/code]
As you can see, primary key is doing the same (as it's left part is already an index for `guild_id` the second one is just the same, and is not used by default).
This post has been edited by akrus on May 24, 2012 12:40