Improving performance with indices

One of the ways to improve the performance of a relational database such as SQLite is to make join operations faster. The ideal way to do this is to include enough index information so that slow search operations aren't done to find matching rows. Without an index, an entire table must be read to find referenced rows. With an index, just a relevant subset of rows can be read.

When we define a column that might be used in a query, we should consider building an index for that column. This means adding yet more SQL DDL statements to our table definitions.

An index is a separate storage but is tied to a specific table and column. The SQL looks like the following code:

CREATE INDEX IX_BLOG_TITLE ON BLOG( TITLE );

This will create an index on the title column of the Blog table. Nothing else needs to be done. The SQL database will use the index when performing queries based on the indexed column. When data is created, updated, or deleted, the index will be adjusted...