fulltext - mysql match

¿Cómo puedo manipular la relevancia de búsqueda de texto completo de MySQL para hacer que un campo sea más "valioso" que otro?

Question

Supongamos que tengo dos columnas, palabras clave y contenido. Tengo un índice de texto completo en ambos. Quiero una fila con foo en las palabras clave para tener más relevancia que una fila con foo en el contenido. ¿Qué debo hacer para que MySQL pondere las coincidencias en palabras clave más altas que las del contenido?

Estoy usando la sintaxis "combinar contra".

SOLUCIÓN:

Pude hacer que esto funcionara de la siguiente manera:

SELECT *,
CASE when Keywords like ''%watermelon%'' then 1 else 0 END as keywordmatch,
CASE when Content like ''%watermelon%'' then 1 else 0 END as contentmatch,
MATCH (Title, Keywords, Content) AGAINST (''watermelon'') AS relevance
FROM about_data
WHERE MATCH(Title, Keywords, Content) AGAINST (''watermelon'' IN BOOLEAN MODE)
HAVING relevance > 0
ORDER by keywordmatch desc, contentmatch desc, relevance desc

Answer 1

Bueno, eso depende de a qué se refiere exactamente con:

Quiero una fila con foo en las palabras clave para tener más relevancia que una fila con foo en el contenido.

Si quiere decir que una fila con foo en las palabras clave debe aparecer antes de cualquier fila con foo en el contenido, entonces haré dos consultas separadas, una para las palabras clave y luego (posiblemente de forma perezosa, solo si se solicita) la otra en el contenido.

Answer 2

Crea tres índices de texto completo

• a) uno en la columna de palabras clave
• b) uno en la columna de contenido
• c) uno en la columna de palabra clave y contenido

Entonces, tu consulta:

SELECT id, keyword, content,
MATCH (keyword) AGAINST (''watermelon'') AS rel1,
MATCH (content) AGAINST (''watermelon'') AS rel2
FROM table
WHERE MATCH (keyword,content) AGAINST (''watermelon'')
ORDER BY (rel1*1.5)+(rel2)

El punto es que rel1 le da la relevancia de su consulta solo en la columna de keyword (porque creó el índice solo en esa columna). rel2 hace lo mismo, pero para la columna de content . Ahora puede agregar estos dos puntajes de relevancia aplicando la ponderación que desee.

Sin embargo, no está utilizando ninguno de estos dos índices para la búsqueda real. Para eso, usa su tercer índice, que está en ambas columnas.

El índice de (palabra clave, contenido) controla su recuperación. Aka, lo que se devuelve.

Los dos índices separados (uno solo en palabras clave, uno solo en contenido) controlan su relevancia. Y puede aplicar sus propios criterios de ponderación aquí.

Tenga en cuenta que puede usar cualquier cantidad de índices diferentes (o varíe los índices y las ponderaciones que utiliza en el tiempo de consulta en función de otros factores, tal vez ... solo busque en palabras clave si la consulta contiene una palabra de suspensión ... disminuya el sesgo de ponderación para palabras clave si la consulta contiene más de 3 palabras ... etc.).

Cada índice consume espacio en el disco, por lo que hay más índices y más disco. Y a su vez, una mayor huella de memoria para mysql. Además, las inserciones tardarán más, ya que tiene más índices para actualizar.

Debe comparar el rendimiento (teniendo cuidado de desactivar el caché de consultas de MySQL para la evaluación comparativa, de lo contrario, los resultados serán sesgados) para su situación. Esto no es eficiente en Google, pero es bastante fácil y "listo para usar" y es casi seguro mucho mejor que el uso de "me gusta" en las consultas.

Encuentro que funciona realmente bien.

Answer 3

En el modo booleano, MySQL admite el operador ">" y "<" para cambiar la contribución de una palabra al valor de relevancia que se asigna a una fila.

Me pregunto si algo así podría funcionar.

SELECT *,
MATCH (Keywords) AGAINST (''>watermelon'' IN BOOLEAN MODE) AS relStrong,
MATCH (Title,Keywords,Content) AGAINST (''<watermelon'' IN BOOLEAN MODE) AS relWeak
FROM about_data
WHERE MATCH(Title, Keywords, Content) AGAINST (''watermelon'' IN BOOLEAN MODE)
ORDER by (relStrong+relWeak) desc

Answer 4

