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):

 I don't know why the index isn't used. This is what I get (includes
 sqlite3 version and schema version). The index seems to be there:

 {{{
 $ sqlite3 zone.sqlite3
 SQLite version 3.7.15.2 2013-01-09 11:53:05
 Enter ".help" for instructions
 Enter SQL statements terminated with a ";"
 sqlite> .schema
 CREATE TABLE diffs (id INTEGER PRIMARY KEY,
                     zone_id INTEGER NOT NULL,
                     version INTEGER NOT NULL,
                     operation INTEGER NOT NULL,
                     name TEXT NOT NULL COLLATE NOCASE,
                     rrtype TEXT NOT NULL COLLATE NOCASE,
                     ttl INTEGER NOT NULL,
                     rdata TEXT NOT NULL);
 CREATE TABLE nsec3 (id INTEGER PRIMARY KEY,
                     zone_id INTEGER NOT NULL,
                     hash TEXT NOT NULL COLLATE NOCASE,
                     owner TEXT NOT NULL COLLATE NOCASE,
                     ttl INTEGER NOT NULL,
                     rdtype TEXT NOT NULL COLLATE NOCASE,
                     rdata TEXT NOT NULL);
 CREATE TABLE records (id INTEGER PRIMARY KEY,
                     zone_id INTEGER NOT NULL,
                     name TEXT NOT NULL COLLATE NOCASE,
                     rname TEXT NOT NULL COLLATE NOCASE,
                     ttl INTEGER NOT NULL,
                     rdtype TEXT NOT NULL COLLATE NOCASE,
                     sigtype TEXT COLLATE NOCASE,
                     rdata TEXT NOT NULL);
 CREATE TABLE schema_version (version INTEGER NOT NULL,
                     minor INTEGER NOT NULL DEFAULT 0);
 CREATE TABLE zones (id INTEGER PRIMARY KEY,
                     name TEXT NOT NULL COLLATE NOCASE,
                     rdclass TEXT NOT NULL COLLATE NOCASE DEFAULT 'IN',
                     dnssec BOOLEAN NOT NULL DEFAULT 0);
 CREATE INDEX nsec3_byhash ON nsec3 (hash);
 CREATE INDEX nsec3_byhash_and_rdtype ON nsec3 (hash, rdtype);
 CREATE INDEX records_byname ON records (name);
 CREATE INDEX records_byrname ON records (rname);
 CREATE INDEX records_bytype_and_rname ON records
                        (rdtype, rname);
 CREATE INDEX zones_byname ON zones (name);
 sqlite> select * from schema_version;
 2|1
 }}}

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