MySQL

rename a table

RENAME TABLE old_table TO tmp_table,
             new_table TO old_table,
             tmp_table TO new_table;

 

re-index massive tables that are taking too long to re-index and are highly volatile

 

  • rename the table to something and create a table to accept incoming (temporary data)

 

    rename table status_log to status_log_maint; rename table status_log_temp to status_log;

 

  • optimise the renamed table

 

    optimize table status_log_maint

 

  • move big table back into place

 

    rename table status_log to status_log_temp; rename table status_log_maint to status_log;

 

 

  • move data back again:

 

    insert into status_log 
              (status_log_datetime, status_log_task_id, status_log_type, status_log_description, 
               status_log_status, status_log_booked_to_id, time, run_at_server) 
    select status_log_datetime, status_log_task_id, status_log_type, status_log_description, 
    status_log_status, status_log_booked_to_id, time, run_at_server 
    from status_log_temp;

 

  • remove temp table

 

    drop table status_log_temp

 

Lascia un commento

Il tuo indirizzo email non sarĂ  pubblicato. I campi obbligatori sono contrassegnati *