mejorar consulta sql para buscar cadenas

[pithon]

Estoy intentando crear una consulta sql en mysql para buscar cadenas utilizo LIKE, pero tengo un problema primero pongo la sql:

Código MySQL:

SELECT nombre FROM registrados WHERE descripcion LIKE '%me gusta cine%' ;
 

bien el problema lo tengo que si en mi base de datos en el campo descripcion tengo guardado texto con estilos css algo asin:

descripcion:

me gusta <b>cine</b>

entonces ya no me muestra el registro este, necesito alguna ayuda para mejorar mi consulta y evitar problemas como este y poder hacer una busqueda mas efectiva.
saludos.

[pithon]

Bueno leyendo el manual conseguir dar con REGEXP que hace exactamente lo que quiero coincidencias, saludos.

[gnzsoloyo]

Te conviene no usar LIKE sino tablas MyISAM e índices FULLTEXT para ese tipo de cosas.
Por otro lado, es una pésima idea usar búsquedas con comdines en ambos lados, por cuanto eso genera full table scan, que es el peor escenario de consultas.
Además, para buscar palabras sueltas es mejor precisamente el FULLTEXT.
Por otro lado, es una muy mala práctica realizar búsquedas con palabras demasiado cortas (menos de cinco caracteres) porque generará muchísimos falsos positivos. Esa es la razón por la cual no se las toma en cuenta con el MATCH() AGAINST().

[pithon]

Hola gnzsolo yo te anticipastes a mi respuesta, queria decir que el REGEXP no me vale para lo que quiero, el problema esta en que la tabla en innoDB entonces el FULLTEXT no me sirve, si no hay otra forma mejor puedo general una tabla aparte con el campo descripcion para hacer las busquedas pero si sabes una solucion mejor me gustaria saberla.
saludos.

[gnzsoloyo]

Para tu problema, el mejor camino es el que te comenté. Usar LIKE no es una opción, y menos en tu contexto de datos.

[pithon]

Estoy leyendo sobre FULLTEXT y la verdad que es muy interesante, igual no me acaba de funcionar pase la tabla a MyISAM y cree en los campos donde quiero buscar los indices FULLTEXT, pero al hacer esta consulta por ejemplo no me funciona.

Código MySQL:

SELECT  nombre FROM registrados WHERE MATCH(perfil,descripcion) AGAINST('me gusta cine')
 

me dice que no se puedo encontrar el indice FULLTEXT para comparar las listas de columna, pero los dos indices se han creado bien en sus campos en cambio si quito un campo de busqueda poniendo solo descripcion me funciona, a que se debo este error?
Saludos.

[jurena]

Pithon,
no crees un índice full-text para cada campo, sino un índice full-text de dos campos, y en ese mismo orden.

[pithon]

Perfecto, gracias jurema ahora la consulta hace lo que quiero, mil gracias a los dos por vuestra colaboracion, la verdad que no conocia FULLTEXT pero es muy interesante.
Saludos.

[pithon]

Perdonar por estar aqui otra vez pero ejecuto esta consulta

Código MySQL:

SELECT *,  MATCH(perfil,descripcion) AGAINST('mee gustaa el cine') AS score FROM registrados WHERE MATCH(perfil,descripcion) AGAINST('mee gustaa el cine')
 

y si en la descripcion tengo esta parte de cadena:
me gusta <b>cine</b>

Me tendria que mostrar el registro? Porque no me lo muestra me dice que 0 consultas.

Saludos.

[pithon]

Rectifico si que funciona bien la consulta que hice, pero sigo teniendo otro problema para usar MATCH, intento usar COUNT pero no me funciona no se si no es posible hacerlo para saber el numero de registros que tiene dicha consulta.
alguna ayuda?
saludos

[pithon]

sigo teniendo dudas no con el COUNT ya que aplicar el COUNT es como una consulta normal, pero usando MATCH, es posible buscar coincidencias o como deberia hacer para conseguir ese resultado ya que MATCH no me lo hace, osea si en una cadena contiene esto:

productos caducados
y en la busqueda se pone producto la busqueda anterior posteada no me muestra resultados, como puede hacer que este tipo de coincidencias tambien me las muestre?
Saludos.

[jurena]

Para el COUNT, mira aquí.
http://dev.mysql.com/doc/refman/5.0/...-language.html

Para hacer búsquedas booleanas de texto truncado por el final, mira aquí y el uso del asterisco.
http://dev.mysql.com/doc/refman/5.0/...t-boolean.html

[pithon]

Hola jurena muchas gracias por tu colaboracion, referente a COUNT no tenia dudas las dudas las tenia con la busqueda, referente a las busquedas booleanas hace exactamente lo que quiero, pero no tiene en cuenta lo del 50% osea que si hay un 50% de coincidencias me las muestras igual, tampoco me lo ordena por orden de relevancia, lo del 50% me da igual pero lo de la relevancia es importante, tengo alguna otra opcion para conseguir ese resultado o ya tendria que programar?
Saludos.

[jurena]

Creo que el orden de relevancia no es fácil con las búsquedas booleanas, aunque algún ejemplo encontrarás en la página oficial (no lo he probado):
Posted by Sean Cannon on November 12 2008 7:24pm [Delete] [Edit]

Here's a query I use to return relevance-based data while still using boolean mode searches, and using weights for different columns, and even weights for specific results:

Código MySQL:

SELECT id,
store,
name,
(((MATCH(name)
AGAINST(?) * 1.2) +
MATCH(description, keywords)
AGAINST (?) +
((MATCH(creators)
AGAINST (?) * 1.2)) / 3) +
(((store IN ('xfx','w3d')) * .12) * ((store IN ('iv', '3da', 'vp')) * .1))) *
MATCH(description,keywords,requirements,creators,name)
AGAINST(? IN BOOLEAN MODE)
AS sort_rel
FROM prod_text
WHERE MATCH(description,keywords,requirements,creators,name)
AGAINST(? IN BOOLEAN MODE)
AND store != 'pp'
HAVING sort_rel > 0.2
ORDER BY sort_rel DESC
 

All placeholders (?s) take the search terms entered by the user in this case.

Sé que existen otras posibilidades como indexar con Lucene o con sphinx, pero no sé si ahí encontrarás lo que quieres, aunque por lo que he leído sí hay orden por relevancia:
http://jayant7k.blogspot.com.es/2006...ql-lucene.html

Pero esto se me escapa a mí.

[pithon]

Hola jurema gracias por tu interes en ayudarme, la consulta que posteaste no la entiendo si alguien me la puede explicar o pasarme algun enlace, se agradeceria la verdad.
saludos.

[jurena]

pithon,
no puedo darte toda la ayuda que tal vez requieres. Te diré lo que yo veo. Por lo que dice en el texto donde aparece ? debes escribir tu consulta. Si observas lo que hay en AS sort_rel te darás cuenta de que son consultas sobre distintas columnas, cuyo resultado (0 o 1, false o true) es multiplicado por ese ese valor asignado. La suma del producto de esas multiplicaciones es el valor de relevancia por el que se ordena. Sería un valor de relevancia algo casero y a medida, pero no sé si te interesa para tu caso.
Eso no tiene nada que ver con la relevancia y sus fórmulas que en tiempos encontré en una web, pero que ahora el enlace se perdió:

Hay tres formulas

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.