> (please don't top-post)
>
> Michele Pradella wrote:
>> so there's no way to use that kind of double column index on a select like
>> explain query plan select * from test where (CarPlate LIKE 'AA000%') AND
>> (DateTime>1);
>> because at least one field have to do with operator = correct?
> No, the number of "initial columns" that use "=" might be zero.
>
> This query can user either an index that has DateTime as first column,
> or an index that has CarPlate as first column (if the column has text
> affinity, and if the index use a case-insensitive collation):
Ok but the question is: can a statement (SELECT * FROM table WHERE
(field1 LIKE 'TEXT%')AND(field2>=X)AND(field2<=Y)) use the index created
on multicolumn field1,field2? or I use it only if I have file1 IN,=,IS?
Which is the best index to use here to have the best result in terms of
query speed?
>
> CREATE INDEX indexA ON test(DateTime);
> CREATE INDEX indexB_for_LIKE ON test(CarPlate COLLATE NOCASE);
>
> To be able to use a normal (case-sensitive) index, you'd need to do
> a case-sensitive search: (CarPlate GLOB 'AA000*').
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
