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.

Improve this question

4 Answers 4

Reset to default
44

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

Improve this answer
2
  • 2
    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.
    – Michael Haren
    Commented Apr 19, 2009 at 2:49
  • 6
    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 :)
    – MatBailie
    Commented Apr 19, 2009 at 20:55
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.

Improve this answer
1
  • Indexing a two-value field can (occasionally) be useful when the distribution is extremely uneven - and the database knows it through statistics.
    – Mike Woodhouse
    Commented Apr 19, 2009 at 16:52
6

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.

Improve this answer
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.

Improve this answer
3
  • 1
    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...
    – MatBailie
    Commented Apr 19, 2009 at 20:52
  • 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.
    – Steve Cooper
    Commented Dec 10, 2009 at 11:48
  • 1
    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.
    – MarkR
    Commented Dec 10, 2009 at 11:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.