I read many articles about comparisons of MyISAM and InnoDB from performance point of view and I decided to use MyISAM for reading data and InnoDB for writing data.

I want to use two structure like an image below. Two same (synchronized) databases but different table engines.

enter image description here

What is your suggestions about this structure? What are disadvantages of this design?

  • 2
    You do realise that "synchronising" will involve the same amount of writes to the "reading" database, thus making the separation pointless? – Philᵀᴹ Jan 15 '13 at 15:12

I recommend against using this. Consider:

This would only be of some advantage if on two separate servers (no point in mixing both on same MySQL server, naturally, since reads & writes will take place on both)

Which means you would follow RolandoMySQLDBA's advice, and set up an InnoDB master with MyISAM slave.

Do you have foreign keys on your InnoDB tables? If so, you're into integrity issues. If you happen to have ON DELETE CASCADE or ON DELETE SET NULL - prepare for bad news -- the cascading will not propagate on the slave. See: Impact of foreign keys absence on replicating slaves

OK, so maybe it's just this one table, and no foreign keys. But then you get to inherit all of MyISAM's other disadvantages, such as the likely possibility of not being able to recover from crash. Transactions won't work; the slave may find itself trying to execute queries on a table that hasn't really recovered from a crash; contains incorrect data. You may run into replication failures.

Phil's comment is also very valid: by synchronizing the tables you actually mean any write to your InnoDB table must propagate to your MyISAM table. This means same amount of writing.

If you are willing to relax some constraints, such as only batching (somehow?) the writes to the slave every once in a while, then that makes somewhat more sense.

So, in general, while the solution could work, you would have a constant headache to take care of.

| improve this answer | |
  • +1 for this more comprehensive answer, especially laying out the disadvantages. – RolandoMySQLDBA Jan 16 '13 at 12:22

This should pose no problem whatsoever provided you do the following:

Using ServerA and ServerB with no initial data

STEP 01) Setup MySQL Replication

  • ServerA as Master
  • ServerB as Slave

STEP 02) Add this line on ServerB


STEP 03) On ServerB, service mysql restart

This will automatically convert each InnoDB table into MyISAM as it replicates from ServerA

STEP 04) Load your production data (as a mysqldump) into ServerB

You will now have excellent read speed from ServerB

Let ServerA do all the heavy lifting of INSERTs, UPDATEs, and DELETEs.

I answered a question similar to this back on Aug 14, 2012 : Can I have an InnoDB master and MyISAM slaves with Full-Text for searching? . The person that implemented this was happy.

I have also suggested changing user MyISAM tables to Fixed in the past:


If you already have Data in the Master and Slave and you do not wish to reimport it from a mysqldump, you can do this

STEP 01) Setup MySQL Replication

  • ServerA as Master
  • ServerB as Slave

STEP 02) Add this line on ServerB


STEP 03) On ServerB, service mysql restart

STEP 04) Create this script

SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name', ROW_FORMAT=Fixed;')"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQLSTMT="${SQLSTMT} table_schema NOT IN ('information_schema','mysql')"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > MassConvertRowFormat.sql
less MassConvertRowFormat.sql

STEP 05) Login to MySQL on the Slave and run

source MassConvertRowFormat.sql


While this topology makes it possible for your question to be answered, this is not "one size fits all." In terms of performance, this may be feasible for one application and horrible for another. This sync of data introduces something else: Replication Lag. MySQL Replication is asynchronous by nature. Make sure you effectively monitor replication lag (Seconds_Behind_Master from SHOW SLAVE STATUS\G)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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