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.
-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
