We're still benchmarking the various changes we've made during the day. I'm sitting here watching a tail of a log file waiting for it to finish. Ah the joys of the internet startup :)

Our plan of action is

1. Have baseline from which we can work from. We now have that.

2. Create the correct sub-query index and work out the (hopefully) increase in speed.

3. Work through all the remaining indexes and check that we have not made the same mistake. I know we actually have :( Keep rerunning our benchmark so we know if we are actually making a difference.

4. Work through returning just the columns we actually need from our queries. We have a recollection that if we can build an index with all the information necessary in it, we can do all the work in joins rather than paging out to disk. Is this what you are referring to?

5. Sleep (not exactly sure when) and watch three international rugby games tomorrow.


On 17 Mar 2017, at 18:15, Simon Slavin wrote:

On 17 Mar 2017, at 5:30pm, Rob Willett <> wrote:

echo "select * from Disruptions where status = 2 OR status = 6;" | sqlite3 tfl.sqlite > /dev/null

twice and each run as 12 mins. So we were twice as quick, which is nice.

Do you actually need all columns ? If not, then specifying the columns you need can lead to a further speedup. It might be enough just to specify the columns you need, but you can achieve further increases in speed by making a covering index. If speed for this SELECT is sufficiently important to you, and you don’t actually need all columns, post again and we’ll explain further.

sqlite-users mailing list
sqlite-users mailing list

Reply via email to