Re: [sqlite] Prefix joins

[Igor Tandetnik]

Eleytherios Stamatogiannakis  wrote:
> select * from a,b where a.c1 LIKE b.c1||'%';
> 
> but with the additional guarantee for the optimizer that all pattern
> matching will happen on the postfix and not on the prefix, so the
> optimizer will be able to use an index to do the join.

The closest you can get is something along these lines:

where a.c1 between b.c1 and b.c1 || x'FF';

This should use a full scan on b, and an index on a.c1 if available.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Prefix joins

[Eleytherios Stamatogiannakis]

Hello,

Is there a version of "LIKE", in SQLite, which makes it clear that we 
only care to have a pattern on the postfix of a column?


An example follows:

select * from a,b where POSTFIX_LIKE(a.c1, b.c1 ,'%');

In above example, POSTFIX_LIKE works in the same way as if we had written:

select * from a,b where a.c1 LIKE b.c1||'%';

but with the additional guarantee for the optimizer that all pattern 
matching will happen on the postfix and not on the prefix, so the 
optimizer will be able to use an index to do the join.


Thanks in advance,

Lefteris.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users