Dear all,

We progress steadily forward.

1. We immediately halved our execution time by moving to an updated index that doesn't use COLLATE. Thats 32mins to 16mins.

2. We then shaved a further six minutes off the execution time by removing extraneous fields in the select statement, so instead of "select * ...", we identified which fields we used and directly selected those. So we are now down to 10 mins or 1/3 of when we started for, to be honest, virtually no extra work, merely being smarter, or rather you being smarter.

3. We have looked through all our indexes and can see that every index has a COLLATE against it, even if the column is an integer. We have raised a support call with Navicat.

4. The next step is to create a "covering index" to try and get the whole of the query into the index. However its 22:11 in London and I need to get home.

Thanks very much for the help so far. Tomorrow is more tricky but I'll read up on covering indexes to see how to use them,.


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

On 17 Mar 2017, at 6:22pm, Rob Willett <> wrote:

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?

It works only where all the columns you need to read are in the same table. The ideal form of a covering index is to have the columns listed in this order:

1) columns needed for the WHERE clause
2) columns needed for the ORDER BY clause which aren’t in (1)
3) columns needed to be read which aren’t in (2) or (1)

SQLite detects that all the information it needs for the SELECT is available from the index, so it doesn’t bother to read the table at all. This can lead to something like a doubling of speed. Of course, you sacrifice filespace, and making changes to the table takes a little longer.

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

Sleep while waiting for indexes to be created and ANALYZE to work. May you see skilled players, creative moves and dramatic play.

sqlite-users mailing list
sqlite-users mailing list

Reply via email to