Disadvantages of many indexes in MySQL [closed]

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 ?

Answer 1

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