Hello again,
Another question about max()/min() optimisation. Is there a way I can
implement a virtual table so that max()/min() of a sorted
(incrementing) column (which could be an integer primary key in a
regular table) gets fast?
For example,
sqlite> explain query plan select max(unix_time) from vtab;
0|0|0|SEARCH TABLE vtab VIRTUAL TABLE INDEX 0: (~1 rows)
Currently, "select max(unix_time) from vtab" causes SQLite to search
through millions of rows, which may take nearly half a minute for my
table, no faster than other non-sorted columns.
I've added special treatment of this sorted "unix_time" column in
xBestIndex, so that a query like:
select max(unix_time) from vtab where unix_time > strftime("%s", "2012-04-14");
runs fast (i.e. then my table will only look through a few rows at the end).
Perhaps what I'm asking is whether it's possible to add a special
treatment for max() and min() in a virtual table.
--
Steinar
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
