Search code examples
mysqlinnodbmyisam

Using both MyIsam & Innodb


For past few days I was in a great confusion deciding whether to use MyIsam or Innodb, with both having their own pros and cons. My table will have large amount of data with heavy INSERT, UPDATE and SELECT operations.

I decided to create two tables of same structure; tbl_mytbl_innodb(innodb engine) and tbl_mytbl_myisam(myisam engine) I then created two triggers on tbl_mytbl_innodb for INSERT and UPDATE events that will insert/update tbl_mytbl_myisam. So it will always write to tbl_mytbl_innodb and read from tbl_mytbl_myisam.

Is this process correct, or do I need to do it a better way?


Solution

  • I'd say that's the opposite of a correct process. My personal recommendation is use InnoDB for almost all business activities, as it supports transactions... the only use I've found for MyISAM is full text searches (dunno why that's not available in InnoDB) but I freely admit that's a personal preference.

    Using triggers to synchronize the same data across multiple tables can't be good. Define your business requirements and choose an engine. If you need to use the other engine for a specific requirement, define that and populate a subset of data as necessary.