Revi esse problema muitas vezes e nunca encontrei uma resposta adequada.

É possível executar uma pesquisa no MySQL que retorne resultados ACTUAL classificados com precisão por relevância?

Estou tentando criar um formulário de pesquisa ajax que faz sugestões à medida que o usuário digita em um campo de entrada e não encontrou uma solução decente para isso usando apenas consultas MySQL puras. Sei que existem servidores de pesquisa disponíveis, como o ElasticSearch, quero saber como fazê-lo apenas com uma consulta bruta do MySQL.

Eu tenho uma tabela de disciplinas escolares. Existem menos de 1200 linhas e isso nunca será alterado. Vamos realizar uma pesquisa básica do FULLTEXT em que o usuário começa a digitar "Biografia".

Consulta ("Bio ...") - MODO BOOLEANO COMPLETO

SELECT name, MATCH(name) AGAINST('bio*' IN BOOLEAN MODE) AS relevance
FROM subjects
WHERE MATCH(name) AGAINST('bio*' IN BOOLEAN MODE)
ORDER BY relevance DESC
LIMIT 10

Resultados

name                                        |  relevance
--------------------------------------------------------
Biomechanics, Biomaterials and Prosthetics  |  1
Applied Biology                             |  1
Behavioural Biology                         |  1
Cell Biology                                |  1
Applied Cell Biology                        |  1
Developmental/Reproductive Biology          |  1
Developmental Biology                       |  1
Reproductive Biology                        |  1
Environmental Biology                       |  1
Marine/Freshwater Biology                   |  1

Para mostrar quão ruins são esses resultados, aqui está uma comparação com um simplesLIKE consulta que mostra todos os resultados mais relevantes que não foram mostrados:

Consulta ("Biografia ...") - COMO

SELECT id, name
WHERE name LIKE 'bio%'
ORDER BY name

Resultados

name                                        |  relevance
--------------------------------------------------------
Bio-organic Chemistry                       |  1
Biochemical Engineering                     |  1
Biodiversity                                |  1
Bioengineering                              |  1
Biogeography                                |  1
Biological Chemistry                        |  1
Biological Sciences                         |  1
Biology                                     |  1
Biomechanics, Biomaterials and Prosthetics  |  1
Biometry                                    |  1

E você já vê quantos assuntos não são sugeridos, mesmo sendo mais prováveis o que o usuário procura.

O problema com o usoLIKE no entanto, é como pesquisar várias palavras e no meio de palavras comoFULLTEXT faz.

A ordem básica que eu gostaria de implementar é algo como:

Primeiras palavras que começam com o termo da pesquisaSegundas palavras começando com o termo de pesquisaPalavras em que o termo não está no início das palavrasTudo geralmente em ordem alfabética, se não mais relevante

Então, minha pergunta é: como obter uma lista de sugestões classificadas de maneira sensata para o usuário com uma pesquisa no MySQL em várias palavras?