7

Possible Duplicate:
Is it common practice to mix InnoDB and MyISAM tables on same server?

I have a movie clips and photos table in an application; these tables contain a tag column, which allows users to search for the movies/photos by tag; however, the whole database is designed using InnoDB tables specifically for data integrity.

Since the tables are not MyISAM, I cannot use the full-text feature (which sucks). I was thinking of changing those 2 tables into MyISAM tables; however, I read somewhere that combining MyISAM and InnoDB tables is a very bad idea.

I really would want to use the fulltext feature on those tables.

My questions are these:

  1. Is it really a bad idea (performance-wise, data integrity-wise, etc) to combine those 2 tables?
  2. Is there another way of achieving the same result (the results obtained using fulltext feature) in InnoDB tables?
Community
  • 1
Kneel-Before-ZOD
  • 171
  • 4

3 Answers3

3

As long as you recognize the tradeoffs you're making, there is nothing inherently "wrong" about introducing a MyISAM table into a primarily InnoDB database for the purposes of supporting full-text searches.

Tradeoffs:

  1. No enforcement of referential integrity on MyISAM tables. Referential integrity may be enforced at the application level instead if this is critically important.
  2. Database has a mix of InnoDB and MyISAM storage engines which may go unnoticed or cause confusion for future developers and DBAs. At worst, an enterprising dev or DBA may try to "improve" the schema by changing the MyISAM table to InnoDB, thereby breaking full-text searching the application.
  3. MyISAM full text search requires MySQL-specific SQL syntax which will reduce the portability of your application.
Asaph
  • 133
  • 5
3

Aside from the answer I posted in comments, I'll focus on 2 other solutions for your full-text needs.

First, you can install Sphinx now to achieve full-text search.

Second, you can wait for 5.6, in which InnoDB will have full-text search implementation.

Derek Downey
  • 23,568
  • 11
  • 79
  • 104
2

If data integrity is important, then leave the tables as InnoDB.

If you want to have a full-text search feature, use a real search engine like Solar. Not only is Solar very good at searching, but it comes with a number of additional features that make it more useful to your users as well. The full text search features found in most databases are woefully inadequate when it comes to delivering the results users expect from a good search (thanks google).

cdeszaq
  • 176
  • 1
  • 4