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 compare different files from 2 servers with rsync?

php linux wordpress server rsync

I got trouble with some hackers, they hacked into our website, so I cloned our server to a new one, and then reset the github on that server.

Optimize MYSQL (3) Table Join Query

php mysql

Running an EXPLAIN on some of my query tests have resulted in slow ALL joins even with indexes.