Consulta em 3 tabelas

[Ygor Guedes 0]

Senhores, bom dia! Gostaria de uma imensa ajuda pois neste momento estou com uma consulta que está levando muito tempo para ser concluída; via mysql.

Gostaria de me fazer entender:

1. Na tabela status, recebo a cada 3 minutos, aproximadamente 500 registros.

2. Na tabela cadastro_ponto, estão os dados estáticos para pesquisa, que deveria estar entrelaçado com a tabela status.

3. Na tabela tab_garagens, também possui dados estáticos.

4. O campo em comum na tabela status e na tabela cadastro_pontos, é o campo onibus_ponto

5. O campo em comum na tabela cadastro_pontos e na tabela tab_garagens, é o campo local com o campo onibus_prefixo

 

Minha consulta seria a seguinte: buscar apenas o último registro de cada agrupamento de dados (agrupado pelo campo onibus_ponto com MAX(data) em que estes fiquem diferentes de 4 minutos) com um join na tabela cadastro_pontos (para verificar se existe uma associação) e um último join  na tabela tab_garagens (para verificar se existe um cadastro associado também).

 

Hoje minha consulta está assim:

SELECT s.id, s.onibus_ponto, s.data FROM status s JOIN cadastro_pontos c ON c.onibus_ponto = s.onibus_ponto JOIN tab_garagens t ON t.onibus_prefixo = c.local WHERE s.id = (SELECT id FROM status WHERE onibus_ponto = s.onibus_ponto ORDER BY data DESC LIMIT 1) AND data < (NOW() - INTERVAL 4 MINUTE)

 

No entanto, ela está demorando muito tempo para realizar toda esta consulta (de 3 a 5 minutos). Alguma coisa não está certo e eu não entendo muito de SQL; tenho apenas conhecimentos superficiais.

 

Outro detalhe: como inserir alguma coisa e fazer com que os dados se apaguem automaticamente, deixando sempre os mais novos, AGRUPADOS pela coluna onibus_ponto? Esta tabela, a cada dia, gera aproximadamente 13 mil registros.

 

Se puderem me ajudar, fico agradecido!

 

Minhas tabelas estão em anexo!

[ckcesar 7]

SELECT s.id, s.onibus_ponto, s.data FROM status s 
INNER JOIN cadastro_pontos c ON c.onibus_ponto = s.onibus_ponto 
INNER JOIN tab_garagens t ON t.onibus_prefixo = c.local 
WHERE s.id = (SELECT id FROM status WHERE onibus_ponto = s.onibus_ponto ORDER BY data DESC LIMIT 1) AND data < (NOW() - INTERVAL 4 MINUTE)

Teste usando INNER JOIN..

[Ygor Guedes 0]

1 hora atrás, ckcesar disse:

SELECT s.id, s.onibus_ponto, s.data FROM status s 
INNER JOIN cadastro_pontos c ON c.onibus_ponto = s.onibus_ponto 
INNER JOIN tab_garagens t ON t.onibus_prefixo = c.local 
WHERE s.id = (SELECT id FROM status WHERE onibus_ponto = s.onibus_ponto ORDER BY data DESC LIMIT 1) AND data < (NOW() - INTERVAL 4 MINUTE)

Teste usando INNER JOIN..

Testei sua modificação meu caro; demorou muito na consulta também. :/

[ckcesar 7]

Dependendo do seu resultado, tente gerar 2 SQL pegando os códigos  no primeiro SQL e no segundo usando IN(códigos ); Talvez serve pra você.

[Motta 645]

As tabelas tem índices ?

Um indice por data pode melhorar a coisa.

 

 

Se poderia gerar uma tabela histórico e uma diária , um event copiaria os dados da diaria para a historico e daria um truncate na diaria.

 

 

Tente tb esta sql

 

SELECT s.id, s.onibus_ponto, s.data
FROM status s
INNER JOIN cadastro_pontos c ON c.onibus_ponto = s.onibus_ponto
INNER JOIN tab_garagens t ON t.onibus_prefixo = c. local
WHERE s.data = (SELECT max(s2.data)
                FROM status s2
                WHERE s2.onibus_ponto = s.onibus_ponto
                and s2.data < (NOW() - INTERVAL 4 MINUTE))
AND data < (NOW() - INTERVAL 4 MINUTE)

 

[Ygor Guedes 0]

Em 06/11/2019 at 21:13, Motta disse:

SELECT s.id, s.onibus_ponto, s.data FROM status s INNER JOIN cadastro_pontos c ON c.onibus_ponto = s.onibus_ponto INNER JOIN tab_garagens t ON t.onibus_prefixo = c. local WHERE s.data = (SELECT max(s2.data) FROM status s2 WHERE s2.onibus_ponto = s.onibus_ponto and s2.data < (NOW() - INTERVAL 4 MINUTE)) AND data < (NOW() - INTERVAL 4 MINUTE)

 

Motta, boa tarde! Sua consulta demorou também. Eu deixei minha consulta desse jeito (menor tempo que encontrei):

SELECT m.onibus_ponto, data FROM status AS m INNER JOIN (SELECT onibus_ponto, MAX(data) AS data FROM status GROUP BY onibus_ponto) x USING(onibus_ponto, data) INNER JOIN cadastro_pontos AS c ON c.onibus_ponto = m.onibus_ponto WHERE data < (NOW() - INTERVAL 4 MINUTE) GROUP BY m.onibus_ponto

No entanto, realmente, minha tabela está ficando imensa pelo fato de ter muitos registros em pouco tempo; já está na casa de 1 milhao de registros. Pensei bem e gostaria de excluir os antigos, por grupos (onibus_ponto). Queria excluir os dados mais antigos de cada grupo deixando uns 2 registros, os mais novos, apenas.

Da imagem que segue, gostaria de deixar da coluna onibus_ponto apenas os 2 registros mais novos:

 

(12/09/19 10 AM e 12/09/19 09 AM) - onibus_ponto 10

(12/09/19 09 AM e 12/09/19 07 AM) - onibus_ponto 5

(12/09/19 10 AM e 12/09/19 05 AM) - onibus ponto 4

 

O restante, eu gostaria de excluir tudo. Assim evita de ficar uma tabela com muitos dados.

 

[Motta 645]

Pensou em particionar ?

 

https://dev.mysql.com/doc/refman/5.7/en/partitioning.html

 

 

Pense bem antes de descartar dados , informação é dinheiro. $$$

 

Volume de passageiros por hora e ponto , publicidade .... etc ...

[Ygor Guedes 0]

14 horas atrás, Motta disse:

Pensou em particionar ?

 

https://dev.mysql.com/doc/refman/5.7/en/partitioning.html

 

 

Pense bem antes de descartar dados , informação é dinheiro. $$$

 

Volume de passageiros por hora e ponto , publicidade .... etc ...

 

Bom dia Motta! Já pensei sim, porém, esta tabela é apenas uma informação sem importância; ela apenas pega o status do computador pra saber quando foi a última vez que ele comunicou com meu servidor. Por isso que a tabela está gigante. Eu não criei um robot pra isso, eu faço via SQL mesmo + ShellScript.

[Motta 645]

Beleza , como diria o Cap. Nascimento "senta o delete nesta |◇044@"

 :)