On Mon, Sep 9, 2013 at 8:27 PM, Jared Albers <jaredaalb...@gmail.com> wrote:
> This query takes 10x longer to execute when using versions 3.8.x. Step back > to 3.7.17 or older and the query is much faster. I checked the query plans > of both versions and they are identical. > Please post: (1) Your complete schema (2) The content of the sqlite_stat1 and sqlite_stat3 tables, if those tables exist. (3) The output of the sqlite3_analyze.exe utility run on your complete database. We need the above information to investigate your issue. We especially need (1) and (2). > > -Jared > > Query: > SELECT R.child, R.instance, R.owner, R.relationship, I.*, NS.rowid AS sort, > COALESCE(L1.may_play, L2.may_play, L3.may_play, L4.may_play, L5.may_play, > L6.may_play, L7.may_play, L8.may_play, D.may_play) AS may_play, > COALESCE(L1.may_pause, L2.may_pause, L3.may_pause, L4.may_pause, > L5.may_pause, L6.may_pause, L7.may_pause, L8.may_pause, D.may_pause) AS > may_pause, COALESCE(L1.may_seek, L2.may_seek, L3.may_seek, L4.may_seek, > L5.may_seek, L6.may_seek, L7.may_seek, L8.may_seek, D.may_seek) AS > may_seek, COALESCE(L1.may_next, L2.may_next, L3.may_next, L4.may_next, > L5.may_next, L6.may_next, L7.may_next, L8.may_next, D.may_next) AS > may_next, COALESCE(L1.may_previous, L2.may_previous, L3.may_previous, > L4.may_previous, L5.may_previous, L6.may_previous, L7.may_previous, > L8.may_previous, D.may_previous) AS may_previous, COALESCE(L1.may_queue, > L2.may_queue, L3.may_queue, L4.may_queue, L5.may_queue, L6.may_queue, > L7.may_queue, L8.may_queue, D.may_queue) AS may_queue, > COALESCE(L1.is_queued, L2.is_queued, L3.is_queued, L4.is_queued, > L5.is_queued, L6.is_queued, L7.is_queued, L8.is_queued, D.is_queued) AS > is_queued, COALESCE(L1.may_order, L2.may_order, L3.may_order, L4.may_order, > L5.may_order, L6.may_order, L7.may_order, L8.may_order, D.may_order) AS > may_order, COALESCE(L1.may_like, L2.may_like, L3.may_like, L4.may_like, > L5.may_like, L6.may_like, L7.may_like, L8.may_like, D.may_like) AS > may_like, COALESCE(L1.is_liked, L2.is_liked, L3.is_liked, L4.is_liked, > L5.is_liked, L6.is_liked, L7.is_liked, L8.is_liked, D.is_liked) AS > is_liked, COALESCE(L1.may_rate, L2.may_rate, L3.may_rate, L4.may_rate, > L5.may_rate, L6.may_rate, L7.may_rate, L8.may_rate, D.may_rate) AS > may_rate, COALESCE(L1.rating, L2.rating, L3.rating, L4.rating, L5.rating, > L6.rating, L7.rating, L8.rating, D.rating) AS rating, COALESCE(L1.may_star, > L2.may_star, L3.may_star, L4.may_star, L5.may_star, L6.may_star, > L7.may_star, L8.may_star, D.may_star) AS may_star, COALESCE(L1.is_starred, > L2.is_starred, L3.is_starred, L4.is_starred, L5.is_starred, L6.is_starred, > L7.is_starred, L8.is_starred, D.is_starred) AS is_starred, > COALESCE(L1.played_date, L2.played_date, L3.played_date, L4.played_date, > L5.played_date, L6.played_date, L7.played_date, L8.played_date, > D.played_date) AS played_date, COALESCE(L1.added_date, L2.added_date, > L3.added_date, L4.added_date, L5.added_date, L6.added_date, L7.added_date, > L8.added_date, D.added_date) AS added_date FROM Items I INNER JOIN Defaults > D ON I.type = D.type INNER JOIN Relationships R ON R.child = I.uid INNER > JOIN (SELECT uid FROM Items ORDER BY name COLLATE NOCASE ASC) NS ON NS.uid > = I.uid LEFT OUTER JOIN Attributes L1 ON L1.parent = R.parent AND L1.child > = R.child AND L1.instance = R.instance AND L1.owner = '' LEFT OUTER JOIN > Attributes L2 ON L2.parent = R.parent AND L2.child = R.child AND > L2.instance = R.instance AND L2.owner IS NULL LEFT OUTER JOIN Attributes L3 > ON L3.parent = R.parent AND L3.child = R.child AND L3.instance IS NULL AND > L3.owner = '' LEFT OUTER JOIN Attributes L4 ON L4.parent = R.parent AND > L4.child = R.child AND L4.instance IS NULL AND L4.owner IS NULL LEFT OUTER > JOIN Attributes L5 ON L5.parent = R.parent AND L5.child IS NULL AND > L5.instance IS NULL AND L5.owner = '' LEFT OUTER JOIN Attributes L6 ON > L6.parent = R.parent AND L6.child IS NULL AND L6.instance IS NULL AND > L6.owner IS NULL LEFT OUTER JOIN Attributes L7 ON L7.parent IS NULL AND > L7.child = R.child AND L7.instance IS NULL AND L7.owner = '' LEFT OUTER > JOIN Attributes L8 ON L8.parent IS NULL AND L8.child = R.child AND > L8.instance IS NULL AND L8.owner IS NULL WHERE R.parent = (SELECT parent > FROM Relationships WHERE rowid = 2) ORDER BY sort ASC LIMIT 100; > _______________________________________________ > 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
