BIND 10 #1899: performance issue of SQLite3 "iterate" query (w/ or w/o NSEC3)

[BIND 10 Development]

#1899: performance issue of SQLite3 "iterate" query (w/ or w/o NSEC3)
-------------------------------------+-------------------------------------
                   Reporter:         |                 Owner:
  jinmei                             |                Status:  new
                       Type:         |             Milestone:
  defect                             |  Sprint-20120925
                   Priority:         |            Resolution:
  medium                             |             Sensitive:  0
                  Component:  data   |           Sub-Project:  DNS
  source                             |  Estimated Difficulty:  6
                   Keywords:         |           Total Hours:  0
            Defect Severity:         |
  Medium                             |
Feature Depending on Ticket:         |
        Add Hours to Ticket:  0      |
                  Internal?:  0      |
-------------------------------------+-------------------------------------

Comment (by muks):

 Something like this avoids the temporary Btree construction:

 {{{
 sqlite> explain query plan SELECT rdtype, ttl, sigtype, rdata, name, rname
 FROM (SELECT *, '' as hash, '' as owner FROM records WHERE zone_id=5 UNION
 SELECT *, owner as name, owner as rname, '' as sigtype FROM nsec3 WHERE
 zone_id=5 ORDER by rname, rdtype);
 2|0|0|SCAN TABLE records USING INDEX records_byrname_and_rdtype (~2 rows)
 3|0|0|SCAN TABLE nsec3 USING INDEX nsec3_byowner_and_rdtype (~1 rows)
 1|0|0|COMPOUND SUBQUERIES 2 AND 3 (UNION)
 0|0|0|SCAN SUBQUERY 1 (~3 rows)
 }}}

-- 
Ticket URL: <http://bind10.isc.org/ticket/1899#comment:6>
BIND 10 Development <http://bind10.isc.org>
BIND 10 Development