I have hundred thousands of records in this table:
CREATE TABLE days (
day_id INTEGER NOT NULL PRIMARY KEY,
day_date DATE
);
CREATE INDEX day_i ON days (day_date ASC);
And then if I run such query:
EXPLAIN QUERY PLAN
SELECT JULIANDAY(MAX(day_date)) FROM days;
0|0|0|SCAN TABLE days (~1000000 rows)
it will do full table scan instead of use an index.
But:
EXPLAIN QUERY PLAN
SELECT MAX(day_date) FROM days;
0|0|0|SEARCH TABLE days USING COVERING INDEX day_i (~1 rows)
so it is as I expected.
Is there any other way to rewrite such kind of queries with functions?
The query below uses an index but it is much longer:
EXPLAIN QUERY PLAN
SELECT JULIANDAY(day_date) FROM days
WHERE day_date IN (SELECT MAX(day_date) FROM days);
0|0|0|SEARCH TABLE days USING COVERING INDEX day_i (day_date=?) (~250 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|0|SEARCH TABLE days USING COVERING INDEX day_i (~1 rows)
Best regards,
Adam
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
