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
