Impact of indices on MySQL

It is generally well known that indexes help a lot to speed up database queries (especially SELECT but also UPDATE and DELETE). It is less known, that indexes also have some disadvantages.

One of these disadvantages is, that indexes require space in memory and on disk. An other disadvantage of indexes is, that they slow down DML statements like INSERT and DELETE.

We often see at our customers that they do not realized this behavior. Now I found the time to show the impact of (too) many indexes graphically.

In the following test scenario we created a simple table:

CREATE TABLE `test` (
  `id` INT(10) UNSIGNED NOT NULL,
  `f1` INT(10) UNSIGNED NOT NULL,
  `f2` INT(10) UNSIGNED NOT NULL,
  `f3` INT(10) UNSIGNED NOT NULL,
  `f4` INT(10) UNSIGNED NOT NULL,
  `f5` INT(10) UNSIGNED NOT NULL,
  `f6` INT(10) UNSIGNED NOT NULL,
  `f7` INT(10) UNSIGNED NOT NULL,
  `f8` INT(10) UNSIGNED NOT NULL
) ENGINE = MyISAM;

and added some indexes:

ALTER TABLE test ADD PRIMARY KEY (id);
ALTER TABLE test ADD INDEX (f1);
...

Then we did 10 times 500k simple INSERT statements to this table.

What we can clearly see is for every index we add to the table the INSERTs will take longer and longer.

The database parameters were as follows:

key_buffer_size         = 384M
sort_buffer_size        = 256M
read_buffer             =   8M
myisam_sort_buffer_size = 128M

The INSERTs were running single threaded.

In our example we were just indexing INT values (4 bytes in size). But I predict that it becomes even slower, when we index VARCHAR fields.

This situation will become even worse when you get pressure to your RAM or when the MyISAM key buffer will become too small and/or if you have pressure to your I/O system.

When you go to the limits of your hardware you should find a good balance between as many indexes as necessary but as little indexes as possible.

When you want to get rid of indexes the quick wins are indexes which are completely redundant. This happens when the people who create the indexes are not looking if such an index already exists.

An other quick win are indexes which are partially redundant (from left to right). With such indexes it does often not make sense to keep both of them (except you make use of covering indexes and explicitly specified them for this purpose).

It is a bit more tricky to find indexes which are never used. How this works out we will see in a later post.