Criei um aplicativo que busca relatórios de uma fonte externa. Ele baixa os dados do relatório uma vez por dia. Cada relatório cobre o período de agora até 30 dias atrás.
Por exemplo, digamos que é 1º de maio e o relatório foi baixado nos últimos 30 dias e terminamos com 30 linhas na tabela (vamos fazer com que cada dia seja representado por uma linha). Então, em 2 de maio, buscamos o relatório e terminamos com 31 linhas na tabela - 1 linha do relatório obtido em 1º de maio e 30 linhas de 2 de maio. 29 linhas mais antigas que se sobrepõem são descartadas. As linhas do relatório mais recente as substituem. Então, em 3 de maio, terminamos com 32 linhas na tabela, 4 de maio, 33 linhas, etc.
Agora a consulta que faz a limpeza dos dados antigos na minha aplicação fica assim (registros com a mesma data que possuem um ID menor são excluídos):
DELETE FROM $tableName WHERE id NOT IN (SELECT MAX(id) FROM (SELECT * FROM $tableName) AS sth GROUP BY $date
O problema é que essa consulta é muito ineficiente. À medida que a quantidade de dados na tabela aumenta, ela começa a matar o servidor de banco de dados, pois cada instrução select é aplicada a todo o conjunto de dados. Também NOT INe MAXtornam-se cada vez menos eficientes à medida que o número de linhas aumenta.
O sistema precisa atualizar as linhas do passado, pois a fonte externa normaliza os backwords de dados no tempo, de modo que os registros do passado mudem ocasionalmente e estou sempre interessado nos últimos 30 dias.
Eu ficaria grato por conselhos sobre como posso otimizar esta parte do aplicativo, como posso superar o problema de desempenho desta SELECTdeclaração DELETE.
