>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".
PS. Also try "select .. status = 2 COLLATE NOCASE or status = 6 COLLATE NOCASE"
just for good measure
___________________________________________
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
___________________________________________
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
