0

I have a table that is populated twice a day with data collected from other tables, and I use it to create some reports.

The steps to achieve this is the following: Get all data that will be placed on the table, truncate the table and and then insert all data again.

Is it the best way to perform this in terms of performance? Isn't there a way to updating only things that really changed, insert the new data and skip the rest?

Nielsen Martins Gonçalves
  • 342
  • 6
  • 12

2 Answers2

0

You can follow this for updating a table with merge:

UPDATE multiple tables in MySQL using LEFT JOIN

To manage the UPDATE or INSERT you can use this method:

Insert into a MySQL table or update if exists

Obviously all depends on your DB structure, what kind of data you are merging and, mainly, which keys are available.

Regards

Community
  • 1
  • 1
White Feather
  • 2,733
  • 1
  • 15
  • 21
0
CREATE TABLE new LIKE real;
INSERT INTO new ...;
RENAME TABLE real TO old, new TO real;
DROP TABLE old;

Pros/cons:

  • You always have table real. (The RENAME is 'instantaneous' and 'atomic'.)
  • It is simple.
  • It may be slower than a combination of UPDATE and INSERT, but does that really matter, based on my first comment?

If there is more than you have let on, then see http://mysql.rjweb.org/doc.php/staging_table , which discusses other aspects of rapidly "ingesting" new data.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I think this solve the problem with inserting new data. But supose some data changed, I need to update it too. This won't solve it right? – Nielsen Martins Gonçalves Mar 20 '16 at 06:13
  • Unclear... If the tables is built _entirely_ from available data, then my method works. If _only changes_ are available, then my method is fatally flawed. Please clarify your statement "... populated ... with data collected from ..." – Rick James Mar 20 '16 at 16:41