When too many indexes can be a bad thing

Many web developers I've worked with believe that the solution to every database slowness problem is to add an index.  This is due to an incomplete understanding of how databases use indexes.  The explanation is fairly simple, so this will be a short post.

Any time you insert a new row into a table, or delete a row, all indexes will need to be updated.  If you update a row, only the affected indexes will need to be updated -- in some cases, that may still be all of them.  Thus, while too few indexes can slow down your read queries, too many indexes can slow down your write queries.  Depending on the workload for that table, this may not be an issue.  In fact, there are cases where it makes sense to have the space required for a table's indexes exceed the size of the table data.

In many cases, though, I've seen indexes that are unlikely to ever be used.  I've also seen the same index added multiple times, probably by different developers.  It's helpful to know what commands can be used to determine what indexes already exist, and how useful they are.

SHOW INDEXES FROM times;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| times |          0 | PRIMARY  |            1 | id          | A         |         120 |     NULL | NULL   |      | BTREE      |         |               |
| times |          1 | ts       |            1 | ts          | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| times |          1 | room     |            1 | room        | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| times |          1 | size     |            1 | size        | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

The SHOW INDEXES FROM command can be used to see what indexes already exist on a table.  This will help you determine when to add new keys.  You should also use the EXPLAIN command to determine if your index is being used.  You will need a sample SELECT query.  You can use the MariaDB site's Explain Analyzer to better understand the output of the EXPLAIN command.

If you are using a graphical SQL query tool, you'll need to consult the documentation for your tool to determine how to view indexes and explain your queries.