Alex Rivera | Logout

What are the disadvantages of having many indices?

Asked 2009-04-18T21:08:04.337
18

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.

sql database sqlite indexing rdbms-agnostic
Edit
Report

2 Answers

6

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.

answered 2009-04-18T21:12:01.457
5

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.

answered 2009-04-19T02:46:22.027

Your Answer