BIND 10 #2610: Query for large zone in SQLite datasrc takes a very long time

[BIND 10 Development]

#2610: Query for large zone in SQLite datasrc takes a very long time
-------------------------------------+-------------------------------------
            Reporter:  vorner        |                        Owner:
                Type:  defect        |                       Status:  new
            Priority:  medium        |                    Milestone:  Next-
           Component:  data source   |  Sprint-Proposed
            Keywords:                |                   Resolution:
           Sensitive:  0             |                 CVSS Scoring:
         Sub-Project:  DNS           |              Defect Severity:
Estimated Difficulty:  0             |  Medium
         Total Hours:  0             |  Feature Depending on Ticket:
                                     |          Add Hours to Ticket:  0
                                     |                    Internal?:  0
-------------------------------------+-------------------------------------

Comment (by vorner):

 Hello

 Replying to [comment:11 jinmei]:
 > Okay, then I suspect the most likely bottleneck is "hasSubdomains()",
 > which requires this SQL query:

 That seems possible, the query itself takes about a second and it is
 called
 multiple times from `findWildcardMatch` too (the other query called from
 this
 method is the usual one for direct lookup).

 > So I'd try this manually:
 > {{{
 > sqlite> SELECT rdtype, ttl, sigtype, rdata FROM records WHERE zone_id=xx
 AND rname LIKE 'cz.nic.ns.c.%'
 > sqlite> explain query plan SELECT rdtype, ttl, sigtype, rdata FROM
 records WHERE zone_id=XX AND rname LIKE 'cz.nic.ns.c.%'
 > }}}

 It is indeed slow:
 {{{
 SELECT rdtype, ttl, sigtype, rdata FROM records WHERE zone_id=1 AND rname
 LIKE 'cz.nic.ns.c.%';
 CPU Time: user 0.966666 sys 0.080000

 explain query plan SELECT rdtype, ttl, sigtype, rdata FROM records WHERE
 zone_id=1 AND rname LIKE 'cz.nic.ns.c.%';
 0|0|0|SCAN TABLE records (~50000 rows)
 }}}

 It doesn't seem to use the index much (it probably does something, since
 it
 says it'd scan 50k rows, but the table contains 1911305 rows in the zone).

 Looking at the sqlite3 webpage, it should use the index. But I don't know
 why
 it doesn't.

 Anyway, we don't really need to know if the result is NXRRSET or NXDOMAIN
 in
 this case, since these are the additional records and the result would be
 the
 same. Should we add a flag to disable that kind of check with subdomains?
 We
 probably still need it for the general case, though, so we should make the
 query faster somehow too.

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