On 19 Feb 2016, at 10:34am, Michele Pradella <michele.pradella at selea.com> wrote:
> 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? In cases like this, until you get an understanding of how indexes are used, create indexes which cover both orders do an ANALYZE use EXPLAIN QUERY PLAN See which index SQLite uses and delete the other one. For your example you should create both indexes: CREATE INDEX indexTAB ON test(DateTime,CarPlate); CREATE INDEX indexTBA ON test(CarPlate,DateTime); If you want to figure these things out by yourself, use the phonebook analogy. you have a phonebook. Someone says to you SELECT * FROM test WHERE (DateTime>1)AND(DateTime<100) AND (CarPlate = 'AA000BB'); "Find me everyone with a surname between 'P' and 'S' and firstname 'Michele'." Which order is more useful to you: "firstname, surname" or "surname, firstname" ? Why is the other order still a bit useful but not as useful ? Simon.
