text matching on InnoDB table

[sneakyimp]

So INNODB tables don't support full-text search indexes in MySQL so I can't use this:

select * from my_table where match(text_field_1) against('search keywords')

Am I now forced to massage my search words into a big fat LIKE clause with wildcards or is there some other way?

Also, I was a bit frustrated with full text search failing to match the keyword 'prod' with 'productions' or 'products'. Only complete word matches were taken into account.

[mpasternak]

is it possible to change the table properties in the database to myisam? or are you stuck with what yuo have?

[sneakyimp]

I asked if that was possible and the lead said NO.

[cgraz]

What about something like this?

[MarkR]

You could of course, keep a MyISAM table containing the same (or similar data), have some tool which keeps them in sync, and use a FT index on that.

That of course has some caveats:
- You need more disc space
- This need to be kept in sync
- The MyISAM table isn't transactional so it won't be rolled back if you roll your txn back.

But it should be achievable quite easily with some batch job or manually kicked off routine which finds rows which appear in one but not the other (and vice versa, for cleanup).

You CAN do a join between a InnoDB and MyISAM table, and use FT indexes on the MyISAM table.

Mark

[Sxooter]

If only there was a database with a full text search engine that works with transactional tables.
tsearch2
🙂

pgsql-general post about a benchmark of pgsql tsearch2 versus mysql's full text search
the benchmark pub is here

[sneakyimp]

go ahead and gloat sxooter! you earned it on that other post.

[Sxooter]

🙂 Hey, there are some things innodb and mysql can do that postgresql can't, like predicate locking. It makes each good at something the other isn't so good at.

Did you look at that benchmark? I don't know if you understood what all Oleg was saying in his graphs, but PostgreSQL was hugely faster on large data sets for full text searches.