> -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Dinu Marina > Sent: donderdag 12 maart 2015 11:34 > To: sqlite-users at mailinglists.sqlite.org > Subject: Re: [sqlite] Weird (slow) TEMP B-TREE ORDER BY > > Hi Richard, > > Like said, we've already tried: > CREATE TEMP TABLE x AS (...query without outer sort...); CREATE xind ON > x (price); SELECT * FROM x ORDER BY 2 > but it also takes 30 seconds; for some really esoteric reason, CREATE > TABLE x AS ... takes all of those 30s, even though the execution plan > looks identical to the simple select (the same 72 steps). This is what I > would dare call "spooky", any explanation would be appreciated here too.
Did you try running analyze after adding some data and before looking at the query plans? With indexes that contain multiple columns adding the sqlite_stat1 table (via analyze, or in another way) may introduce huge differences in query plans and performance. We found that out the hard way in Subversion, because we have a pretty much static first column wc_id in most of our indexes in preparation for some new features. Even when it used the right indexes in query plans, it sometimes didn't use all the columns that it should. We now add an initial sqlite_stat1 table and we can trust to see a stable (good) performance from Sqlite. https://www.sqlite.org/optoverview.html#manctrl Bert
