Nicolas Williams <[EMAIL PROTECTED]> wrote:
> On Mon, Jan 28, 2008 at 10:07:01AM +0100, Ralf Junker wrote:
> > Hello Bharath Booshan L,
> >
> > >>select * from t where filepath regexp '/MyMovie(\.[^\.]+)*$';
> > >
> > >Will this query use index, if we had one, on filepath?
> >
> > No. It will do a full table scan.
>
> But it could, no?
>
> I suppose that to make this generic so that users can replace the
> regexp, like, and glob functions would require some new interfaces.
> SQLite would have to be able to extract a constant prefix from the
> pattern in order to be able to use an idex in this case.
>
Or, you could say:
SELECT * FROM t
WHERE filepath REGEXP '/MyMovie(\.[^\.]+)*$'
AND filepath GLOB '/MyMovie.*'
The second GLOB term will do the indexing. Then the
REGEXP is applied to the all the terms that the GLOB
matches to do a second layer of filtering.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
