Saturday, February 23, 2013

mysql_search: Automatically create fulltext indexes in MySQL with South

If you work with Django, you'll notice that Django has a search field lookup operation. I reckon that it is rarely used because it is firstly specialized for MySQL, and secondly demanding a FULLTEXT INDEX to be created externally. syncdb does not create fulltext indexes. Neither does South.

To overcome this limitation, I have created a little package called mysql_search. It extends South's MySQL DatabaseOperations class to add appropriate fulltext indexes to suitable fields. By "suitable" I mean fields whose class names are prefixed with Fulltext. This pattern is hard coded. After you have declared your fields, a migrate command will trigger necessary changes to add fulltext indexes to your table.

There are two caveats I need to mention. First, MySQL only supports fulltext index on MyISAM tables. Therefore, mysql_search will silently convert your tables to MyISAM if you declare any fulltext field in its model. You might want to create a separate model, that mirrors your actual model, whose sole purpose is to hold fulltext indexes. Second, mysql_search creates one fulltext index for one appropriate field. It does not create a combined index on multiple fields.

The package is released under the terms of the BSD license. Enjoy!