On Mon, Oct 18, 2010 at 9:18 AM, Peter <pe...@somborneshetlands.co.uk>wrote:
> I have a query which takes 17 minutes to run with 3.7.3 against 800ms
> with 3.7.2
>
>
Thank you for the report.
Can you please send your complete schema. The query is useful in
combination with the schema but is pretty much useless without it.
Have you tried running ANALYZE? Does it help? Can you also send (in
addition to the schema) the content of the sqlite_stat1 table after you have
run ANALYZE.
Your easiest work-around for the time being is to do
PRAGMA automatic_index=OFF;
> 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
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
