PHP-hispano.net

Amigos,
Esto es algo que me corroe hace algún tiempo, pero recientemente me he introducido en el mundo de los índices FULLTEXT y no he logrado resolver el problema.
Los índices FULLTEXT me funcionan bien y me devuelven los datos como espero si son índices de una misma tabla.
Sin son índices con campos de dos tablas distintas, he resuelto con un SELECT...MATCH...AGAINST ... UNION ALL SELECT ... MATCH...AGAINST, pero no he conseguido hacer que funcionen las consultas que incluyen índices de campos de tablas relacionadas, es decir, que no he logrado traer los datos con una consulta del tipo INNER JOIN ON entre tablas y que luego incluya MATCH... AGAINST...con índices fulltext de una o de las dos tablas.
Tal vez alguno de vosotros pueda echarme una mano...

Hola jurena,

He estado haciendo pruebas sencillas con indices FULLTEXT y no he tenido problemas al hacer INNER JOIN's, lo único que me ha dado problemas es que al tener pocos registros el MATCH con palabras comunes a varios registros me daba 0. Puede que te esté pasando lo mismo, para comprobar esto pon el MATCH en el SELECT en lugar del WHERE:

SELECT MATCH(indice) AGAINST('texto') FROM tu_tabla INNER JOIN tu_otra WHERE...

De esta forma podrás saber si no obtienes resultados pq el MATCH devuelve 0.


Un saludo

http://krowork.blogspot.com

estimado krowork,
en realidad, el problema era otro (aunque te diré que sufrí hasta darme cuenta de lo del 50% y al menos 3 referencias, sobre todo cuando uno anda en pruebas). Yo trataba de unir en un MATCH dos índices fulltext procedentes de distintas tablas, en lugar de usar la solución lógica, un OR y otro match. Me explico.
Tengo tres tablas de prueba: cliente, compra, compracliente. En la tabla cliente hago un índice fulltext al campo apellido, y en la tabla compra se lo hago al campo producto, Yo quería buscar todos los registros que tuvieran la palabra libro sea en el campo producto de compra o en el campo apellido de cliente, y hacía esta barbaridad:
SELECT idcliente, cliente.apellido, compra.producto
FROM cliente
INNER JOIN compracliente ON compracliente.idcliente = cliente.id
INNER JOIN compra ON compra.id = compracliente.idcompra
WHERE MATCH (apellido,
producto
)
AGAINST (
'libro'
)

Naturalmente, no encontraba nada y me daba repetidamente error, pues esa unión apellido, producto en el MATCH no es posible, al pertenecer ambos a distintas tablas. Tuve que recurrir, iluminado por la rotundidad con que decías que era posible, a esto, y funcionó.

SELECT idcliente, cliente.apellido, compra.producto
FROM cliente
INNER JOIN compracliente ON compracliente.idcliente = cliente.id
INNER JOIN compra ON compra.id = compracliente.idcompra
WHERE MATCH (
producto
)
AGAINST (
'libro'
)
OR MATCH (
apellido
)
AGAINST (
'libro'
)
LIMIT 0 , 30

Gracias, amigo krowork.

un dia teneis que explicar que es eso de los indices fulltext XD es la primera vez que lo oigo en serio :S igual se lo que son pero no sabia que se llamaban asi.

