Michele Pradella wrote: >> 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?
No; all columns but the last actually used in a index must use =. So with this query, only one column can use an index. > Which is the best index to use here to have the best result in terms of query > speed? This depends on which comparison has the greater selectivity (i.e., removes more rows from the output). Run ANALYZE to let SQLite handle this: <http://www.sqlite.org/lang_analyze.html>. Regards, Clemens
