Simon,
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.
Rob
On 17 Mar 2017, at 18:15, Simon Slavin wrote:
On 17 Mar 2017, at 5:30pm, Rob Willett <rob.sql...@robertwillett.com>
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.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users