En realidad, usar una declaración de caso para hacer un par de banderas podría ser una mejor solución:

select
...
, case when keyword like ''%'' + @input + ''%'' then 1 else 0 end as keywordmatch
, case when content like ''%'' + @input + ''%'' then 1 else 0 end as contentmatch
-- or whatever check you use for the matching
from
...
and here the rest of your usual matching query
...
order by keywordmatch desc, contentmatch desc

Nuevamente, esto es solo si todas las coincidencias de palabras clave tienen un rango más alto que todas las coincidencias de solo contenido. También asumí que una coincidencia en palabras clave y contenido es el rango más alto.

Answer 5

Hasta donde yo sé, esto no es compatible con la búsqueda de texto completo de MySQL, pero puede lograr el efecto de alguna manera repitiendo esa palabra varias veces en el campo de palabra clave. En lugar de tener palabras clave "foo bar", tenga "foo bar foo bar foo bar", de esa manera tanto foo como la barra son igualmente importantes dentro de la columna de palabras clave, y dado que aparecen varias veces, se vuelven más relevantes para mysql.

Usamos esto en nuestro sitio y funciona.

Answer 6

Hice esto hace unos años, pero sin el índice de texto completo. No tengo el código a mano (antiguo empleador), pero recuerdo bien la técnica.

En pocas palabras, seleccioné un "peso" de cada columna. Por ejemplo:

select table.id, keyword_relevance + content_relevance as relevance from table
left join
(select id, 1 as keyword_relevance from table_name where keyword match) a
on table.id = a.id
left join
(select id, 0.75 as content_relevance from table_name where content match) b
on table.id = b.id

Por favor, perdonen cualquier SQL de mala calidad aquí, han pasado algunos años desde que necesité escribir alguno, y estoy haciendo esto de lo más desaforado ...

¡Espero que esto ayude!

J.Js

Answer 7

Necesitaba algo similar y usé la solución de OP, pero noté que el texto completo no coincide con palabras parciales. Por lo tanto, si ''sandía'' está en palabras clave o contenido como parte de una palabra (como sandíasalesmanager) no coincide y no se incluye en los resultados debido al DONDE PARTIDO. Así que me engañé un poco y modifiqué la consulta del OP a esto:

SELECT *,
CASE WHEN Keywords LIKE ''%watermelon%'' THEN 1 ELSE 0 END AS keywordmatch,
CASE WHEN Content LIKE ''%watermelon%'' THEN 1 ELSE 0 END AS contentmatch,
MATCH (Title, Keywords, Content) AGAINST (''watermelon'') AS relevance
FROM about_data
WHERE (Keywords LIKE ''%watermelon%'' OR
Title LIKE ''%watermelon%'' OR
MATCH(Title, Keywords, Content) AGAINST (''watermelon'' IN BOOLEAN MODE))
HAVING (keywordmatch > 0 OR contentmatch > 0 OR relevance > 0)
ORDER BY keywordmatch DESC, contentmatch DESC, relevance DESC

Espero que esto ayude.

Answer 8

Si la métrica es solo que todas las coincidencias de palabras clave son más "valiosas" que todas las coincidencias de contenido, entonces puede usar una unión con recuentos de filas. Algo a lo largo de estas líneas.

select *
from (
select row_number() over(order by blahblah) as row, t.*
from thetable t
where keyword match
union
select row_number() over(order by blahblah) + @@rowcount + 1 as row, t.*
from thetable t
where content match
)
order by row

Para algo más complicado que eso, donde quiera aplicar un peso real a cada fila, no sé cómo ayudar.

Answer 9

Versión más simple usando solo 2 índices de texto completo (créditos tomados de @mintywalker):

SELECT id,
MATCH (`content_ft`) AGAINST (''keyword*'' IN BOOLEAN MODE) AS relevance1,
MATCH (`title_ft`) AGAINST (''keyword*'' IN BOOLEAN MODE) AS relevance2
FROM search_table
HAVING (relevance1 + relevance2) > 0
ORDER BY (relevance1 * 1.5) + (relevance2) DESC
LIMIT 0, 1000;

Esto buscará ambas columnas indexadas completas contra la keyword y seleccionará la relevancia emparejada en dos columnas separadas. Excluiremos los artículos que no coincidan (relevancia1 y relevancia2 son ambos cero) y reordenaremos los resultados por un mayor peso de la columna content_ft . No necesitamos índice compuesto de texto completo.