XGallox,
Te diré lo poco que yo sé, pues me he iniciado recientemente. En MySQL estos índices sólo pueden utilizarse en las tablas MyISAM y en campos CHAR, VARCHAR, TEXT, y no en las tablas InnoDB, algo que para algunos puede ser un problema y que, por otra parte, es una de las principales ventajas de las tablas MyISAM frente a las tablas InnoDB, que sin embargo frente a las tablas MyIsam permiten transacciones y controlan la integridad referencial.
Bien, los índices fulltext se crean en los campos en los que quieras hacer las consultas sobre palabras completas o grupos de palabras completas y, como puedes ver por mi último post y la respuesta de krowork, puede hacerse con campos de tablas distintas. El índice fulltext ocupa bastante espacio y no está recomendado para tablas cuyos datos cambien con mucha frecuencia. Todavía no he comprobado qué tal funcionan con UTF8. El índice fulltext actúa sobre todo el campo y la consulta sobre él te permite buscar si escribes la cadena de búsqueda "Me duele mucho la cabeza" y encontrará Me duele mucho la cabeza, duele mucho, duele cabeza, cabeza, etc. una o varias palabras, pues el espacio que separa las palabras en la cadena de tu consulta de consulta es como si fuera OR, aunque los especialistas recomiendan cuando se trate de la búsqueda de una sola palabra lanzar una búsqueda normal con LIKE como hacemos hasta ahora, si bien otros concretan que sólo cuando se trate de la parte de una palabra. Piensa que el índice fulltext tiene ciertas limitaciones: no encuentra palabras de menos de tres caracteres o de tres, excluye palabras frecuentes, etc., aunque eso se puede cambiar (no lo recomiendan porque aumenta mucho el espacio reservado). Lo mejor es programar con PHP para que si se trata de una palabra haga una búsqueda con LIKE normal, y se trata de más de una palabra que lance una consulta usando el índice fulltext. Pero dirás que qué ventaja tiene un índice de esos frente a la búsqueda con índices normales con OR LIKE...: pues muchas. La primera es la relevancia: una fórmula matemática fácil hace que primero ordene "me duele mucho la cabeza", luego "duele cabeza", luego "cabeza", etc. Además, si usas la búsqueda IN BOOLEAN MODE puedes pedirle que te busque texto exacto poniéndolo entre comillas "me duele la cabeza", que te busque "duele" pero que no aparezca "cabeza" con una sintaxis como esta +duele -cabeza, etc. Son los famosos operadores de IN BOOLEAN MODE, entre los que se incluye el asterisco *. Hay muchas más posibilidades más allá de la búsqueda natural, la primera de la que te hablé. Seguro que lo conocías por otro nombre, pero si no es así, échale un vistazo, pues es una buena herramienta de consulta y se usa mucho en la búsqueda productos de las empresas en varios campos: un buscador parecido al de google, salvando las distancias, y al que, según creo, tenemos en nuestro PHP-HISPANO para buscar temas y expresiones en el buscador general o por foros.
La sintaxis básica es muy sencilla, en general, y se reduce casi a MATCH(campos de la misma tabla con índice fulltext separados por coma) AGAINST ('cadenabuscada')
pero como te he dicho las posibilidades son muchas.
Los principales problemas cuando se hacen pruebas es que tienes que tener más de tres registros y que la cadena buscada aparezca en menos del 50%; si no no te encontrará nada. xGallox, sé que eres un técnico y tal vez te preguntes por la fórmula matemática que usa la consulta. Si te interesa te la buscaré, pues la he visto por ahí y no es muy difícil.
Aquí la tienes,la encontré:
Las formulas
Hay tres formulas, y no son para nada complicadas

peso_local = (log(dtf)+1)/sumdtf * U/(1+0.0115*U)
peso_global = log((N-nf)/nf)
peso_consulta = peso_local * peso_global * qf
Parámetros
dtf el número de veces que el término aparece en la fila.
sumdtf el sumatorio de (log(dtf)+1) para todos los términos de la misma fila.
U el número de términos únicos que hay en la fila.
N el número de filas que hay en la tabla.
nf el número de filas que contienen el término.
qf el número de veces que el término aparece en la consulta.
Por último log(n) hace referencia al logaritmo neperiano de n.

Tomemos por ejemplo la búsqueda de la palabra special sobre la primera fila de la tabla, que myisam_ftdump identifica como fila 0 (cero).

Para la primera formula: (log(dtf)+1)/sumdtf * U/(1+0.0115*U);

dft special aparece dos veces en la fila 0, así que log(dtf()+1) = 0.6931472 + 1 = 1.6931472
sumdft special aparece 2 veces en la fila 0, añadimos log(2)+1
times aparece 1 vez en la fila 0, añadimos log(1)+1
require aparece 1 vez en la fila 0, añadimos log(1)+1
socks aparece 1 vez en la fila 0, añadimos log(1)+1
el cálculo queda sumdtf = log(2)+1 + (log(1)+1)*3 = 4.6931472
U Hay 4 términos únicos en la fila 0, por lo que U/(1+0.115*U) = 4/(1+0.0115*4) = 3.824092

peso_local = 1.6931472 / 4.6931472 * 3.824092 = 1.3796179. El mismo número que sale en el volcado del indice proporcionado por myisam_ftdump.

Para la segunda formula: log((N-nf)/nf);

N Hay 4 filas en la tabla quotes
nf El término special tiene ocurrencias en 1 fila.

peso_global = log((N-nf)/nf) = log(3) = 1.0986123. El mismo número que devuelve myisam_ftdump en el volcado de pesos por palabra.

Para la tercera fórmula: peso_consulta = peso_local * peso_global * qf;

peso_local 1.3796179
peso_global 1.0986123
qf special aparece 1 vez en la consulta.

peso_consulta = 1.3796179 * 1.0986123 * 1 = 1.5156652. Finalmente esta es la relevancia de la consulta sobre la tabla de ejemplo.

Está sacado de esta dirección que puede ser útil a alguien:
http://quarkblog.org/2007/01/14/mysql-full-text-para-humanos/

MUCHO MÁS EN EL MANUAL DE MYSQL, que le dedica varios apartados...

Espero haber animado a algunos usarlos si no lo conocíais.