Requires MySQL INSERT - SELECT query for tables with millions of records

Question

I am trying to take one step towards optimizing a 90GB + table:

Old table

Every day the table captures approx. 750,000 records from an external source and adds them to the table with a new date. This has been happening for three years now from what I understand. 97% of records do not change from one day to another.

New table

I am trying to go through an old table (millions and millions of records) and eliminate redundancy, which is likely to significantly reduce the size of the table.

old_table

• the date
• record_id
• data_field (really a lot of fields, but for example)

new_table_index

• the date
• index_id

new_table

• index_id
• record_id
• data_field

Logic when we look at every entry in old_table

if (record_id new_table) (record_id new_table, data_field)

new_table index_id

index_id record_id new_table_index

index_id new_table_index

? MySQL, . script PHP, 3 , . ??? !

Answer 1

PHP MySQL ( , ):

• PR ( INSERT - SELECT)
• , ( INSERT - SELECT)
• INSERT LINK FOR NEWLY UPDATED PRs ( SELECT - php foreach - )
• ( INSERT - SELECT)
• INSERT LINK PR ( INSERT - SELECT)

- php foreach, ! !

Answer 2

:

new_table
    * date
    * record_id (pk)
    * data_field


INSERT INTO new_table (date,record_id,data_field)
    SELECT date, record_id, data_field FROM old_table
        ON DUPLICATE KEY UPDATE date=old_table.data, data_field=old_table.data_field;

- , ​​ ​​old_table.

. mySQL

Answer 3

, , . , , MySQL: date.

script ( new_table index_id):

INSERT INTO new_table (date, record_id, data_field)
  SELECT
    date,
    record_id,
    data_field
  FROM
    old_table
  GROUP BY
    data_field

, . , .

Answer 4

, LastModified. On Insert On Update, . , LastMotified , .

, , .

, . , , .