Performance Tuning

This page is an area where you can place code, hits, suggestion or text that relates to increasing the speed of the SQLITE databases.

Place indexes on join and where column

Placing indexes on columns that are used in joins or where statements are the best way to increase query speed.

Use cluster indexes

Clustered indexes are where you have more than one column in the index. These increase the speed on queries over multiple indexed in situation where you have multiple joins to the one table or you are sleecting rows based on multiple columns (This is true in other DB system, I don't know if it holds true in SQLITE)

How SQLITE works out JOINS

from email by D. Richard Hipp on 1/13/2004
SQLite implements JOIN USING by translating the USING clausing into some extra WHERE clause terms. It does the same with NATURAL JOIN and JOIN ON. So while those constructs might be helpful to the human reader, they don't really make any difference to SQLite's query optimizer.
So if there work in working out joins it stands to reason that doing that work for SQLITE will speed up the compile times. If you are returning 100's of rows this might not mater but if you are returning 1 row many times this might be a big issue.

Order your tables properly in your where clause

Place the tables where you can eliminate the most rows from using a where clause (preferably on an indexed column) first, in order to limit the number of join operations required.