2 min de leitura ·

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

2 anos atrás

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

Autor

2 anos atrás

@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

2 anos atrás

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

Autor

2 anos atrás

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()?