Effect of indices on SQLite (optimizer) performance
From: Erik Huelsmann <ehuels_at_gmail.com>
Date: Sat, 5 Feb 2011 19:05:52 +0100
Yesterday or IRC, Bert, Philip and I were chatting about our SQLite
After searching and discussing its documentation, Philip suggested the
So, I went to work with his "fake database generator script" (attached
The type of query we're seeing problematic performance with looks like
SELECT * FROM nodes WHERE wc_id = 1 AND (local_relpath = 'foo' OR
We discussed 3 ways to achieve the effect of this query:
1. The query itself
So, I've created 'perf.py' to evaluate each of these scenarios,
This is my finding:
Scenario (1) [an AND combined with a complex OR] doesn't perform well
Scenario (2) performs differently, depending on the available indices.
Scenario (3) performs roughly equal to scenario (2).
Scenario (2) takes ~0.27 seconds to evaluate in the unmodified
Seems Philip was right :-) We need to carefully review the indices we
This is an archived mail posted to the Subversion Dev mailing list.