Como otimizar uma consulta SQL contendo UNION

Olá a todos,

Preciso otimizar a consulta MySQLi abaixo, A consulta é para exibir posts relacionados, estava funcioando tranquilo mas ultimamente o blog aumentou as visitas, aí começou a pesar a consulta, levando atualmente em média 0.4917s o que é um tempo absurdo para cada consulta. Preciso de ajuda pra tentar chegar em 0.04s no máximo a consulta.

Segue o código SQL:

( SELECT ID,post_name,post_update FROM `wp_posts`
INNER JOIN `wp_term_relationships` ON `wp_term_relationships`.`object_id` = `wp_posts`.`ID`
WHERE wp_term_relationships.term_taxonomy_id = '254' AND `wp_posts`.`ID` != '10768' AND wp_posts.post_status = 'publish'
ORDER BY RAND() LIMIT 2 )
UNION ALL

( SELECT ID,post_name,post_update FROM `wp_posts`
INNER JOIN `wp_term_relationships` ON `wp_term_relationships`.`object_id` = `wp_posts`.`ID`
WHERE wp_term_relationships.term_taxonomy_id = '112' AND `wp_posts`.`ID` != '10768' AND wp_posts.post_status = 'publish'
ORDER BY RAND() LIMIT 2 )
UNION ALL

( SELECT ID,post_name,post_update FROM `wp_posts`
INNER JOIN `wp_term_relationships` ON `wp_term_relationships`.`object_id` = `wp_posts`.`ID`
WHERE `wp_posts`.`ID` != '10768' AND wp_posts.post_status = 'publish'  AND (wp_posts.linguagem != 'en') 
ORDER BY RAND() LIMIT 8)

LIMIT 8

Acredito que se acharmos uma solução, isso poderá no futuro ajudar pessoas com o mesmo problema.

#-- ATUALIZAÇÕES --# Depois de muito trabalho descobri que o que estava deixando minha consulta lenta não era nem o rand(), mas sim a consulta ao "post_status" e adicionando para ele buscar antes o tipo do post "post_type" a consulta ficou muito mais rápida, cerca de 0,035s no total o que ficou num valor aceitavel pra mim

Consulta Atualizada:

( SELECT ID,post_name,post_update FROM `wp_posts`
INNER JOIN `wp_term_relationships` ON `wp_term_relationships`.`object_id` = `wp_posts`.`ID`
WHERE wp_term_relationships.term_taxonomy_id = '254' AND `wp_posts`.`ID` != '10768' AND wp_posts.post_status = 'publish'
ORDER BY RAND() LIMIT 2 )
UNION ALL

( SELECT ID,post_name,post_update FROM `wp_posts`
INNER JOIN `wp_term_relationships` ON `wp_term_relationships`.`object_id` = `wp_posts`.`ID`
WHERE wp_term_relationships.term_taxonomy_id = '112' AND `wp_posts`.`ID` != '10768' AND wp_posts.post_status = 'publish'
ORDER BY RAND() LIMIT 2 )
UNION ALL

( SELECT ID,post_name,post_update FROM `wp_posts`
INNER JOIN `wp_term_relationships` ON `wp_term_relationships`.`object_id` = `wp_posts`.`ID`
WHERE `wp_posts`.`ID` != '10768' AND wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish'  AND (wp_posts.linguagem != 'en') 
ORDER BY RAND() LIMIT 8)

LIMIT 8

Talvez essa tabela "post_status" esteja precisando de um OPTIMIZE TABLE mas ainda não me sinto seguro para usar essa função com medo de perder meus dados.

CodigosTravessos há 2 anos

Realmente é necessario fazer isto com o union?

minha sugestão:

SELECT ID ,post_name ,post_update FROM wp_posts INNER JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_posts.ID WHERE wp_term_relationships.term_taxonomy_id IN ('254', '112') AND wp_posts.ID != '10768' AND wp_posts.post_status = 'publish' AND wp_posts.linguagem != 'en' ORDER BY RAND() LIMIT 8;

Provavelmente essa consulta que fiz tera melhor desempenho porque em comparação com a primeira consulta, utilizei operador "IN" ao invés de várias cláusulas "UNION ALL". Além disso, a clausa where roda somente uma vez, em vez de rodar tres vezes.

Alekis há 2 anos

@Marilzon Acabei descobrindo que o que estava pensando na minha consulta era o último rand(), pois minha tabela tem mais de 100.000 linhas mas mesmo com rand(), sua consulta processou em 0.0064s o que já é incrivel, Mas não está retornando do mesmo jeito do código original (Editado) E testando mais a fundo no SLQ do Banco de dados, percebi que todos os posts da consulta tem relação com a categoria e com as tags, por isso que ficou leve a consulta. Pois ele não está buscando os posts aleatórios como na consulta original. Eu preciso que ele retorne 2 posts que tenha relação a tag ("254") se existir e 2 posts que tenha relação a categoria ("112") se existir E depois gere o restante aleatóriamente em no máximo 8 resultados ao todo. Existe outra forma de gerar valores aleatórios sem usar o rand()?

shedyhs há 2 anos

bom dia, chegou a testar alguma outra solução? ou quer uma explicação de como otimizar query's?

SELECT ID, post_name, post_update
FROM (
  SELECT ID, post_name, post_update, 1 AS sort_order
  FROM wp_posts
  INNER JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_posts.ID
  WHERE wp_term_relationships.term_taxonomy_id = '254'
    AND wp_posts.post_status = 'publish'
    AND wp_posts.ID != '10768'
  
  UNION
  
  SELECT ID, post_name, post_update, 2 AS sort_order
  FROM wp_posts
  INNER JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_posts.ID
  WHERE wp_term_relationships.term_taxonomy_id = '112'
    AND wp_posts.post_status = 'publish'
    AND wp_posts.ID != '10768'
  
  UNION
  
  SELECT ID, post_name, post_update, 3 AS sort_order
  FROM wp_posts
  INNER JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_posts.ID
  WHERE wp_posts.post_status = 'publish'
    AND wp_posts.ID != '10768'
    AND wp_posts.linguagem != 'en'
) AS subquery
ORDER BY RAND() 
LIMIT 8;

Alekis há 2 anos

Olá , @shedyhs obrigado pela resposta Usei sua fórmula e a consulta levou 0.9496s, no caso ficou ainda mais pesado Fazendo uns testes mais a fundo no SQL do banco de dados, Descobri que o ultimo rand() está pesando muito a consulta, pois minha tabela wp_posts tem mais de 100.000 linhas. Será que tem como gerar resultados aleatórios de uma forma mais otimizada, ou uma forma alternativa ao rand()?