[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