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

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 ...

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

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

"... 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

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

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

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

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

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

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 ...

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

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

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

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

Change your query to explain query plan select * from Disruptions where status = 2 collate nocase OR status = 6 collate nocase; to match your index. > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Rob Willett > Sent:

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

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

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

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 list Subject: Re: [sqlite] Yet

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

ers@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 :) Ou

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

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

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 ?

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

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 Auftrag von Rob Willett Gesendet: Freitag, 17. März 2017 12:19 An: SQLite mailing li

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

ce 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). -Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlit

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

esendet: Freitag, 17. März 2017 12:19 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> 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 possibl

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 have just run the query again sqlite> analyze; sqlite> drop index Disruptions_idx4; sqlite> CREATE INDEX "Disruptions_idx4" ON Disruptions

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

>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

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

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

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

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