Otimização de Consultas

[zeke_ 2]

Galera, fui surpreendido ontem pela hospedagem do meu site, aonde a mesma bloqueou meu banco de dados alegando que o mesmo estava com consultas muito lentas assim sobrecarregando o servidor.

Eles me enviaram um relatório aonde constava algumas consultas feita pelo sistema ao banco de dados, onde a mais lenta era a seguinte:

SELECT news_categories.category_name, news_posts.news_id, news_datepublish,news_summary, news_headline, news_title, news_frienflytitle, news_image_frontend
FROM news_posts
INNER JOIN news_categories ON news_posts.news_category_id = news_categories.category_id
AND news_panel_id =1
WHERE news_posts.news_status =1
AND news_datepublish <= DATE_ADD( NOW( ) , INTERVAL -1 HOUR )
ORDER BY news_posts.news_datepublish DESC
LIMIT 5;

Essa consulta acima (Segundo a hospedagem) está deixando o servidor lento, porém não sei como otimizar ela, pois a tabela "news_posts" existem mais de 90Mil registros.

 

Alguém poderia me ajudar nisso?

Desde já, muito obrigado.

[lokaodomau 61]

Quais os índices destas tabelas?

[Motta 645]

Se houver índices , as estatísticas das tabelas estão atualizadas ?

[zeke_ 2]

Segue os índices e estatísticas da tabela "news_posts"

 

Segue os índices e estatísticas da tabela "news_categories"

Obrigado pela atenção.

[lokaodomau 61]

Tente utilizar um índice que utilize primeiro o campo `new_status`, depois o campo `new_datepublish`, em sequencia.

 

Não sei explicar direito, mas vou tentar... Nenhum dos outros índices já criados ajudam para esta consulta, pois o banco de dados utiliza o índice na ordem em que os campos foram informados, e como o filtro começa pelo campo `new_status` (último campo relacionado em todos os índices), acredito que o banco não utilize índices neste caso.

 

Pode utilizar um EXPLAIN antes da consulta, para ver qual índice é utilzado no campo `key`, também pode ver quais possíveis outros índices poderiam ser utilizados em `possible_keys`.

 

O próprio banco tenta otimizar a consulta. Não sei se expliquei corretamente, mas pode ter uma noção melhor neste link.

[zeke_ 2]

lokaodomau, primeiramente, obrigado por ter respondido.

Deixa eu ver se entendi isso direito, então terei que criar um index para cada campo que eu utilizar na cláusula where?

[Motta 645]

Tem de ver também que algumas consultas não podem ser otimizadas pela sua natureza , esta parece ser uma pois a busca

news_datepublish <= DATE_ADD( NOW( ) , INTERVAL -1 HOUR )

trás TODAS as datas praticamente da tabela um índice aí de pouco adiantaria, em geral o otimizador verifica se a quantidade de registros a ser lido por um índice é muito grande é "mais barato" fazer um full table scan.

[lokaodomau 61]

Veja a cardinalidade dos índices.

Inclusive, suspeito que a coluna `news_id` seja auto_increment, digo isso porque a cardinalidade não aumenta levando em consideração os outros campos `news_category_id`, `news_panel_id` e `news_site_id`. Se a coluna `news_id` não tem valor repetido, aconselho a remover as demais colunas do índice.

Veja a cardinalidade do índice `idx_3`, que só possui a coluna `news_category_id`. Dê uma olhada também no índice `idx_2`, é o exemplo que o @Motta citou, quando a coluna `news_datepublish` é relacionada, a cardinalidade aumenta consideravelmente.

[zeke_ 2]

A coluna news_id é auto_increment mesmo... exclui todos os index e recriei os novamente, porém de nada resolver, a cardinalidade continua altíssima conforme imagem abaixo:

Já não sei oq fazer sobre como otimizar essa consulta e estou desesperado, se puderem me dar dicas...

 

Muito obrigado.

[lokaodomau 61]

Acho que agora entendi a explicação do @Motta, teria que criar a limitação de data, tentando encontrar as 5 últimas publicações por data.

 

Faz um teste com a query:

SELECT MAX(news_datepublish) AS datepublish
FROM news_posts
GROUP BY id
ORDER BY `datepublish` DESC
LIMIT 5

Veja também quanto tempo demorou para executar.