>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

Reply via email to