At Wed, 12 Mar 2014 14:38:15 +0400,
Георгий Жуйков wrote:
>
> 1 . We have a database of measurements: time DATETIME, name TEXT, value
> NUMERIC
> indexes:
> 'tags_name_index' ON 'TAGS' ('NAME' ASC)
> 'tags_name_itemtime_index' ON 'TAGS' ('NAME' ASC ', ITEMTIME' ASC)
> In case of record auto_vacuum=INCREMENTAL flag is used
[...]
> 4 . The request of data is made for time slot, i.e. from several databases.
> For example:
> SELECT COUNT (*) as COUNTER FROM Entries WHERE (TIMESTAMP BETWEEN @STARTTIME
> AND @ENDTIME)
> SELECT * from Entries WHERE (TIMESTAMP BETWEEN @STARTTIME AND @ENDTIME) of
> ORDER BY TIMESTAMP DESC LIMIT 1000 OFFSET 0
You seem to be saying that your table has columns time, name, and value;
that you index on NAME and ITEMTIME; and that you query on TIMESTAMP.
Apart from name and NAME, none of this matches up.
I expect you need an index on whatever TIMESTAMP is. If you choose to
use a compound key for the index, you'll need to take care to make
TIMESTAMP the first component of this key.
I hope this helps.
Best regards,
Niall O'Reilly
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
