Bugz : Consultoria Digital

SQL – Buscas e mais buscas

Na minha briga interminável por otimizar um site que me perturba aqui no trabalho, acabei me deparando com uma busca personalizada.
Usamos um WordPress como base para o sistema, porém, devido a um número quase abusivo de custom fields, nossa busca precisa ser personalizada. De cara, nossa busca estava com uma série de problemas… Paginação, busca com “LIKE” em campos TEXT e LONG TEXT, vários INNER JOINs e por aí vai…
O primeiro ponto que enderecei foi a paginação.

O que eu costumo a ver nos forums e afins é a opção de fazer um SELECT COUNT() na query que você vai rodar e depois retornar a query efetivamente com um LIMIT.
Exemplo:

1
2
3
4

# você busca o COUNT
SELECT COUNT(id) FROM tabela WHERE condição;
# depois usa o COUNT para controlar a paginação e coloca limits na query que retorna resultado
SELECT id, nome, title FROM tabela WHERE condição LIMIT 0,10;

Isso para grande parte das queries simples, funciona relativamente bem, porém, como a nossa query é bem complexa, tive que achar uma solução menos “intensa” no servidor…

Pesquisando um pouco, achei uma solução usando o próprio MySQL. O comando é o SQL_CALC_FOUND_ROWS, que basicamente armazena no MySQL o número de resultados dessa query.
Como ele funciona efetivamente? Veja o exemplo:

1
2

#fazemos o nosso SELECT, passando o CALC_ROWS na parte dos campos.
SELECT SQL_CALC_FOUND_ROWS id, nome, title FROM tabela WHERE condição LIMIT 0,10;

Note que passamos o LIMIT diretamente na nossa query…

Em seguida, execute uma nova query:

1
2

#Busca o numero de resultados
SELECT FOUND_ROWS() AS total_de_linhas

Nesse modelo, embora você ainda precise fazer duas queries para buscar o total de resultados e uma listagem “truncada” de resultados, você faz uma query pesada (a sua principal) e uma leve apenas para retornar os valores.

Só para referência, com essa mudança básica, o tempo de execução da query baixou um aproximadamente 50% (de 12s em média para uns 6s) – isso é óbvio se considerarmos que estamos somente rodando uma query complicada. (no final do post eu coloco a query complicada para os amantes de SQL).

Uma vez resolvido esse problema, fui buscar uma melhora de performance na query em si.

Percebi que no WordPress, os campos, wp_posts.title e wp_posts.content não são indexados. Para tentar minimizar esse problema, criei indices FULLTEXT nos dois campos e mudei a query de um LIKE para um MATCH() AGAINST().
O preço dessa mudança é uma imprecisão maior nos resultados. Embora você tenha dezenas de opções para utilizar o MATCH (busca booleana, natural language, expanded e por aí vai) você acaba tendo resultados um pouco mais genéricos do que com o LIKE ‘%termo%’.
O MATCH, além de ser mais eficiente por usar colunas indexadas acaba também trazendo uma enorme vantagem se usado no modo BOOLEAN. Se você fizer uma busca por exemplo “sapato marrom”, seu query de like (supondo que você não trate separadores e etc..) vai retornar exclusivamente os conteudos com “sapato marrom” nelas… as vezes é isso que queremos, mas, minha experiência me mostrou que em 99% dos casos, o cliente quer que retornemos “sapato” e/ou “marrom”. Com o MATCH no modo BOOLEAN, isso já é nativo…
Para entender melhor como usar o MATCH AGAINST, aqui vai uma super breve explicação (você pode achar tudo no site do MySQL)

1
2
3
4

SELECT
   POST_ID FROM POSTS
WHERE
   MATCH (POSTS.TITLE, POSTS.CONTEUDO) AGAINST ('sapato marrom' IN BOOLEAN MODE)

Isso essencialmente retorna todos os POST_ID da tabela POSTS que contém “sapato” e ou “marrom” tanto no título como no conteúdo.
Simples né?
Basta lembrar de indexar suas colunas como FULLTEXT.

Finalmente, para os curiosos, esta é a query que uso para trazer os resultados de busca no site:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

SELECT SQL_CALC_FOUND_ROWS
	DISTINCT(wp_posts.ID),
	wp_posts.post_title,
	wp_posts.post_type,
	wp_posts.guid
FROM wp_posts
	INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
	INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
	INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
	INNER JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id)
WHERE
  (
	MATCH(wp_posts.post_title, wp_posts.post_content) AGAINST ('TERMO')
	OR MATCH(wp_postmeta.meta_value) AGAINST ('TERMO')
	OR (wp_terms.name LIKE '%TERMO%')
  )
  AND
  wp_term_taxonomy.term_id NOT IN (1,5,6,7,8,9,10,11)
  AND
  (wp_posts.post_status = 'publish')
  ORDER BY wp_terms.term_id
  LIMIT 0,30

Uma breve explicação:

Na linha 1 : temos o SQL_CALC_FOUND_ROWS que nos trará o total de registros encontrados depois.
Linahs 7 – 10 : os inner joins que trazem todos os itens que precisamos.
Linhas 13 e 14 : Temos dois MATCHs – isso porque estamos usando o MATCH em duas tabelas distintas… isso infelizmente é necessário.
Linha 15 : O termo aqui precisa ser exatamente contido no campo wp_terms.name e por isso usamos o LIKE (para sitauções como a descrita acima onde o cara procura “sapato marrom”)
Linha 18 : Excluimos algumas categorias que não são buscáveis dentro do WordPress
Linha 22 : Já incluimos o LIMIT que nos trará somente os primeios 30 resultados – o Zero é trocado posteriormente pelo PHP para controlar a paginação.

Bom é isso.. espero que ajude um pocuo. Pra mim fez uma enorme diferença.

One Response to “SQL – Buscas e mais buscas”

1. 

   Victor Hugo Odo
   September 17, 2011 at 00:15 » Reply

   Muito interessante. Acho que poderei adaptar isso para uma solução em um outro projeto.

   Obrigado Pelo post.