0

My entire database is in INNDB. I love the features, hands down. However it doesn't allow full text indexing on TEXT-type columns. So I have to take my current TEXT column from my main table (INNODB) and create a MYISAM table and reference back to the original table. But because MYISAM doesn't allow FK constraints I realize I've created a potential weakness. If the original table index changes it won't cascade down into the MYISAM table. Vice versa if I create a FK link from the original table to the MYISAM table, and the MYISAM row is deleted, then I have linked to a nonexistent entry. The data consistency check is simply not there.

In short, INNODB got me too comfortable and dependent on FK constraints for my own good.

Castiblanco
  • 1,200
  • 4
  • 13
  • 32
BlackHat
  • 79
  • 2
  • 9

2 Answers2

0

I would consider not using the MyISAM fulltext indexing at all, and instead using a proper search engine alongside your db. Lucene/Solr, sphinx and xapian seem to be the leading choices (I've only used Lucene/Solr myself).

see this question for more :)

edit: also this question.

Community
  • 1
  • 1
simon
  • 15,344
  • 5
  • 45
  • 67
  • I've not worked with third party search engines before but it should be interesting albeit would add a few days delay in what I thought was going to be a straight-forward table design (stupid scope creep). But thanks for the rapid reponse, it may be the solution I require. – BlackHat Feb 14 '11 at 07:31
0

If you are using some sort of framework, the framework can control the referential integrity for you. CakePHP does a nice job of this with their Model classes.

Ben
  • 1