[bind10-dev] scalability issue with SQL(ite3) select with 'like'
Michal 'vorner' Vaner
michal.vaner at nic.cz
Mon Mar 5 09:55:22 UTC 2012
Hello
On Mon, Mar 05, 2012 at 01:18:46AM -0800, JINMEI Tatuya / 神明達哉 wrote:
> const char* const q_count_str = "SELECT COUNT(*) FROM records "
> "WHERE zone_id=?1 AND rname LIKE ?2;";
>
> and have the caller append '%' to the string passed to
> sqlite3_bind_text().
Won't the sqlite library do too much escaping and „deactivate“ our %? Probably
not, but just checking.
> - I also noticed this query is slow:
> "SELECT rdtype, ttl, sigtype, rdata, name FROM records " // ITERATE
> "WHERE zone_id = ?1 ORDER BY rname, rdtype",
> Because it builds a temporary B-tree for "ORDER BY". Like the
> "previous" case, we could solve this by adding an index on (rname,
> rdtype) in this order. But in this case I wonder we could rather
> omit the "order by rdtype" part. Adding another compound index
> makes the DB file even bigger and would make update slower. Unless
> we need this ordering by the API contract it's probably better to be
> less strict.
Well, this is used to make sure RRs are aggregated to RRsets correctly, they
need to be consecutive. I think we don't need anything else than that single
RRset „sticks together“ in the output of the query and we probably could do it
in a different way. But I don't have a quick idea how.
With regards
--
~, sweet ~
Michal 'vorner' Vaner
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 198 bytes
Desc: Digital signature
URL: <https://lists.isc.org/pipermail/bind10-dev/attachments/20120305/a6a56a65/attachment.bin>
More information about the bind10-dev
mailing list