Identify and archive old Mysql records

Question

I am working on a project using database in MySql where some areas will be constantly fed generating a high number of records in a short time, such as an area for maintenance tickets. However, these data become obsolete as quickly as they are created and, after a certain time, no longer need to be used.

How can I set up a system to "archive" these records so that it no longer 'weighs' on the table?

I currently have a related table structure, where I relate customers and moderators to Ticket. Example:

tabela: Cliente
id | nome_cliente | sobrenome_cliente | etc..

tabela: Moderador
id | nome_moderador | cargo | etc..

tabela: Ticket
id | id_cliente | id_moderador | id_assunto  | etc..

I could just create a new column arquivado on the table Ticket and use a SQL that select all but the archived ones. But still they would be registered in that table.

Creating a new duplicate table from the Ticket table would be the solution? Ex.: ticket_arquivos and then move the records to her?

Or what other method I can use to do this management?

---

Another question regarding this would be the identification of these obsolete data. I currently have the column status and data_update, where the status must be closed and the current date must be more than 15 days from the update date. It is possible to make this column data_update automatically update regardless of the type of sql that she suffers? Be one UPDATE or a SELECT?

Answer 1

Friend, I believe that the best solution is really to create a table that receives these records "obsolete".

You could create a job (http://dev.mysql.com/doc/refman/5.7/en/events-overview.html) which runs periodically and which will be responsible for creating the records in the file table and delete from the original table.

Example of job (or Event, as it is called in the Mysql):

delimiter |

CREATE EVENT e_arquivamento_diario
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'Transfere registros obsoletos para tabela de arquivamento'
    DO
      BEGIN
        INSERT INTO meu_esquema.meu_arquivo (col1, col2, col3)
          SELECT col1, col2, col3
            FROM meu_esquema.minha_tabela
           WHERE status = 'encerrado'
             AND DATEDIFF(CURDATE(), data_update) >= 15;

         // aqui também adicionar código de exclusão da tabela original

      END |

delimiter ;

Regarding the maintenance of the column data_update you can use a Trigger (https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html) to update your value in INSERT and UPDATE, however there is no such device to control access to the registry via SELECT. I believe that in this case it would have to be controlled via system.

One Trigger is triggered when an operation of INSERT, UPDATE or DELETE is executed in a table. The code allocated in the Trigger can be executed before (BEFORE) or after (AFTER) of the original operation.

Example of Trigger which will be executed before the INSERT, to fill the value of a given column:

CREATE TRIGGER ins_data_update BEFORE INSERT ON minha_tabela
   FOR EACH ROW SET NEW.data_update = CURDATE();

Trigger for UPDATE:

CREATE TRIGGER upd_data_update BEFORE UPDATE ON minha_tabela
   FOR EACH ROW SET NEW.data_update = CURDATE();

Note: the values, table names and columns of the examples are only illustrative and should be replaced by real values and names for execution in production environment.