Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question

migrated from stackoverflow.com Dec 19 '11 at 16:38

This question came from our site for professional and enthusiast programmers.

marked as duplicate by jcolebrand Dec 19 '11 at 16:48

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

add comment

3 Answers

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.

share|improve this answer
    
+1 for promoting MySQL 5.6 in anticiplation of InnODB FULLTEXT indexing. –  RolandoMySQLDBA Dec 19 '11 at 16:42
    
Sorry, I ran out of votes already. I'll upvote at 7:01 PM EDT. –  RolandoMySQLDBA Dec 19 '11 at 16:43
add comment

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).

share|improve this answer
add comment

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.
share|improve this answer
    
I do this mix all the time with excellent results. If you are worried about data integrity you might want to put only that one column into a MyISAM table and join it. –  Eugen Rieck Dec 19 '11 at 16:36
    
@Eugen Rieck: you should add that as an answer: useful advice –  gbn Dec 19 '11 at 16:42
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.