What is an index in SQLite?

Question

I don't understand what an index is or does in SQLite. (NOT SQL) I think it allows for sorting in acending and decending order and access to data quicker. But I'm just guessing here.

Answer 1

Yes, indexes are all about improved data access performance (but at the cost of storage) http://en.wikipedia.org/wiki/Index_(database)

Answer 2

The same as an index in any SQL (YES SQL) RDBMS.

You can see the SQLite query optimizer considers indexes: http://www.sqlite.org/optoverview.html

Answer 3

An index (in any database) is a list of some kind which associates a sorted (or at least, quickly searchable) list of keys with information about where to find the rest of the data associated with the key.

You may not be finding information about this on the Internet because you're assuming it's a SQLite concept, but it's not - it's a general computer engineering concept.

Answer 4

Why not SQL? The answer is the same, though the internal details will differ between implementations.

Putting an index on a column tells the database engine to build, unsurprisingly, an index that allows it to quickly locate rows when you search for certain values in a column, without having to scan every row in the table.

A simple (and probably suboptimal) index might be built with an ordinary binary search tree.

Answer 5

Think about an address book. If you are searching the phone number of Rossi Mario, you know that surnames are ordered alphabetically so you can go to the letter R, then search for the letter o and so on. Index do the same, are a collections of references to entries that speed up a lot some operations.

Searching in an unordered address book would be much more slower, you should start from the first name on the first page and search in all the pages until you find the name you are looking for.

Answer 6

I think it allows for sorting in acending and decending order and access to data quicker.

Yes, that's what's it's for. Indexes create the abstraction of having sorted data, which speeds up searches significantly. With an index using a balanced binary search tree, searches take O(log N) instead of O(N) time.

What the other answers haven't mentioned that most databases use indexes in order to implement UNIQUE (and therefore also PRIMARY KEY) constraints. Because in order to ensure uniqueness, you have to be able to detect whether the key is already there, and this means you want fast searches for it.

Take a look in your SQLite database. Those sqlite_autoindex_ indices were created to enforce UNIQUE constraints.