Indexes (or indices) are auxiliary data structures used by the database system to enforce unique constraints, speed up sort operations, and provide faster access to specific records. They are often created in hopes of making queries run faster by avoiding table scans and providing a more direct lookup method.
Understanding where and when to create indexes can be an important factor in achieving good performance, especially as datasets grow. Without proper indexes in place, the database system has no option but to do full table scans for every lookup. Table scans can be especially expensive when joining tables together.
Indexes are not without cost, however. Each index must
maintain a one-to-one correspondence between index entries and table rows.
If a new row is inserted, updated, or deleted from a table, that same
modification must be made to all associated indexes. Each new index will add
additional overhead to the
DELETE commands. Indexes also consume space,
both on disk as well as in the SQLite page cache. A proper, well-placed
index is worth the cost, but it isn’t wise to just create random indexes, in
hopes that one of them will prove useful. A poorly placed index still has
all the costs, and can actually slow queries down. You can have too much of
a good thing.
Each index is associated with a specific table. Indexes can be either single column or multicolumn, but all the columns of a given index must belong to the same table. There is ...