Always-indexed MySQL indexing/searching replacements for InnoDB?

Question

I am using InnoDB for a MySQL table, and obviously queries using LIKE and RLIKE/REGEXP can take a lot of time.

I've tried Spinx, and it works great, except I have to re-index context at intervals. I can re-index every minute, but I am wondering if there is either 1) a setting in Sphinx to keep records always indexed or 2) other software besides Sphinx that will keep records always indexed.

I want it where that immediately upon inserting or updating a record, the index is updated.

Answer 1

One of the Apache Lucene projects would probably do you best.

They're designed to build reverse indexes on text content including optional abilities to do some natural language processing. Lucene is the base project, originally written in Java but now also ported to .Net - this uses a very efficient file based store for the data.

For bigger indexes there's the Solr project which is basically a service based version of Lucene that you can access via rest-ful services. This also has options for scaling such as through sharding the indexes accross multiple machines.

Answer 2

1. Are you sure your text is indexed as fulltext?
2. try to use like or boolean match against instead of regex.