On Fri, Sep 5, 2014 at 8:43 AM, Stefan Heymann [email protected] [firebird-support] <[email protected]> wrote:
> > I’m using Firebird 2.5.3, and I am looking for text in a emo field, eg. > > I’m tempted to do something like > > Select * from mytable t where lowercase(t.mymemo) like ‘%find me%’ > > but since this memo field could be enormous, I’m guessing that wold be > horribly inefficient. > > I use upper() for that and it's surprisingly fast (lowercase shouldn't > be different). > > What you can do is try to use a case insensitive collation like > unicode_ci or unicode_ci_ai, but I don't know if that really speeds > things up. > As Set said before, no index is going to help unless the text string is at the very beginning of the field. CONTAINING is the best bet - it's case insensitive, but doesn't take wild cards, so if you're looking for "%Find%me%", you'll need to use LIKE or SIMILAR and upcase the incoming value. However, you;re going to read every byte of every instance of that blob (memo) field - well, every byte of every instance that does not contain your value, and every byte up to the value if it does exist. What's needed is a full-text index, which Firebird doesn't offer. Lucene and others produce full-text indexes that can be used with Firebird (I think, haven't tried it). Good luck, Ann
