What are the disadvantages of having many indices?

Question

I recently sped up a complicated query by an order of magnitude by giving SQLite a good index to work with. Results like this make me wonder if I should index a lot of other fields that are commonly used for JOINs or ORDER BY clauses. But I don't want to get overzealous and have it backfire on me: I assume there must be some reasons not to create indices, or every field would be indexed by default.

I'm using SQLite in this case, but of course DBMS-agnostic advice is welcome as well.

Abridgment

Answer 1

Indexes slow down inserts and updates (which can become a really serious issue with locking) and cost disk space. That's pretty much it.

Aziza

That covers it. Don't forget that you do need proper indexes for good insert/update/delete performance, too. Like all things, it's a balance. – Tor

inserts don't benefit from indexes... Updates and Deletes require finding the relevant row(s), so they benefit from indexes for that step, but then if you have to many indexes, even that benefit can be negated. As you say, it's all a balance :) – Bloodstained

Answer 2

Indexes use up disc space to store, and take time to create and maintain. Unused ones don't give any benefit. If there are lots of candidate indexes for a query, the query may be slowed down by having the server choose the "wrong" one for the query.

Use those factors to decide whether you need an index.

It is usually possible to create indexes which will NEVER be used - for example, and index on a (not null) field with only two possible values, is almost certainly going to be useless.

You need to explain your own application's queries to make sure that the frequently-performed ones are using sensible indexes if possible, and create no more indexes than required to do that.

Piecework

Indexing a two-value field can (occasionally) be useful when the distribution is extremely uneven - and the database knows it through statistics. – Carrageen

Answer 3

In order to test your particular application you can put "EXPLAIN QUERY PLAN" in front of any query you run and check the results. It will show you where it is or is not using indexes.

That way you can determine where you could use more indexes and where they would not make a difference.

Sqlite Explain

I use SqliteSpy to hand test query's that seem to be causing trouble.

Betthezel

Answer 4

The cost of an index in disk space is generally trivial. The cost of additional writes to update the index when the table changes is often moderate. The cost in additional locking can be severe.

It depends on the read vs write ratio on the table, and on how often the index is actually used to speed up a query.

Avram

Hmmm, depending on the table and the fields being indexed, disk space cost is not always trivial. I have had many a case where the sum of the index space was double (or more) than the sum of the table space... – Bloodstained

Agree with Dems; for a narrow table, a nonclustered index can be of comparable size to the table. Say an index costs half the disk space of the table itself; a table with six indices is four times as large on disk as an unindexed one. Clustered indexes are basically free. – Eclat

No, the cost of an index in disc space is not "generally trivial". It is possible to use up a lot of space with indexes. – Piecework