Ad 4) not quite,but close. If the index used for a join also contains all the 
other fields of that table that are referenced in the query, SQLite can use 
those values to avoid reading in the corresponding table row. This saves memory 
(no storage for table row consumed), CPU cycles (no going through another BTree 
structure) and disk IO (no access to the tables' pages).

-----Ursprüngliche Nachricht-----
Von: sqlite-users [] Im 
Auftrag von Rob Willett
Gesendet: Freitag, 17. März 2017 19:22
An: SQLite mailing list <>
Betreff: Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...


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 

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 


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.
> Simon.
> _______________________________________________
> sqlite-users mailing list
sqlite-users mailing list

 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

sqlite-users mailing list

Reply via email to