Disadvantages of many indexes in MySQL

Question

Situation

I have a table called follow and this is the table structure:

- follower_id
- following_id
- when // timestamp

These are the SQL Queries I execute:

SELECT * FROM follow WHERE follower_id = ? ORDER BY when LIMIT 0,20
SELECT * FROM follow WHERE following_id = ? ORDER BY when LIMIT 0,20
SELECT * FROM follow WHERE follower_id = ?
SELECT * FROM follow WHERE following_id = ?

and these are my indexes:

follower_id, when
following_id, when
follower_id
following_id

Question

Is this approach true? And is there any disadvantage of having this amount (or more than this) of indexes in DB ?

Indices speed up `SELECT` but cost more on `INSERT`. You need to benchmark your database's performance and see if they have an impact. Any other advice would be guesswork. — Boris the Spider, Nov 16 '13 at 18:04
@BoristheSpider thank you. Looks like indexes decrease write operations like `insert` `update` `delete` while they are increasing reading operations. — Can Geliş, Nov 16 '13 at 18:12
Well, you don't need the last two indexes anyways. An index on (A, B) can also be used if you only have WHERE a = ?. Therefore, the first two indices can be used for the latter two queries too. — Markus Winand, Nov 16 '13 at 19:49
@MarkusWinand Nice to see you here. Thanks for you advice. What about I use `when, follower_id` index ? is this the same? — Can Geliş, Nov 17 '13 at 14:51
@CanGeliş No, that's not the same. See here: http://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys — Markus Winand, Nov 18 '13 at 20:57

score 24 · Accepted Answer · edited Jun 20 '17 at 22:55

And is there any disadvantage of having this amount (or more than this) of indexes in DB ?

I dont think that these amount of indexes will affect your performance.

However you may note that Indexes are good and speedy when using SELECT rather than INSERT.

Disadvantages of Index from [here][1] says that:

When an index is created on the column(s), MySQL also creates a separate file that is sorted, and contains only the field(s) you're interested in sorting on.

Firstly, the indexes take up disk space. Usually the space usage isn’t significant, but because of creating index on every column in every possible combination, the index file would grow much more quickly than the data file. In the case when a table is of large table size, the index file could reach the operating system’s maximum file size.

Secondly, the indexes slow down the speed of writing queries, such as INSERT, UPDATE and DELETE. Because MySQL has to internally maintain the “pointers” to the inserted rows in the actual data file, so there is a performance price to pay in case of above said writing queries because every time a record is changed, the indexes must be updated. However, you may be able to write your queries in such a way that do not cause the very noticeable performance degradation.

[1]: spam link removed

Thanks for your answer. Do you mean indexes decrease `insert` performance while they are increasing the `select` performance ? — Can Geliş, Nov 16 '13 at 18:07

Disadvantages of many indexes in MySQL

Situation

Question

1 Answers1