Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-20 Thread Rob Willett
Just to close this thread down completely. We've implemented the changes on our production server and the actual performance increase is even better than we predicted. Our production server is a containerised Ubuntu Server. Its hardware configuration is significantly different to our Macs on

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-20 Thread Rob Willett
David, We're English and masters of the understatement ;) Rob On 20 Mar 2017, at 15:04, David Raymond wrote: "... Our query has moved from 32 mins to 54 secs. We're quite happy with that performance increase." I have to admit that the hearty chuckle which that statement produced from me hu

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-20 Thread David Raymond
"... Our query has moved from 32 mins to 54 secs. We're quite happy with that performance increase." I have to admit that the hearty chuckle which that statement produced from me hurt my sore throat. Totally worth it though. ___ sqlite-users mailing li

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-19 Thread Paul Sanderson
Oops - somehow misread the last message - 54 seconds down from 32 minutes - that's a result! Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite em

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-19 Thread Paul Sanderson
What is the average size of the text in the direction field? and what page size have you set for the database? If the size of a record is such that only a small handful fit into a page, or worse each record overflows (and your select includes the direction field) then this could impact performance.

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread Rob Willett
We've just implemented a covering index for the last step (is it really?) in our quest to get the query execution time down. To summarise we have gone from 32 mins to 16 mins by updating an index so it doesn't use collate, we took another six minutes off by removing extra fields in the select

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread Simon Slavin
On 17 Mar 2017, at 10:12pm, Rob Willett wrote: > 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. This might also be something that Navicat should look into. The

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread Keith Medcalf
On Friday, 17 March, 2017 16:13, Rob Willett said: > 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. If you want a table field to be non-case-sensitive, the proper

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread Keith Medcalf
tt > Sent: Friday, 17 March, 2017 04:20 > To: SQLite mailing list > Subject: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ... > > Hi, > > We've been struggling with a complex query that we have written. One of > the elements of this complex query is a se

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread Rob Willett
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

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread David Raymond
re fields than what it needs for the where clause. Of course, I have been known to be wrong on these things. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Friday, March 17, 2017 2:40 PM To: SQLite mailing

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread Simon Slavin
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

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread Hick Gunter
qlite] 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.

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread Rob Willett
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

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread Simon Slavin
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 colu

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread Rob Willett
the query with UNION ALL to have 2 copies of the search (which would not affect the run time) and maybe even a temporary table of results (which would take longer and use more memory). -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im A

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread Rob Willett
I guess the query with OR will have a subprogram called once for each status value, whereas I expect the query with UNION ALL to have 2 copies of the search (which would not affect the run time) and maybe even a temporary table of results (which would take longer and use more memory). -Ur

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread Hick Gunter
esendet: Freitag, 17. März 2017 12:19 An: SQLite mailing list Betreff: Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ... Gunter, Simon, Thanks for the replies, You both seem to be drilling into the collation sequence as a possible issue. We now have a new index and we

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread Rob Willett
Gunter, Simon, Thanks for the replies, You both seem to be drilling into the collation sequence as a possible issue. We now have a new index and we have just run the query again sqlite> analyze; sqlite> drop index Disruptions_idx4; sqlite> CREATE INDEX "Disruptions_idx4" ON Disruptions ("stat

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread Hick Gunter
>On 17 Mar 2017, at 10:20am, Rob Willett wrote: > >> CREATE INDEX "Disruptions_idx4" ON Disruptions ("status" COLLATE NOCASE ASC); >> >> […] >> >> As part of the larger more complex query, we are executing the query >> >> ``` >> select * from Disruptions where status = 2 OR status = 6; > >The sche

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread Hick Gunter
>On 17 Mar 2017, at 10:20am, Rob Willett wrote: > >> CREATE INDEX "Disruptions_idx4" ON Disruptions ("status" COLLATE NOCASE ASC); >> >> […] >> >> As part of the larger more complex query, we are executing the query >> >> ``` >> select * from Disruptions where status = 2 OR status = 6; > >The sche

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread Rob Willett
Simon, Thank you for the quick response. We'll do this. We're just waiting for another analyze to finish. Our current DB is 45GB, the query works OK on smaller databases, but at this size its very slow, thats why we have a bad feeling that we're tinkering on the edges and we need a new DB des

Re: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread Simon Slavin
On 17 Mar 2017, at 10:20am, Rob Willett wrote: > CREATE INDEX "Disruptions_idx4" ON Disruptions ("status" COLLATE NOCASE ASC); > > […] > > As part of the larger more complex query, we are executing the query > > ``` > select * from Disruptions where status = 2 OR status = 6; The schema for t

[sqlite] Yet Another Why Doesn't Sqlite Use My Index question ...

2017-03-17 Thread Rob Willett
Hi, We've been struggling with a complex query that we have written. One of the elements of this complex query is a select statement that doesn't appear to use an index when we *think* it should do. We're not going to ask you to debug a large complex SQL query (unless you have nothing else to