A nova versão do Sphinx 0.9.9-rc2, possui suporte à MySQL denominado SphinxQL e utiliza o SQL como uma linguagem de consulta a índices do Sphinx. O projeto que encontra-se em sua fase inicial possibilita uma excelente pré-visualização e muitas brincadeiras interessantes.

Para a realização deste teste não se faz interessante o desempenho integral de texto dentro da pesquisa, já que o Sphinx demonstra um bom desempenho em pesquisas de texto completo.

[root@r27 sp]# mysql --host 127.0.0.1 --port 3307
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 0.9.9-id64-rc2 (r1785)
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

Para os testes foi utilizado o motor de busca existente no fórum, restando alguns ids e removendo todo o resto:

CREATE TABLE `sptest` (
  `id` bigint(20) unsigned NOT NULL,
  `site_id` int(10) unsigned NOT NULL,
  `forum_id` int(10) unsigned NOT NULL,
  `author_id` int(10) unsigned NOT NULL,
  `num_links` smallint(5) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Esta tabela continha cerca de 25 milhões de linhas e não existem índices definidos, Sphinx não possui suporte à índices explícitos é claro que quando for possível a utilização índices em MySQL isto será bastante rápido. O Sphinx é um sistema inteligente que realiza triagem, pois não tenta solucionar tudo mas sim exibe o número de linhas que são necessárias para que se atinja o limite.

Sphinx

mysql> select forum_id as f from sptest order by author_id desc limit 10;
+------------+--------+----------+
| id         | weight | forum_id |
+------------+--------+----------+
| 6739362135 |      1 |  2736983 |
| 6739362391 |      1 |  2736983 |
| 6739338327 |      1 |  1024599 |
| 6739357527 |      1 |  1023063 |
| 6739359063 |      1 |  1024599 |
| 6739305559 |      1 |  2558807 |
| 6739336791 |      1 |  2558807 |
| 6739300695 |      1 |   208215 |
| 6739297111 |      1 |  2736471 |
| 6739296855 |      1 |  2736471 |
+------------+--------+----------+
10 rows in set (7.92 sec)

MySQL

mysql> select forum_id as f from sptest order by author_id desc limit 10;
+---------+
| f       |
+---------+
| 2736983 |
| 2736983 |
| 1024599 |
| 1023063 |
| 1024599 |
| 2558807 |
| 2558807 |
|  208215 |
| 2736471 |
| 2736471 |
+---------+
10 rows in set (17.91 sec)

Como pode ser visto, o Sphinx acresce um par de colunas extras para o conjunto de resultados, mesmo que isso não seja pedido pelo usuário. Uma outra coisa interessante é o GROUP BY, o Sphinx realiza a execução na memória fixa o que significa que os resultados podem ser bastante aproximados, esta técnica e voltada para aplicações de pesquisa de texto completo quando a exatidão dos números não é importante.

Sphinx

mysql> select max(forum_id) as m,author_id as a from sptest group  by author_id  order by m desc limit 10;
+------------+--------+----------+-----------+---------+
| id         | weight | forum_id | author_id | m       |
+------------+--------+----------+-----------+---------+
| 6739362135 |      1 |  2736983 | 139452247 | 2736983 |
| 6738995287 |      1 |  1762135 | 134125655 | 2736727 |
| 6739296855 |      1 |  2736471 | 139450967 | 2736471 |
| 6739297111 |      1 |  2736471 | 139451223 | 2736471 |
| 6739227479 |      1 |  2736215 | 139449687 | 2736215 |
| 6739227735 |      1 |  2736215 | 139449943 | 2736215 |
| 6739226967 |      1 |  2735959 | 139449175 | 2735959 |
| 6739227223 |      1 |  2735959 | 139449431 | 2735959 |
| 6739223383 |      1 |  2735703 | 139448663 | 2735703 |
| 6739223639 |      1 |  2735703 | 139448919 | 2735703 |
+------------+--------+----------+-----------+---------+
10 rows in set (32.47 sec)

MySQL

mysql> select max(forum_id) as m,author_id as a from sptest group  by author_id  order by m desc limit 10;
+---------+-----------+
| m       | a         |
+---------+-----------+
| 2736983 | 139452247 |
| 2736727 | 134125655 |
| 2736471 | 139450967 |
| 2736471 | 139451223 |
| 2736215 | 139449687 |
| 2736215 | 139449943 |
| 2735959 | 139449175 |
| 2735959 | 139449431 |
| 2735703 | 139448663 |
| 2735703 | 139448919 |
+---------+-----------+
10 rows in set (1 min 15.03 sec)

Uma otimização que se faz interessante realizar é no " bloco de rejeição secundário" que deve permitir o rápido descarte de grandes blocos de atributos caso eles não possuam nem um dado:

Sphinx

select max(author_id) as a ,forum_id  as f from sptest where num_links=1;
Empty set (2.70 sec)

MySQL

mysql> select max(author_id) as a ,forum_id  as f from sptest where num_links=1;
+------+---+
| a    | f |
+------+---+
| NULL | NULL |
+------+---+
1 row in set (4.29 sec)

Era de se esperar levar um tempo muito maior no teste desta otimização, que aparentemente está quebrada na versão testada. Além disto, podemos notar a diferença no conjunto de resultados pois o Sphinx não encontra nenhum registro e não cria grupos, em contra partida o MySQL gera relatórios de grupos exibindo NULL como resultado.

O SphinxQL é bastante exigente pois requer AS em todas as suas expressões, ele também deixa de analisar algumas consultas, esperamos que em um futuro próximo isto venha a ser melhorado. A boa coisa é que os mecanismos de consulta e execução são bastante estáveis, o que significa que vão estar estabilizados em breve.

O Sphinx oferece aos seus usuário um número de extensões para SQL, que se mostram bastante uteis em casos de busca, dentro da ordem GROUP BY permitindo a seleção de itens e a escolha dos mesmo destro de determinados grupos ( como se gostaria mostrar os documentos mais recentes ou mais relevantes) e outros. Você pode utilizar a API nativa que é mais completa em termos de recursos, mas a linha de comando torna-se bastante útil para testes e depurações, o Sphinx pode ser acessado a partir de linguagens que não possuem suporte para sua API, mas todos interpretam MySQL.

Agora sobre o desempenho , para determinada classe de consultas o Sphinx era apenas 1,5-2 vezes mais rápido. Sinceramente esperávamos mais, embora de forma cuidadosa foram colhidas consultas que são razoavelmente positivas para ambos é fácil de "quebrar" o MySQL para criar grupo, com disco temporário o Sphinx é muito mais rápido e alguns outros pontos.

O ganho real do Sphinx está na sua capacidade de escala quase de forma linear que trabalha com múltiplos núcleos da CPU e diversos nós dentro do sistemas.A velocidade de digitalização-prima foi de quase 10 milhões de linhas por segundo, isso significa que você deve ser capaz de realizar uma varredura através de linhas + 100M no servidor de núcleo único, que é um excelente quantitativo.

Traduzido de: http://www.mysqlperformanceblog.com