{"id":566,"date":"2015-12-14T10:42:33","date_gmt":"2015-12-14T03:42:33","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=566"},"modified":"2024-04-27T20:51:28","modified_gmt":"2024-04-27T13:51:28","slug":"sqlite-index","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-index\/","title":{"rendered":"SQLite Index"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use SQLite indexes to query data faster, speed up sort operations, and enforce unique constraints.<\/p>\n\n\n\n

What is an index?<\/h2>\n\n\n\n

In relational databases, a table is a list of rows. At the same time, each row has the same column structure that consists of cells. Each row also has a consecutive rowid<\/a> sequence number used to identify the row. Therefore, you can consider a table as a list of pairs: (rowid, row).<\/p>\n\n\n\n

Unlike a table, an index has an opposite relationship: (row, rowid). An index is an additional data structure that helps improve the performance of a query.<\/p>\n\n\n\n

\"SQLite<\/figure>\n\n\n\n

SQLite uses B-tree for organizing indexes. Note that B<\/strong> stands for balanced, B-tree is a balanced tree, not a binary tree.<\/p>\n\n\n\n

The B-tree keeps the amount of data at both sides of the tree balanced so that the number of levels that must be traversed to locate a row is always in the same approximate number. In addition, querying using equality (=) and ranges (>, >=, <,<=) on the B-tree indexes is very efficient.<\/p>\n\n\n\n

How does an index work<\/h2>\n\n\n\n

Each index must be associated with a specific table. An index consists of one or more columns, but all columns of an index must be in the same table. A table may have multiple indexes.<\/p>\n\n\n\n

Whenever you create an index, SQLite creates a B-tree structure to hold the index data.<\/p>\n\n\n\n

The index contains data from the columns you specify in the index and the corresponding rowid<\/code> value. This helps SQLite quickly locate the row based on the values of the indexed columns.<\/p>\n\n\n\n

Imagine an index in the database like an index of a book. By looking at the index, you can quickly identify page numbers based on the keywords.<\/p>\n\n\n\n

SQLite CREATE INDEX statement<\/h2>\n\n\n\n

To create an index, you use the CREATE INDEX<\/code> statement with the following syntax:<\/p>\n\n\n

CREATE<\/span> [UNIQUE<\/span>] INDEX<\/span> index_name \nON<\/span> table_name(column_list);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To create an index, you specify the following information:<\/p>\n\n\n\n