I want to do full text search efficiently in MYSQL using match against. Specially I need to use binary operators(eg. match some text but not other text). However I also need efficient transaction support.
But full text search is only supported on MYISAM, not Innodb. What to do?
views:
47answers:
3
+1
A:
Assuming you need to use Innodb.
I'd suggest using an external search tool - sphinx or solr would get my vote. Both of them consistently return better results than mysql fulltext search, and much faster. I'd say sphinx is easier to get up and going with, especially coming from mysql.
If you can't install either of them, or just don't have time, at a push, you could either move or duplicate just the necessary fields in a myisam table so that you can use mysql's fulltext search.
benlumley
2010-03-20 00:33:54
good advise, but I can't move or duplicate tables/columns, I need to delete the matching results after search, preferably done in a single SQL statement.
2010-03-20 00:39:19
+1
A:
This is a suggestion rather than answer.
If you're going to have a large database or want advanced features, don't use mysql's full-text search. You can use sphinx or lucene.
SPhinx is generally faster and lighter, but Lucene has much more features. I personally use Sphinx.
TIMEX
2010-03-20 00:40:35
You can use any language with lucene or sphinx. It's the fact that "setting it up", lucene is java-based.
TIMEX
2010-03-20 00:49:03
A:
You can also try Ferret it is a ruby-implementation of an apache searching library. According to speed tests, it is also much faster.
Maz
2010-03-20 00:44:34