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

Access control inside a listbox without triggering selectionChanged event

windows-phone-7 checkbox listbox selectionchanged

For my listbox in windows phone I added a checkbox for each item with the help of this tutorial (option 2). Before I already had a SelectionChanged event for my listbox. How do I prevent firing the

Modeling the storage of multiple data types that also have parent child relationships

mysql database-design data-structures relational-database entity-attribute-value

I'm trying to design a MySQL database for a project I've started but I cannot figure out the best way to do it.