Ordenar por número de coincidencias

[qwerty_wq]

Buenas. Tengo el siguiente problema, a ver si podéis ayudarme. Disponemos de dos tablas, una que contiene nombres de productos con un ID numérico asociado, y otra que contiene keywords (o palabras clave) asociadas a cada producto. Por ejemplo:

TABLA 'productos'

Código:

ID    NOMBRE
1     Apple Macbook Pro
2     Apple Macbook
3     Apple iPod
4     Dell XPS M1330

TABLA 'keywords'

Código:

KEYWORD    ID_PRODUCTO
Apple      1
Apple      2
Apple      3
Macbook    1
Macbook    2
Pro        1
iPod       3
Dell       4
XPS        4
M1330      4

Bien, creo que la idea es bastante sencilla e intuitiva. Ahora supongamos que un usuario quiere buscar "Apple Macbook Pro". Lo que quiero hacer es mostrar los resultados de los productos por coincidencia con el NÚMERO (el orden da igual) de keywords, de mayor a menor. Y así, como resultado de la anterior búsqueda los productos serían, por orden:

1 Apple Macbook Pro
2 Apple Macbook
3 Apple iPod

Ya que los tres productos tienen la palabra "Apple" el segundo tiene además la palabra "Macbook" y el primero las tiene todas.

Si buscase "Apple iPod" el resultado sería:

3 Apple iPod
2 Apple Macbook
1 Apple Macbook Pro

Aquí el segundo y tercer puestos pueden intercambiarse, porque ambos sólo tienen una coincidencia ["Apple"] con la búsqueda. Creo que se entiende bien.

Mi pregunta es, ¿cómo puedo hacer esto con una consulta MySQL? Me vale cualquier lenguaje de bases de datos (y por eso lo pongo en este foro), ya que sé un poco de todos y lo podría adaptar fácilmente a MySQL.

He probado a hacer un LEFT JOIN por cada palabra clave de búsqueda y luego ordenándolos por número de keywords en los resultados, pero esto no me sirve porque se da prioridad a los resultados de la primera tabla, y si una persona introduce la búsqueda "Ordenador Apple Macbook" no se devuelve ningún resultado, ya que "Ordenador" es una palabra "intrusa". Y por supuesto, también se dan problemas cuando las palabras no son intruducidas en el orden determinado.

¿Alguien sabe cómo hacer lo que quiero?

Gracias.

PD: lo que he puesto es sólo un ejemplo, pero imaginarios una tabla con decenas de miles de productos, cada uno más de media docena de palabras clave. El tiempo de búsqueda puede ser inviable.

[seyko]

PD: lo que he puesto es sólo un ejemplo, pero imaginarios una tabla con decenas de miles de productos, cada uno más de media docena de palabras clave. El tiempo de búsqueda puede ser inviable.

Exacto, el problema es cuando tengas muchos registros.
creo que Mysql tiene algo como fulltext, investiga sobre el tema que puede que te sirva.

A parte, a mi parecer, el diseño no es bueno. Porque no utilizas 3 tablas??
- productos
- keywords
- productos_x_keywords

[seyko]

Hice unas pruebas:


drop table producto;
create table producto (id serial primary key, nombre varchar);
insert into producto (nombre) values ('Apple Macbook Pro');
insert into producto (nombre) values ('Apple Macbook');
insert into producto (nombre) values ('Apple iPod');
insert into producto (nombre) values ('Dell XPS M1330');
drop table key;
create table key (id integer references producto(id), key varchar);
insert into key(key, id) values ('Apple', 1);
insert into key(key, id) values ('Apple', 2);
insert into key(key, id) values ('Apple', 3);
insert into key(key, id) values ('Macbook', 1);
insert into key(key, id) values ('Macbook', 2);
insert into key(key, id) values ('Pro', 1);
insert into key(key, id) values ('iPod', 3);
insert into key(key, id) values ('Dell', 4);
insert into key(key, id) values ('XPS', 4);
insert into key(key, id) values ('M1330', 4);

select nombre, count(distinct key)
from producto
join key using(id)
where key IN ('Apple', 'Macbook', 'Pro')
group by nombre
order by count(distinct key) desc

select nombre, count(distinct key)
from producto
join key using(id)
where key IN ('Apple', 'iPod')
group by nombre
order by count(distinct key) desc


Con estas select te da los resultados que esperas!

Espero que te sirva pero dudo que sea eficiente cuando las tablas crezcan.

Meter indices en las FK ayudará un poco.

Saludos

[qwerty_wq]

Bueno, muchas gracias por las dos respuestas. En realidad, tal y como decías, no son dos tablas, sino tres: una para los productos (ID_PRODUCTO-NOMBRE) otra para las keywords (ID_KEYWORD-KEYWORD) y otra para relacionar ambas (ID_PRODUCTO-ID_KEYWORD), todas con índices en los campos "críticos" (ID) para acelerar consultas. Lo que pasa es que lo simplifiqué para no liar mucho el asunto, ya que la idea de resolución es básicamente la misma.

He probado tu sugerencia y la verdad es que se parece a algo que ya tenía hecho con un "OR" para cada keyword en la cláusula WHERE (OR keyword='Apple' OR keyword='Macbook'...), y el rendimiento es muy similar, mucho más lento de lo que me gustaría... Pero sí, funciona como tiene que ser.

Si a alguien se le ocurre otra manera de plantearlo para lograr más eficiencia, le estaría agradecido en el alma...

[seyko]

Bueno mira el tema de fulltext que igual te ayuda.

En realidad "x IN (v1, v2, v3)" no es más que una abreviación de x = v1 OR x = v2 OR x = v3.

Un saludo

[qwerty_wq]

Ya tengo una idea para implementar la aplicación usando parte de MySQL y parte de PHP. Si tengo éxito, lo postearé aquí por si puede ayudar a alguien.