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

 

Sii il primo a lasciare un commento. Non essere timido!

Partecipa alla Discussione

Puoi usare i seguenti HTML tag e attributi: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>