>>> 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.
Already done this check. My last question was about to reduce the number
of indexes on table avoiding kill a "quite unnecessary" index that if
used do a better job that the other.
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)?
>
> 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.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
