SQLITE and autoindexing

Question

I recently began exploring indexing in sqlite. I'm able to successfully create an index with my desired columns.

After doing this I took a look at the database to see that the index was created successfully only to find that sqlite had already auto created indexes for each of my tables: "sqlite_autoindex_tablename_1"

These auto generated indices used two columns of each table, the two columns that make up my composite primary key. Is this just a normal thing for sqlite to do when using composite primary keys?

Since I'll be doing most of my queries based on these two columns, does it make sense to manually create indices, which are the exact same thing?

New to indices so really appreciate any support/feedback/tips, etc -- thank you!

Complexion

Answer 1

SQLite requires an index to enforce the PRIMARY KEY constraint -- without an index, enforcing the constraint would slow dramatically as the table grows in size. Constraints and indexes are not identical, but I don't know of any relational database that does not automatically create an index to enforce primary keys. So yes, this is normal behavior for any relational database.

If the purpose of creating an index is to optimize searches where you have an indexable search term that involves the first column in the index then there's no reason to create an additional index on the column(s) -- SQLite will use the automatically created one.

If your searches will involve the second column in the index without including an indexable term for the first column you will need to create your index. Neither SQLite (nor any other relational database I know of) can use composite indexes to optimize filtering when the head columns of the index are not specified in the search.

Housebound