SQLite rewalking the WITH table, how efficient does it do it?

I have the following query

WITH temptable AS
  (SELECT listid,
          isplayer,
          name,
          nametagname,
          OWNER,
          target,
          helditem,
          distance
   FROM SuspectListEntries indexed BY SuspectListEntriesByListID
   WHERE listid =
       (SELECT suspectList
        FROM BlocksBrokenByExplosion
        WHERE BlocksBrokenByExplosion.id=76142)
   ORDER BY distance ASC)
SELECT *
FROM
  (SELECT isplayer,
          name,
          nametagname,
          OWNER,
          target,
          helditem,
          distance
   FROM temptable
   LEFT JOIN
     (SELECT count(*)/7 AS cnt,
                           0 AS bonus
      FROM temptable) counter ON 0 = counter.bonus LIMIT 0,
                                                         7)

According to the query plan it walks the temptable twice, rebuilding everything as far as I can deduce from the query plan

"3","0","0","SEARCH TABLE SuspectListEntries USING INDEX SuspectListEntriesByListID (listid=?)"
"3","0","0","EXECUTE SCALAR SUBQUERY 4"
"4","0","0","SEARCH TABLE BlocksBrokenByExplosion USING INTEGER PRIMARY KEY (rowid=?)"
"3","0","0","USE TEMP B-TREE FOR ORDER BY"
"2","0","0","SCAN SUBQUERY 3"
"1","0","0","SEARCH TABLE SuspectListEntries USING INDEX SuspectListEntriesByListID (listid=?)"
"1","0","0","EXECUTE SCALAR SUBQUERY 5"
"5","0","0","SEARCH TABLE BlocksBrokenByExplosion USING INTEGER PRIMARY KEY (rowid=?)"
"1","1","1","SCAN SUBQUERY 2 AS counter"
"1","0","0","USE TEMP B-TREE FOR ORDER BY"
"0","0","0","SCAN SUBQUERY 1"

It walks the table twice because of my pagination method. It still does it within 0ms with a 100.000 records, so speed is not per se an issue for this question.

  1. I was just wondering, how optimized is the rewalking of a temporary view by sqlite?
  2. Does sqlite actually rebuild the temp view as is kinda implied by the query planner?
  3. Or is in the background the results of the temp view kept in memory and is walked over that and is the query planner merely showing what it would do if it didn't have the results already?

Answers


In SQLite, temporary views are handled like 'real' views, which are handled like subqueries. Two instances of the same view are never merged (unless they are flattened into their outer queries).

It would not make sense for the EXPLAIN QUERY PLAN output to lie.


Need Your Help

How to add menu in magento admin panel?

magento magento-1.7

I am looking for some advice or information on how to do the following. I want to add one more menu in magento admin panel. I am trying many ways but not working. How can i add extra one menu in ad...

How to chain NSView to simulate pagination?

cocoa pagination nsview chain

I'd like to chain several views together and have content flow from one view to the next automatically. Think of how text containers work and how their content can span across containers. Does anyone

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.