CODEWITHSUNDEEP
mysqldjango
Gok Demir
Gok Demir

Reputation: 1402

Django full-text search with MySQL InnoDB

How do you configure Django for full-text search with MySQL-InnoDB. MySQL Myisam engine supports full-text search but I will use InnoDB. Is that the best way to use Sphinx? If you explain your configuration I will appreciate. (By the way I wonder how it works with postgresql too.)

Upvotes: 1

Views: 1067

Answers (3)

vikingosegundo
vikingosegundo

Reputation: 52227

May you want to have a look at Haystack

Search doesn't have to be hard. Haystack lets you write your search code once and choose the search engine you want it to run on. With a familiar API that should make any Djangonaut feel right at home and an architecture that allows you to swap things in and out as you need to, it's how search ought to be.

Upvotes: 2

bobince
bobince

Reputation: 536339

Though it can be configured to talk to MySQL automatically, Sphinx is really a separate tool to MyISAM or InnoDB. It offers much better processing and performance than the simplistic fulltext search in MyISAM, but of course the price is that querying with conditions based on both the search and the database at the same time becomes difficult. You can sort of do it using the SphinxSE (storage engine) to query and join via MySQL, but it's not as flexible as keeping it in the database engine and the joins won't perform well.

Otherwise, you can take a hybrid approach with MyISAM and InnoDB. Put all your canonical data in the proper InnoDB tables, and just use MyISAM for storing fulltext searchbait. For normal operations you just touch the InnoDB content; only when you're doing a fulltext search do you need to join the MyISAM tables. You then have to make sure you update the MyISAM tables from any new text inserted into the InnoDB tables after a successful transaction.

Whilst the lack of transaction support in MyISAM risks occasionally making the data inconsistent in concurrent or error conditions (a danger that obviously also applies to the separate fulltext store solution like Sphinx, Lucene et al), that's not a big problem in reality as it's only the searchbait that becomes inconsistent and not your actual data. You can also take the opportunity to process your fulltext content differently, for example by applying trivial stemming, since MySQL doesn't implement that itself.

PostgreSQL has its own built-in fulltext stuff based on the @@ operator. It's not quite up to Sphinx's speed, but way way ahead of MyISAM FULLTEXT in features (eg. stemming, dictionaries, better handling of relevancy values), and you can still combine it freely with other query conditions.

Upvotes: 4

Michal Čihař
Michal Čihař

Reputation: 10091

Last time I was about to make such thing I wanted to try django-sphinx, so that might be also the choice for you.

Upvotes: 1

Related Questions