I have a query which takes 17 minutes to run with 3.7.3 against 800ms
with 3.7.2
The query is:
SELECT x.sheep_no, x.registering_flock, x.date_of_registration
FROM sheep x LEFT JOIN
(SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
s.date_of_registration, prev.owner_change_date
FROM sheep s JOIN flock_owner prev ON s.registering_flock =
prev.flock_no
AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00')
WHERE NOT EXISTS
(SELECT 'x' FROM flock_owner later
WHERE prev.flock_no = later.flock_no
AND later.owner_change_date > prev.owner_change_date
AND later.owner_change_date <= s.date_of_registration || '
00:00:00')
) y ON x.sheep_no = y.sheep_no
WHERE y.sheep_no IS NULL
ORDER BY x.registering_flock
explain query plan with 3.7.3:
0 0 TABLE sheep AS s
1 1 TABLE flock_owner AS prev WITH INDEX
sqlite_autoindex_flock_owner_1
0 0 TABLE flock_owner AS later WITH INDEX
sqlite_autoindex_flock_owner_1
0 0 TABLE sheep AS x
1 1 TABLE AS y
explain query plan with 3.7.2:
0 0 TABLE sheep AS s
1 1 TABLE flock_owner AS prev WITH INDEX
sqlite_autoindex_flock_owner_1
0 0 TABLE flock_owner AS later WITH INDEX
sqlite_autoindex_flock_owner_1
0 0 TABLE sheep AS x
1 1 TABLE AS y WITH AUTOMATIC INDEX
Seems the planner has missed creating an index for the second nested SELECT.
The flock_owner table has an index on each of flock_no, owner_person_id
and owner_change_date.
Pete
--
Peter Hardman
Breeder of Shetland sheep and cattle
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
