BIND 10 #414: huge zone in sqlite3 hangs b10-auth if nxdomain

[BIND 10 Development]

#414: huge zone in sqlite3 hangs b10-auth if nxdomain
-------------------------------------+-------------------------------------
                 Reporter:  jreed    |                Owner:
                     Type:  defect   |               Status:  new
                 Priority:  major    |            Milestone:  A-Team-
                Component:  data     |  Sprint-20110126
  source                             |           Resolution:
                 Keywords:           |            Sensitive:  0
Estimated Number of Hours:  30.0     |  Add Hours to Ticket:  0
                Billable?:  1        |          Total Hours:  0
                Internal?:  0        |
-------------------------------------+-------------------------------------

Comment (by shane):

 The problem here ends up being an unindexed column. The 'rdtype' column on
 the 'records' table is used in the following lookup:

 {{{
 const char* const q_previous_str = "SELECT name FROM records "
     "WHERE zone_id=?1 AND rdtype = 'NSEC' AND "
     "rname < $2 ORDER BY rname DESC LIMIT 1";
 }}}

 In this case, the SQL engine has to look through ALL entries with "rname <
 $2" in order to find ones that match "rdtype = 'NSEC'" since rdtype is
 unindexed.

 The solution is to add an appropriate index:

 {{{
 CREATE INDEX records_byrdtype ON records (rdtype)
 }}}

 The code change is simple (one line in SCHEMA_LIST[]), but we need to deal
 with being able to update the schema. We'll open a separate ticket for
 this, along with collecting a couple more SQL updates in other tickets.

-- 
Ticket URL: <https://bind10.isc.org/ticket/414#comment:5>
BIND 10 Development <http://bind10.isc.org>
BIND 10 Development