[bind10-dev] scalability issue with SQL(ite3) select with 'like'

[JINMEI Tatuya / 神明達哉]

I've looked into http://bind10.isc.org/ticket/324 (a very long delay
in a huge zone stored in an SQLite3 DB), and, as I updated in the
ticket I found it's because the SQLite3 module performs the
following SQL query:

"SELECT COUNT(*) FROM records WHERE zone_id=?1 AND rname LIKE (?2 || '%')"
('?2' is set to (reverse version of) some nonexistent name)

Ticket #324 suggested adding more columns to indices, but it didn't
help.  I suspect due to the nature of LIKE indices cannot help much in
general.

Is my understanding correct?  If so, is there any easy way (easy = by
not fundamentally changing the table organization) to get the result
we want to get more efficiently?  What we need to know is basically
that when we know there is no row whose name is
"nxdomain.example.com." whethere there is a row whose name is a pure
subdomain of it (e.g. "a.nxdomain.example.com.").

---
JINMEI, Tatuya