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 > > The query is: > > SELECT x.sheep_no, x.registering_flock, x.date_of_registration > FROM sheep x LEFT JOIN > Regarding: "Turning automatic_index off doesn't restore the original performance (I got bored waiting after a couple of minutes though)." The planner code, as it incorporates ever more human-like intelligence, is clearly going to sleep because you are instructing it to count sheep. [just a joke; forgive me] ------------------------ Even though the development team may want to tweak the planner algorithms, wouldn't you still want to add an explicit index for owner_person_id > (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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
