Jump to content

POWERED BY:

Ygor Guedes

Consulta em 3 tabelas

Recommended Posts

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!

mysql.png

Share this post


Link to post
Share on other sites
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..

Share this post


Link to post
Share on other sites
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. :/

Share this post


Link to post
Share on other sites

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)

 

Share this post


Link to post
Share on other sites
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.

 

imagem.png

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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

 :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • By douglas79
      Bom dia,

      Há alguns dias que venho instalar o apache, o php, mysql e o phpmyadmin manualmente e sem obter sucesso. Até consegui rodar o php, porém, quando vou baixar a úitima versão do MYSQL, não tem todos os pacotes nele instalados, inclusive no completo, só encontro o Router.
      Alguém pode me dizer o porquê que isso está ocorrendo?
      Desde já agradeço a ajuda de vocês, que será bem vinda!
      No aguardo!

      Uso a versão 8.3.9 do PHP
      Meu SO é o Windows 10 32 bits
    • By landerbadi
      Tenho uma tabela chamada "item" com os seguintes campos: id, name, active. Nela tem cadastrado vários itens. No campo "active" eu coloco a letra "S" para informar que este item está ativo no sistema. Por exemplo: 1, casa, S 2, mesa, S 3, cama, S 4, moto S 5, rádio O quinto registro "radio" não está ativo no sistema pois não tem um "S" no campo active. E outra tabela chamada "product" com os seguintes campos (id, name) com os seguintes registros: 1, Produto A 2, Produto B 3, Produto C E uma terceira tabela chamada "product_item" com os seguintes campos (productID, itemID). No campo productID eu coloco o id de um produto da tabela "product" e no campo "itemID" eu coloco o id do produto da tabela "item". Exemplo: 1, 1 1, 3 1, 4 2, 3 2, 4 Sendo assim o produto A da tabela 'product" comtem os itens casa, cama e moto. Eu preciso fazer uma busca da seguinte maneira:  Eu escolho um registro da tabela "item", por exemplo "casa". Preciso fazer com que o php me liste todos os registros da tabela "product" que contenham a palavra "casa" e que os demais itens estejam ativos no siste. Ou seja, que contenham um "S" no campo "active"  Eu consegui fazer isso da seguinte maneira: SELECT P.id, P.name, GROUP_CONCAT(I.name ORDER BY I.name) AS items FROM product P JOIN product_item PI ON P.id = PI.productID JOIN item I ON I.id = PI.itemID AND I.active = 'S' WHERE P.id NOT IN ( SELECT PI.productID FROM product_item PI JOIN item I ON I.id = PI.itemID WHERE I.active IS NULL ) AND P.id IN ( SELECT PI.productID FROM product_item PI JOIN item I ON I.id = PI.itemID WHERE I.name = 'mesa' ) GROUP BY P.id, P.name; O problema que eu estou tendo é o seguinte:
      Quando eu jogo este código para o banco de dados onde eu já tenho os registros cadastrado o php fica lendo uma eternidade e não lista os produtos.
       
      Usando código no banco de dados que eu fiz para testes ele funciona perfeitamente pois nele tem poucos registros.
       
      No banco de dados principal a tabela "item" tem 11.196 registros. A tabela "product" tem 88.214 registros e a tabela "product_item" tem 518.378 registros. 
       
      Eu acredito que, devido o banco de dados ser muito grande, ele não consegue listar.
       
      Alguém sabe de algum meio de resolver isso?
       
       
    • By landerbadi
      Boa tarde pessoal. Estou tentado fazer uma consulta no banco de dados porém estou tendo dificuldades. Tenho uma tabela chamada "itens" com os seguintes campos: id, item, ativo. Nela tem cadastrado vários itens. No campo ativo eu coloco a letra "S" para informar que este item está ativo no sistema. Por exemplo: 1, casa, S 2, mesa, S 3, cama, S 4, moto S 5, rádio O quinto registro "radio" não está ativo no sistema pois não tem um "S" no campo ativo. E outra tabela chamada "produtos" com os seguintes campos (id, item1, item2, item3) com os seguintes registros: 1, casa, mesa, moto 2, mesa, casa, cama 3, rádio, cama, mesa Eu preciso fazer uma busca na tabela produtos da seguinte maneira: Eu escolho um registro na tabela "itens", por exemplo "mesa". Preciso fazer com que o php me liste todos os registros da tabela "produtos" que contenham a palavra "mesa". Até aqui tudo bem eu consigo listar. Estou fazendo assim: <?php $item = "mesa" $sql = mysqli_query($conn, "SELECT * FROM produtos WHERE item1 LIKE '$item' OR item2 LIKE '$item' OR item3 LIKE '$item' LIMIT 10"); while($aux = mysqli_fetch_assoc($sql)) { $id = $aux["id"]; $item1 = $aux["item1"]; $item2 = $aux["item2"]; $item3 = $aux["item3"]; echo $id . " - " . $item1 . ", " . $item2 . ", " $item3 . "<br>"; } ?> O problema é que está listando todos os registros que contém o item mesa. Eu preciso que o php verifique os demais item e me liste somente os registro em que todos os registros estejam ativos no sistema. No exemplo acima ele não deveria listar o registro 3. pois nesse registro contém o item "radio" e este item não está ativo no sistema. Ou seja, o registro "radio" na tabela itens não possui um "S" na coluna "ativo". Alguém sabe como resolver isso?
    • By First
      Olá a todos!
       
      Eu estou criando um sistema do zero mas estou encontnrando algumas dificuldades e não estou sabendo resolver, então vim recorrer ajuda de vocês.
      Aqui está todo o meu código: https://github.com/PauloJagata/aprendizado/
       
      Eu fiz um sistema de rotas mas só mostra o conteúdo da '/' não sei porque, quando eu tento acessar o register nada muda.
      E eu também quero que se não estiver liberado na rota mostra o erro de 404, mas quando eu tento acessar um link inválido, nada acontece.
      Alguém pode me ajudar com isso? E se tiver algumas sugestão para melhoria do código também estou aceitando.
       
       
      Desde já, obrigado.
    • By landerbadi
      Olá pessoal, boa tarde
       
      Tenho uma tabela chamada "produtos" com os seguintes campos (id, produto) e outra tabela chamada "itens" com os seguintes campos (id, prod_01, prod_02, prod_03, prod_04).
       
      Na tabela produtos eu tenho cadastrado os seguintes produtos: laranja, maçã, uva, goiaba, arroz, feijão, macarrão, etc.
       
      Na tabela itens eu tenho cadastrado os itens da seguinte maneira:
       
      1, laranja, uva, arroz, feijão;
      2, maçã, macarrão, goiaba, uva;
      3, arroz, feijão, maçã, azeite
       
      Meu problema é o seguinte: 
      Eu escolho um produto da tabela "produtos", por exemplo "uva".  Preciso fazer uma consulta na tabela "itens" para ser listado todos os registros que contenham o produto "uva" e que todos os demais produtos estejam cadastrados na tabela "produtos".
       
      No exemplo acima seria listado apenas dois registros, pois o terceiro registro não contém o produto "uva". 
       
      Alguém pode me ajudar? Pois estou quebrando a cabeça a vários dias e não consigo achar uma solução.
×

Important Information

Ao usar o fórum, você concorda com nossos Terms of Use.