After a table is created with its column and datatypes, it is almost ready to use. The primary columns for data linking are done, but it is not quite ready as defined by a DBA. Instead, an index can be created to speed up SQL queries and act as special lookup tables that SQLite will use as a way of getting information faster.
An index has a data pointer, which will quickly reference the data and bring it back, thus making access quicker without much overhead generally. By definition, indexes do have an overhead in their own right, but the overheads of the index are dwarfed by the performance and efficiency gained. This could be useful for mobile applications because of their limited resource and network access.
A database index as an example is similar to the one located in a book, where you can find what you want because you know what it is, and just opens the book to right page, after reading the index.
The index is slow on data input but fast on the SELECT queries, with or without the WHERE clause. Once an index is created, it can also be dropped without affecting the core data of a table. The following is a simple example of an index called table_index_name being created on the customer table:
CREATE INDEX table_index_name ON customer;
A second example shows how an index is created to reference a column called salary, which is used multiple times in many queries:
CREATE INDEX table_index_salary ON customer (salary);
Here, an index will speed up access. An index called table_index_salary is created on the customer table, which is created on the salary column.
There are also indexes called
composite indexes, which actually index more than one column on a database for further performance gains, but also an overhead on data input. As shown in the following, an index is created and is linked to two columns: salary and bonus. Rather than having two indexes with more overhead issues, a composite index maybe better, since it is one index with a reference to two columns:
CREATE INDEX table_index_salary ON customer (salary, bonus);
When creating indexes, the database server will also create implicit indexes that are automatic. If, as a DBA, you wish to see them, use the following example:
sqlite> .indices customer;