On 11/9/2011 10:33 PM, template wrote:
Hello,
I have 3 million rows in a read only sqlite3 database (select only). I am
looking for the best possible lookup performance. Below is what has been
done so far, what else can be done to improve performance of select for
single threaded access via C++ API on Linux 64 bit machin?
1. Load entire database/index loaded into memory using backup api.
2. Index all columns that may be in the where clause
3. Build, prepare and cache in place statements
In general, a lot depends on your query and the structure of your data.
You should also look at the following:
* Increasing the page size and the number of pages for the cache (will
matter less if the data is in memory)
* De-normalize - this is not the best way usually, but given that you
have a read-only database, you should be able to reduce the number of
JOINs by de-normalizing the database
* Use ANALYZE so that SQLite has a better chance to select the correct
indexes
* Use EXPLAIN query for queries that are slow and make sure that the
correct indexes are being used... having too many indexes may not always
be a good thing since it can result in SQLite picking the wrong index
* See if extensions like RTree and FTS solve any of the problems that
you are facing (e.g., if you need to search text, don't be using LIKE, etc.)
However, I think a lot depends on how your data is stored, and what the
queries you plan to run.
Cheers,
Mohit.
12/9/2011 | 2:42 AM.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
