Add support for defining MySQL FULLTEXT INDEX in the model

[edmorley]

I originally filed this against Django (https://code.djangoproject.com/ticket/26990) however it was suggested django-mysql might be a better home for now.

In MySQL 5.6 support was added to InnoDB for full text indexes:
​https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html

Currently we have to create these via a migration like:

migrations.RunSQL(
    sql='CREATE FULLTEXT INDEX `idx_description` on foo(`description`);',
    reverse_sql='ALTER TABLE foo DROP INDEX idx_description',
),

It would be ideal to be able to define them via the model DDL instead, so the full DB state can be recreated from the model.

In the Django ticket it was mentioned this might need to wait until Django 1.11's more formal support for indexes lands:
https://code.djangoproject.com/ticket/26808
django/django#6857

Many thanks!

[adamchainz]

I don't think django-mysql has a way of getting it from the model until the special index support in Django 1.11 is added. The best I think we could do until then is add a migration operation that encapsulates the above little SQL snippets, which probably isn't that useful given that it's a handful of lines.

However do check out #209 where I intend to add lookups and db expressions equivalent to the fulltext stuff added to contrib.postgres in 1.10.

N.B. InnoDB full text search only searches on committed data which might make your testing hard! You can use the non-transactional MyISAM/Aria though if you just put search data into the table, it's not crash-safe but if you build an indexing process that copies from your main tables data in InnoDB then it can work quite well and be testable.

[kukosk]

Django is already at v2.0, and the index support is there. I think it would be really nice if fulltext mysql indexes could be added in the model definition as basic indexes can be added now. Do you think a feature like this will make it into django-mysql sometimes?

[adamchainz]

Yes, when I get more time to work on it. But PR's are also welcome!

[adamchainz]

Merged into #31 .