0

I'm running a django system over mysql in amazon's cloud, and the database default is innodb. But now I want to put a fulltext index on a couple of tables for searching, which evidently requires myisam.

The obvious solution is to just tell mysql to ALTER TABLE to myisam, but are there going to be any issues with that?

One that comes to mind is that I'll have to remember to do that any time I build a new version of the database, which should theoretically be rare, but there doesn't seem to be a way to tell django to please set the storage engine at the table level. I guess I could write a migration (we use south).

Any other things I might be missing? What could possibly go wrong?

Dave Orr
  • 1,082
  • 1
  • 9
  • 18

2 Answers2

2

Will the application notice? Probably not.

Will it cause problems? Only when things go wrong. MyISAM is not a transactional storage engine. If you change the data in a MyISAM table while inside of a transaction, then have to roll back changes, the changes in that table won't be rolled back. It's been a while since I tried to break it horribly, but I'm willing to wager that MySQL won't even issue a warning when this happens. This will lead to data consistency issues.

You should seriously consider external search software instead of a fulltext index, like ElasticSearch (integrates at the application level), or Sphinx (integrates at the MySQL level, though if you're using RDS instead of MySQL directly, I don't think you'll be able to use it).

Charles
  • 50,943
  • 13
  • 104
  • 142
  • Looks like sphinx isn't an option. I was hoping to hold off on something external (I looked at Solr) until I've had this in front of users for a while so I can see how they're using it and how often. – Dave Orr Mar 14 '11 at 15:52
  • Solr is also pretty good, from my understanding. Both it and ElasticSearch are based on Lucene, which is pretty much the gold standard when it comes to custom search routines. – Charles Mar 14 '11 at 15:59
1

the following may be of help:

use a myisam fulltext table to index back into your innodb tables for example: Build your system using innodb:...

Any way to achieve fulltext-like search on InnoDB

Community
  • 1
  • 1
Jon Black
  • 16,223
  • 5
  • 43
  • 42
  • Interesting. The approach is basically to build another table, replicated to it, then fulltext search that one. – Dave Orr Mar 14 '11 at 15:51
  • 1
    yup becuase you keep all the advantages of the innodb engine namely transactions, row level locking and clustered primary key indexes (think speed) and you supplement this with one or more myisam fulltext searching tables which you use to search and index back into your innodb data :) – Jon Black Mar 14 '11 at 16:00