On 22 February 2016 at 17:07, Michele Pradella <michele.pradella at selea.com> wrote:
> CREATE INDEX indexAB ON test(DateTime,CarPlate); >>>> >>>> CREATE INDEX indexA ON test(DateTime); >>>>> >>>> >>>> So if I have a query like this >>> "SELECT * FROM table_name WHERE DateTime<VALUE_MAX AND >>> DateTime>VALUE_MIN" >>> >> > Generally speaking, I think that if you use and index on (field1) and an > index on (field1,field2) the work that SQLite have to do to create and use > each index is different. So from point of view of SELECT statement, if the > WHERE clause include only field1, is it the same for SQLite to use index on > (filed1) compared from using the index on (field1,filed2)? > It depends not only on the WHERE clause, but also the columns referenced in the initial SELECT. In this case you are selecting all columns, so if sqlite was to use indexA it would have to: 1. Use indexA to narrow down the rows with VALUE_MIN < DateTime < VALUE_MAX 2. Read all the columns from the matching rows from the "test" table On the other hand if it uses indexAB it can skip the table lookup, because the index already includes all the columns. An index which includes all the columns referenced by the entire query is called a "covering" index, which you may see the query planner referring to. Of course it's more expensive to maintain such an index - in this case each two-column index duplicates the entire data table. -Rowan
