SQLite

Chapter 4. Query Optimization

Now that you are familiar with the SQL syntax implemented by SQLite, let's take a look at the way in which your tables are created and how the way your queries are written can affect the performance of your database.

Keys and Indexes

In the preceding chapter you met the CREATE INDEX statement, used to add an index to one or more columns of a database table.

Indexes are used to preserve a particular sort order on a column within the database itself, enabling queries in which the WHERE clause references that column directly to operate much faster. Rather than scanning a table's data from top to bottom and pulling out just the rows that match the given criteria, the index tells SQLite exactly where to look in that table ...