On 22 Feb 2016, at 8:39am, Michele Pradella <michele.pradella at selea.com> wrote:
>> Your indexes are badly designed. >> >> You require the following two indexes: >> CREATE INDEX indexAB ON test(DateTime,CarPlate); >> CREATE INDEX indexBA ON test(CarPlate,DateTime); >> >> The indexes: >>> CREATE INDEX indexA ON test(DateTime); >>> CREATE INDEX indexB ON test(CarPlate); >> serve no useful purpose and should be dropped. > > So if I have a query like this > "SELECT * FROM table_name WHERE DateTime<VALUE_MAX AND DateTime>VALUE_MIN" > do you think I can use indexA or indexAB and the results in terms of > performance are the same? is there some "more work" that query have to do if > we use indexAB instead index? Here is your original SELECT: SELECT * FROM table WHERE (field1 LIKE 'TEXT%')AND(field2>=X)AND(field2<=Y) The question of which index is best depends on how 'chunky' field1 and field2 are. Perhaps half the values in field1 are LIKE 'TEXT%'. In that case filtering on field1 first does not help much in narrowing down your selection, and SQLite would do better filtering on field2 first. The way to find this out is to do what I wrote in my previous post. Create your table. Put data in your table like the data you'll be using in real life. Define the two indexes Dr Hipp listed above. Run the SQL command ANALYZE. Use EXPLAIN QUERY PLAN on your SELECT command and see which index SQLite chose to use. Once you're used to how it works you won't have to do this any more, you will just naturally know what index to make. Simon.
