[rt-users] case insensitive searching in Content?

[Kenneth Marshall]

On Tue, Jun 08, 2010 at 09:10:23AM +0200, V?clav Ovs?k wrote:
> ... 3rd try to mail ...
> 
> On Sun, Jun 06, 2010 at 02:34:58PM -0500, Kenneth Marshall wrote:
> > Hi Zito,
> > 
> > You can look in the mailing list archives for several similar "discoveries" on
> > how MySQL works with content searches and the longblob data type. I would
> > definitely recommend using PostgreSQL for its full-text indexing available
> > in the wiki. Of course, I am partial... :)
> 
> Hi Ken,
> thanks for the suggestion. I red the wiki page
> http://wiki.bestpractical.com/view/PostgreSQLFullText already.
> I'm definitely going to try this way, also patching DBIx::SearchBuilder
> is a bit hack, but ILIKE is too slow on Pg.
> Regards
> -- 
> Zito
> 

Hi Zito,

ILIKE is fine on PostgreSQL as long as the search is anchored on
the left and there is an index. Otherwise it will do a sequential
scan of the attachments table to find matching records. The wiki
entries for full-text indexing with PostgreSQL and Oracle can avoid
the sequential scan through the use of the database's full-text
index support. I do agree that patching the actual DBIx::SearchBuilder
is not preferable. Unfortunately, since that is the API that is used
to provide a database abstraction layer and it does not yet include
the ability to specify full-text indexing details, you have to patch
it. If it is any consolation, the patch is much, much simpler (trivial)
for the PostgreSQL support versus the version that is needed for
OracleText. :)

Regards,
Ken