Menu
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!
579
SQLite3 will create these indexes automatically.
The query optimizer in SQLite has basically two choices on how to implement this query. (There are actually six different choices, but we will only consider two of them here.) Pseudocode below demonstrating these two choices. The same indexes are used to speed up every loop in both implementation options.
A table may have multiple indexes. Whenever you create an index, SQLite creates a B-tree structure to hold the index data. The index contains data from the columns that you specify in the index and the corresponding rowid value. This helps SQLite quickly locate the row based on the values of the indexed columns.
What is an Index in SQLite? An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. Each index name must be unique in the database.
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.
186
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
