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

[JINMEI Tatuya / 神明達哉]

At Tue, 13 Mar 2012 15:42:01 +0100,
Shane Kerr <shane at isc.org> wrote:

> > - We specify "STRING" as the data type for "text-like" columns, but
> >   that's not a valid type name for SQLite3 (or SQL in general I
> >   suspect), and SQLite3 regard it as an "NUMERIC" affinity:
> >   http://www.sqlite.org/datatype3.html#affinity
> >   SQLite3 doesn't use indices for LIKE unless the corresponding column
> >   is of TEXT affinity:
> >   http://www.sqlite.org/optoverview.html
> >   (See Section 4.0 "The LIKE optimization")
> > 
> >   This is the stupid error.  We should have used TEXT instead of
> >   STRING from the beginning.
> 
> Actually this turns out not to be an error at all. SQLite treats STRING
> declarations like TEXT declarations:
> 
> sqlite> create table foo (bar string);
> sqlite> insert into foo values ("baz");
> sqlite> select * from foo;
> baz
> sqlite> select typeof(bar) from foo;
> text

I don't know the relationship between the "type affinity" and the
result typeof(), but the important point here is whether indices are
used for a lookup with LIKE.  And, at least the version of SQLite3 I'm
using on my laptop doesn't use indices with this setup:

sqlite> create table foo (bar string collate nocase);
sqlite> insert into foo values ("baz");
sqlite> create index foo_by_bar on foo (bar);
sqlite> explain query plan select * from foo where bar LIKE 'ba%';
0|0|0|SCAN TABLE foo (~500000 rows)

(btw "collate nocase" is important.  Otherwise indices wouldn't be
used regardless of the type of the column.  See
http://www.sqlite.org/optoverview.html)

Note that indices are used for exact-match lookups:

sqlite> explain query plan select * from foo2 where bar='baz';
0|0|0|SEARCH TABLE foo2 USING COVERING INDEX foo2_by_bar (bar=?) (~10 rows)

If we define it as "text", indices will be used even with LIKE:

sqlite> create table foo2 (bar text collate nocase);
sqlite> insert into foo2 values ("baz");
sqlite> create index foo2_by_bar on foo2 (bar);
sqlite> explain query plan select * from foo2 where bar LIKE 'ba%';
0|0|0|SEARCH TABLE foo2 USING COVERING INDEX foo2_by_bar (bar>? AND bar<?) (~31250 rows)
sqlite> explain query plan select * from foo2 where bar='baz';
0|0|0|SEARCH TABLE foo2 USING COVERING INDEX foo2_by_bar (bar=?) (~10 rows)

I believe I can explain the issues in terms of the use of indices for
other points, but I'll stop here to save time for now.  If you're
still not convinced, please say so.  Then I'll be more specific for
the rest of the points.

---
JINMEI, Tatuya