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 ("status");
sqlite> explain query plan select * from Disruptions where status = 2 OR
status = 6;
selectid|order|from|detail
0|0|0|SEARCH TABLE Disruptions USING INDEX Disruptions_idx4 (status=?)
0|0|0|EXECUTE LIST SUBQUERY 1
sqlite>
So we have a different response from the query planner, which I think is
good.
If we use your other example
sqlite> explain query plan select * from Disruptions where status = 2
UNION ALL select * from Disruptions where status = 6;
selectid|order|from|detail
1|0|0|SEARCH TABLE Disruptions USING INDEX Disruptions_idx4 (status=?)
2|0|0|SEARCH TABLE Disruptions USING INDEX Disruptions_idx4 (status=?)
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)
I'm not sure which query is going to be faster. We'll have to try and
see.
Your last suggestion of "select * from Disruptions where status =2
COLLATE NOCASE or status = 6 COLLATE NOCASE" appears to be logically
equivalent to "explain query plan select * from Disruptions where status
= 2 OR status = 6;" now we have removed the collation from the index.
sqlite> explain query plan select * from Disruptions where status =2
COLLATE NOCASE or status = 6 COLLATE NOCASE;
selectid|order|from|detail
0|0|0|SEARCH TABLE Disruptions USING INDEX Disruptions_idx4 (status=?)
0|0|0|EXECUTE LIST SUBQUERY 1
sqlite>
I'll check if we require all the fields, we require many (which I agree
is not all) of the fields. Following this logic through, does this mean
that it will do more file access bringing the records in from the file
system?
The collation issue seems to be an artifact of the way Navcat for SQLite
works. I suspect we need to be more careful about how we use the tool.
We'll now time the results of each query and run them twice to see the
affect. No idea how long this will take but suspect a few hours :) I
will post back the results as other people may (or may not) find this
helpful.
Thanks
Rob
On 17 Mar 2017, at 10:57, Hick Gunter 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.
If the index is deemed unsuitable by SQLite due to its collation
sequence, then I expect it qwould also be ignored in "select ...
status=1" (without the second ORed value)
If not, then (select ... where status =2 UNION ALL select where status
= 6) should do the trick
Do you really require all the fields from Disruptions?
And yes, collating integers with NOCASE seems quite strange (there are
no capital or lowercase numbers unless you are using roman numerals ;)
); for text affinity, it should render the comparison operators
caseblind, just like "like".
___________________________________________
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at
This communication (including any attachments) is intended for the use
of the intended recipient(s) only and may contain information that is
confidential, privileged or legally protected. Any unauthorized use or
dissemination of this communication is strictly prohibited. If you
have received this communication in error, please immediately notify
the sender by return e-mail message and delete all copies of the
original communication. Thank you for your cooperation.
_______________________________________________
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
