3

I have two tables in the same database (MySql) where one has been populated and there are data been added to it frequently.

I want to have the data added to the first table to be present in the other (synchronized) using a MySql command in console (without any other methods such as triggers, events etc.)

Is this possible and if so what is the command?

  • Why??? The data is sitting there ready for use. (I would rather tackle your real problem.) – Rick James Apr 1 '15 at 6:34
  • @RickJames, the table is a tracking table which has a high data insertion rate and we are generating a report with that which runs for about several minutes (optimized). When the report is generated the site get stuck. I want it so that the site will run smoothly. – Buntu Linux Apr 1 '15 at 8:11
1

This can be achieved using a mysql replace into feature provided you have either primary key or unique key implemented on both table to eradicate duplicates. Replace into have similar syntax as of insert into as when key is present on table it first delete the old row and insert the coming one.

mysql>Replace into table2 select * from table1

Either have cron job or you can use mysql event scheduler to populate the data to other table according to your desired timings.

Hope it helps

  • A caution about REPLACE. It is effectively a DELETE plus an INSERT. If the table has an AUTO_INCREMENT, the old id will be deleted and a new one generated. – Rick James Apr 1 '15 at 17:28
  • INSERT ... ON DUPLICATE KEY UPDATE ... is often a good alternative to REPLACE. It either inserts a new row (if no UNIQUE key matches) or it modifies an existing row. The AUTO_INCREMENT id is not lost. – Rick James Apr 1 '15 at 17:30
  • If both tables truly have AUTO_INCREMENT and you want them to be the same, then definitely use IODKU, not REPLACE. – Rick James Apr 1 '15 at 17:32
1

If those conditions are true :

  • you have a PK and the last inserted row always have the highest id;
  • rows are inserted in the source table and are never modified or deleted;

...then you can stick with a simple INSERT INTO. Something like :

INSERT INTO destTable SELECT * FROM srcTable 
WHERE id > (SELECT COALESCE(MAX(id),0) FROM destTable)

It will INSERT into destTable (the synced table) all the new rows from srcTable.

To run it from the console you can use

mysql -h hostname -u username -p db_name -e "INSERT INTO destTable SELECT * FROM srcTable WHERE id > (SELECT COALESCE(MAX(id),0) FROM destTable);" 
1

You seem to have a Data Warehouse application and your "reports" are causing trouble. It sounds like you are using MyISAM, which does "table locks" -- This is probably the cause for being "stuck" during a report.

Instead of a staging table that contains the entire copy of the data, simply have the 'new' record waiting to be processed.

Instead of running Reports directly from the entire 'raw' ("Fact") table, run Reports from Summary Tables. This is (1) much faster (often 10x faster for the report), and (2) avoids getting "stuck".

It might be advisable to do the summarization from the staging table. This would leave the Fact table virtually untouched, other than for writing data to it. In which case, you don't need, and should not have, many indexes on the Fact table. (Indexes slow down ingestion.)

I discuss details on how to achieve these tasks in

Since DW applications tend to be rather different, you might want to elaborate further on your app. Then I can be more specific in my advice.

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.