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 [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Rob Willett Gesendet: Freitag, 17. März 2017 19:22 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ... 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 ___________________________________________ 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 E-Mail: h...@scigames.at 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
