How to syn identical innodb and read-only myisam tables

Question

I am planning to have 2 identical tables, one in innodb, another in myisam. innodb table is for writing and updating and myisam table is purely for reading only.

What is the best way to sync the the myisam table with the innodb table? The solution doesn't have to be real time. If I can sync them once a day, that would be good enough.

I search through forum and someone suggest insert table select * from table_readonly. But it's a large table with millions of row. It will take me 5 hours to complete the insert.

Any better suggestion?

Answer 1

1. Why so strange a setup? Unless you have a highly multithreaded environment, theres no much difference in performance between these two engines.

2.

 INSERT INTO myisamTable 
 SELECT * 
 FROM innoDbTable AS t1 
 LEFT JOIN myIsamTable AS t2 
 ON (t1.PK = t2.PK) -- use your Primary key for join
 WHERE t2.PK IS NULL  -- this way only rows that do not exist yet i myIsamTable will be copied

1. Why not use AFTER UPDATE and AFTER INSERT triggers?

Answer 2

Maybe a real time solution would be the best choice (surely, it will slow down each operation; however, it might be ok in your case), just define AFTER INSERT/UPDATE/DELETE triggers on a working table to keep 2 tables synchronized all the time. If you prefer to run synchronization query periodically, I'm afraid that it will take comparatively long time in any case. You can improve it as Mchl proposed. If you need to take care of updated records, you can do

REPLACE myisamTable 
INNER JOIN 
( 
   SELECT t1.* 
   FROM innoDbTable AS t1 
   LEFT JOIN myIsamTable AS t2 
   ON (t1.PK = t2.PK AND t1.field1 = t2.field1 AND t1.field2 = t2.field2 ... etc) 
  // join by all fields to detect changes. if field allows NULL, then instead of 'AND (t1.f1 = t2.f1)'
  // you need to write 'AND (t1.f1 = t2.f1 OR (t1.f1 IS NULL AND t2.f1 IS NULL))`
   WHERE t2.PK IS NULL
)changes ON (myisamTable.Pk = changes.Pk)
SET field1 = changes.field1, field2 = changes.field2 , //... etc

Keep in mind that you also need to take care of deleted records (if any) - it should be one more query that deletes them from the read only table.