Skip to Main Content
Using SQLite
book

Using SQLite

by Jay A. Kreibich
August 2010
Intermediate to advanced content levelIntermediate to advanced
526 pages
23h 39m
English
O'Reilly Media, Inc.
Content preview from Using SQLite

Indexes

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 INSERT, UPDATE, and 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.

How They Work

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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Learning SQL, 3rd Edition

Learning SQL, 3rd Edition

Alan Beaulieu
High Performance MySQL, 4th Edition

High Performance MySQL, 4th Edition

Silvia Botros, Jeremy Tinley

Publisher Resources

ISBN: 9781449394592Errata PageSupplemental Content