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 design :(


The reason for the collation is the way NavCat SQLite works, its difficult to get it without the collate but we'll do this direct from the command line.

We'll post back in an hour or so as it'll probably take that long to run :)

Rob

On 17 Mar 2017, at 10:27, Simon Slavin wrote:

On 17 Mar 2017, at 10:20am, Rob Willett <rob.sql...@robertwillett.com> 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 the table says that "status" is INTEGER.
You are supplying numbers as arguments.
Those two match and should create no problem.

But your index has a collation order which is usually used for text. I don’t see that it is obviously wrong, but it does look a little weird.

Try creating another index which is just on "status", without the COLLATE clause.
Then do another ANALYZE, then try the SELECT again.

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

Reply via email to