>After laboriously translating your schema and query into something >readable, I get script shown below.
Apologies. I assumed you planned on recreating the database and that readability wouldn't be an issue. >We (all SQLite developers have been consulted and agree) conclude that the >result of the query is undefined. You are sorting on the rowid of a >subquery. But the rowid of a subquery is undefined, so anything can happen >with there. I was unsure if the rowid of a subquery even existed until I selected it. I reviewed the output and determined that it was what I expected of a rowid from a subquery (simply an auto-incremented int of the result set in order). How/Why is the rowid of a subquery undefined when it always gives me the result I expect? >Was SQLite 3.7.17 really giving you the answer you expected? If so, we >think that was by luck. Yes version 3.7.17 is giving me the results I expect, but so does the latest version (3.8.0.2). The only difference is that the newest version does it much slower. >Can you recast the query in such a way that it does not sort on the rowid >of a subquery? The only reason why I am joining with a subquery and then sorting by its rowid is for performance. Originally I was simply sorting by "name" (or any other column or combination of columns) in the outer query but discovered it was exceedingly slow on large databases. I figured it was slow because I was ordering by a text column with an index that was not being used, which may or may not be true. This lead me to my subquery option that allows me to order by an int column (which I presume would be a "primary key" since it is a rowid). This gave me the performance I was looking for (the difference between ~1 second and ~15 seconds on large databases), but the cost of not being able to order by columns outside of my "Items" table. Note that my query does rely on an "ORDER BY" without a "LIMIT" in a subquery making it far from pointless is my use-case (valid or not). If I can get the performance I need while ordering in the outer query I would much rather do that. -Jared Date: Thu, 19 Sep 2013 15:50:17 -0400 > From: Richard Hipp <[email protected]> > To: General Discussion of SQLite Database <[email protected]> > Subject: Re: [sqlite] Regression: Query takes 10x longer when using > version 3.8.x > Message-ID: > <CALwJ= > [email protected]> > Content-Type: text/plain; charset=ISO-8859-1 > > After laboriously translating your schema and query into something > readable, I get script shown below. > > We (all SQLite developers have been consulted and agree) conclude that the > result of the query is undefined. You are sorting on the rowid of a > subquery. But the rowid of a subquery is undefined, so anything can happen > with there. > > Was SQLite 3.7.17 really giving you the answer you expected? If so, we > think that was by luck. > > Can you recast the query in such a way that it does not sort on the rowid > of a subquery? > > Note that an ORDER BY clause without a LIMIT clause in a subquery is > pointless and might be removed by the query optimizer. > > > CREATE TABLE Items( > uid INTEGER PRIMARY KEY ASC, > id TEXT DEFAULT '', > type TEXT DEFAULT track, > kind TEXT DEFAULT '', > error INT DEFAULT 0, > name TEXT DEFAULT '', > album TEXT DEFAULT '', > artist TEXT DEFAULT '', > genre TEXT DEFAULT '', > composer TEXT DEFAULT '', > description TEXT DEFAULT '', > popularity REAL DEFAULT 0, > frequency REAL DEFAULT 0, > band TEXT DEFAULT '', > call_letters TEXT DEFAULT '', > city TEXT DEFAULT '', > state TEXT DEFAULT '', > country TEXT DEFAULT '', > provider TEXT DEFAULT '', > label TEXT DEFAULT '', > copyright TEXT DEFAULT '', > is_explicit TEXT DEFAULT false, > is_protected TEXT DEFAULT false, > is_purchased TEXT DEFAULT false, > bpm INT DEFAULT 0, > bit_rate INT DEFAULT 0, > sample_rate INT DEFAULT 0, > format TEXT DEFAULT '', > size INT DEFAULT 0, > time INT DEFAULT 0, > url TEXT DEFAULT '', > can_play TEXT DEFAULT false, > can_pause TEXT DEFAULT false, > can_seek TEXT DEFAULT false, > can_next TEXT DEFAULT false, > can_previous TEXT DEFAULT false, > can_queue TEXT DEFAULT false, > can_order TEXT DEFAULT false, > can_like TEXT DEFAULT false, > can_rate TEXT DEFAULT false, > can_star TEXT DEFAULT false, > modified_date INT DEFAULT 0, > released_date INT DEFAULT 0, > purchased_date INT DEFAULT 0, > skipped_date INT DEFAULT 0, > track_number INT DEFAULT 0, > disc_number INT DEFAULT 0, > track_count INT DEFAULT 0, > disc_count INT DEFAULT 0, > listener_count INT DEFAULT 0, > play_count INT DEFAULT 0, > skip_count INT DEFAULT 0 > ); > CREATE TABLE Relationships( > parent INTEGER, > child INTEGER, > instance INT DEFAULT 0, > owner DEFAULT '', > relationship DEFAULT '' > ); > CREATE TABLE Defaults( > type TEXT DEFAULT '', > may_play TEXT DEFAULT true, > may_pause TEXT DEFAULT true, > may_seek TEXT DEFAULT true, > may_next TEXT DEFAULT true, > may_previous TEXT DEFAULT true, > may_queue TEXT DEFAULT false, > is_queued TEXT DEFAULT true, > may_order TEXT DEFAULT true, > may_like TEXT DEFAULT false, > is_liked TEXT DEFAULT '', > may_rate TEXT DEFAULT false, > rating REAL DEFAULT 0.0, > may_star TEXT DEFAULT false, > is_starred TEXT DEFAULT false, > added_date INT DEFAULT 0, > played_date INT DEFAULT 0 > ); > CREATE TABLE Attributes( > parent INTEGER, > child INTEGER, > instance INT, > owner TEXT, > may_play TEXT, > may_pause TEXT, > may_seek TEXT, > may_next TEXT, > may_previous TEXT, > may_queue TEXT, > is_queued TEXT, > may_order TEXT, > may_like TEXT, > is_liked TEXT, > may_rate TEXT, > rating REAL, > may_star TEXT, > is_starred TEXT, > added_date INT, > played_date INT > ); > > CREATE INDEX dtypes ON Defaults (type); > CREATE INDEX types ON Items (type); > CREATE INDEX albums ON Items (album); > CREATE INDEX genres ON Items (genre); > CREATE UNIQUE INDEX guids ON Items (id); > CREATE UNIQUE INDEX relations ON Relationships (parent, child, instance); > CREATE UNIQUE INDEX attribs ON Attributes (parent, child, instance, owner); > CREATE INDEX parents ON Relationships (parent); > CREATE INDEX childs ON Relationships (child); > CREATE INDEX instances ON Relationships (instance); > CREATE INDEX owners ON Relationships (owner); > CREATE INDEX relation ON Relationships (relationship); > CREATE INDEX kinds ON Items (kind); > CREATE INDEX attrib_parent ON Attributes (parent); > CREATE INDEX attrib_child ON Attributes (child); > CREATE INDEX attrib_instance ON Attributes (instance); > CREATE INDEX attrib_owner ON Attributes (owner); > CREATE INDEX names ON Items (name collate nocase); > CREATE INDEX artists ON Items (artist collate nocase); > > explain query plan > 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; > > -- > D. Richard Hipp > [email protected] > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
