I’m new to sqlite but am coming from the old real basic DBs. and always built indexs on certain fields. But I’ve been searching the documents for sqlite and haven’t found any examples where certain fields were index. So I was wondering if they were needed?
Dave
You should follow the same guidelines as with any other DBMS. Index the fields the same way you always have.
Thanks Tim
Will Do
Definitely run your queries in some third part tool
I use the sqlite3 cmd line tool on OS X
And use “explain” to see what or if your indexes are useful and being used
Sometimes you guess right about how to index and sometimes you guess very wrong and put lots of indexes on and that can slow things down as much as it speeds them up
There’s a cost to having lots of indexes
Performance tuning any DB is an art
Isnt the simple approach to this that as well as the primary key, you index the columns you need to perform searches on ? Thats what I always start with and usually keep those indexed columns as near to the start as possible. I know this is simplistic but it has always worked well enough for me.
As Norman said, run some of your popular queries through Explain and see what the result is. Your indexing may be sufficient or you may find that changing this one index, or doing a combined index really speeds things up beyond what you thought was possible.
for really common queries you might be able to get all the data from an index & never have to go to the data page.
This can provide a significant speed up in some cases BUT you have to design indexes as carefully as you design the tables.
Especially in busy multiuser systems.
You may actually design the index to reduce data clustering on “on end” of the table or index and make it deliberately spread it around.
This too can reduce “hot spots” in the index & data pages &, in a really busy system, can prove to be the difference between a few thousand inserts a second and 10’s of thousands a second. Been there done that have the scars to prove it.