Reducir tiempo al buscar coincidencias en grandes consultas

  • Autor Autor jucuegas
  • Fecha de inicio Fecha de inicio
J

jucuegas

Alfa
¡Usuario con pocos negocios! ¡Utiliza siempre saldo de Forobeta!
Hola,
tengo un problema de rendimiento al buscar coincidencias.
Me explico.
Tengo unos proyectos y unos usuarios que pertenecen a los diferentes proyectos.
Los proyectos y usuarios comparten unas 30 categorías.

Entonces cuando creo un nuevo proyecto quiero buscar gente compatible con ese proyecto y busco de entre todos los usuarios las categorías compatibles y lo ordeno de mayor a menor número de categorías compatibles.

El problema viene que si ya tengo cerca de 2500 usuarios y buscar esas 30 categorías una a una a cada usuario pues son muuuchas consultas, cuando no había tantos usuarios no era un problema de tiempo de ejecución.

De momento lo he hago de la siguiente forma
Listo las categorías del proyecto y voy buscando coincidencias en cada uno de los usuarios y lo guardo en otra tabla.
Claro si tengo 20 categorías para buscar en 2500 usuarios son 50000 búsquedas, y si lo acaba haciendo puede tardar 15 o 30 minutos, cuando directamente no se queda colgado.

¿Habría una forma mas eficiente de hacerlo, sin usar tanto procesador?

Muchas gracias
 
Hola, pero es hablando de sql o de funciones de php, si es sql pon la consulta y se intenta optimizar.
Para el tipo de consulta que realizas finalmente necesitarías tabla usuarios, tabla categorias tabla(solo descripctiva con id), relusucat
relusucat campos: id, iduser, categoria1,categoria2,categoria3,....categoria30(tyniint 0 o 1)
select iduser from relusucat where (categoria1=1 and categoria1=13)
Si no lo tienes montado así más o menos entonces optimizarlo será más complicado
 
debes crear una tabla que funcione como un indice las categorias con los usuarios
PHP: 
CREATE TABLE IF NOT EXISTS `taxonomy_index` (
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `categoria_id` int(10) unsigned NOT NULL DEFAULT '0',
  KEY `user_id` (`user_id`),
  KEY `cat_id` (`categoria_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Volcado de datos para la tabla `taxonomy_index`
--

INSERT INTO taxonomy_index` (`user_id`, `categoria_id`) VALUES
(1, 1),
(1, 2),
(2, 1),
(2, 3);
para seleccionar los usuarios que tienen una determinada categoria
PHP: 
SELECT user_id FROM taxonomy_index WHERE categoria_id='$categoria'
 
Es desde php, esta noche pongo la consulta que hago
Saludos

- - - Actualizado - - -

Resumo lo que hago, a ver si se entiende.
1.- extraigo las categorias del proyecto
2.- con un for las recorro
3.- busco los usuarios que cumplen con cada categoria
4.- actualizo la tabla paralela de usuario que he creado previamente con el contador de categorias

PHP: 
// CATEGORIAS  *********************************
$categoria = $row_proyecto['categoriasproyecto']; // listado de categorias en un campo, del tipo: id1,id2,id14,id23
$categoriaoferta = explode(",",$categoria);        // separo las categorias
for ($j=0;$j<count($categoriaoferta);$j++)         // bucle para recorrer cada categorias sobre los usuarios
{
    mysql_select_db($database_basedatos, $basedatos);
    // busco todos los usuarios que cumplen con la categoria a buscar
    $query_usuarios = "SELECT * FROM usuarios WHERE categoriasusuario LIKE '%$categoriaoferta[$j]%'";
    $usuarios = mysql_query($query_usuarios, $basedatos) or die(mysql_error());
    $row_usuarios = mysql_fetch_assoc($usuarios);

    do {
        // previamente he metido a todos los usuarios en la tabla tabla_usuarios_categorias
        // y ahora actualizo los usuario que cumplen tienen esa categoria, sumo +1 al contador numerocategorias e introduzco el id de la categoria en idcategorias.
        $updateSQL = "UPDATE tabla_usuarios_categorias SET numerocategorias=numerocategorias+1, idcategorias=CONCAT(idcategorias, '$categoriaoferta[$j],') WHERE idusuario=$usuarios[idusurio]";
        mysql_select_db($database_basedatos, $basedatos);
        $Result1 = mysql_query($updateSQL, $basedatos) or die(mysql_error());
        } while ($row_usuarios = mysql_fetch_assoc($usuarios));
        
}    // fin de for ($j=0;$j<count($categoriaoferta);$j++)
 
jucuegas dijo:
Es desde php, esta noche pongo la consulta que hago
Saludos

- - - Actualizado - - -

Resumo lo que hago, a ver si se entiende.
1.- extraigo las categorias del proyecto
2.- con un for las recorro
3.- busco los usuarios que cumplen con cada categoria
4.- actualizo la tabla paralela de usuario que he creado previamente con el contador de categorias

PHP: 
// CATEGORIAS  *********************************
$categoria = $row_proyecto['categoriasproyecto']; // listado de categorias en un campo, del tipo: id1,id2,id14,id23
$categoriaoferta = explode(",",$categoria);        // separo las categorias
for ($j=0;$j<count($categoriaoferta);$j++)         // bucle para recorrer cada categorias sobre los usuarios
{
    mysql_select_db($database_basedatos, $basedatos);
    // busco todos los usuarios que cumplen con la categoria a buscar
    $query_usuarios = "SELECT * FROM usuarios WHERE categoriasusuario LIKE '%$categoriaoferta[$j]%'";
    $usuarios = mysql_query($query_usuarios, $basedatos) or die(mysql_error());
    $row_usuarios = mysql_fetch_assoc($usuarios);

    do {
        // previamente he metido a todos los usuarios en la tabla tabla_usuarios_categorias
        // y ahora actualizo los usuario que cumplen tienen esa categoria, sumo +1 al contador numerocategorias e introduzco el id de la categoria en idcategorias.
        $updateSQL = "UPDATE tabla_usuarios_categorias SET numerocategorias=numerocategorias+1, idcategorias=CONCAT(idcategorias, '$categoriaoferta[$j],') WHERE idusuario=$usuarios[idusurio]";
        mysql_select_db($database_basedatos, $basedatos);
        $Result1 = mysql_query($updateSQL, $basedatos) or die(mysql_error());
        } while ($row_usuarios = mysql_fetch_assoc($usuarios));
        
}    // fin de for ($j=0;$j<count($categoriaoferta);$j++)
Hacer clic para expandir...

Si buscas hacer una sensencia SQL rapida y optimizada, te tienes que olvidar de utilizar "LIKE", es lo peor en cuanto al rendimiento.
Como te dije anteriormente tienes que crear una tabla que asocie el usuario y la categoria. Esta solucion es la mas optima y es la que utilizan todos las CMS (wordpres, drupal...)
 
Hola de nuevo
Como te ha dicho comors o esto :
PHP: 
SELECT user_id FROM taxonomy_index WHERE categoria_id='$categoria'  
SELECT user_id FROM taxonomy_index WHERE categoria_id in ('$categoria' ,'$categoria0' ,'$categoria1' )
group by  user_id
O con la tabla más pesada(más columnas) para casos en que el número de categorías no es muy alto pero los usuarios suelen corresponder a más del 50% de las categorías:
PHP: 
select iduser from relusucat where (categoria1=1 and categoria1=13)
Para este caso tendrías que montar la tabla con columna por categoría pero esto es sólo para casos específicos que te he comentado anteriormente, en cualquier otro debes seguir el método de comors
 
Última edición:
Yo empezaría por cambiar un par de cosas, sin ver el código entero tampoco puedo opinar mucho más:

Cambiaría el for por el foreach, es un poquitín más rápido, y si haces el "count($categoriaoferta)" dentro de él, como en tu caso, es un poco más. La diferencia hablamos de milisegundos como siempre...
Cambiaría el like de la consulta por un igual, pero claro, eso ya es depende de como tengas diseñado todo.

Lo que podrías hacer es implementar cacheado. Si implementas caché sólo vas a consultas una vez cada X tiempo una consulta con todos los usuarios que tendrás disponible en seguida. Esto como siempre tiene sus pros y sus contras.

Saludos.

PHP: 
// CATEGORIAS  *********************************
$categoria = $row_proyecto['categoriasproyecto']; // listado de categorias en un campo, del tipo: id1,id2,id14,id23
$categoriaoferta = explode(",",$categoria);        // separo las categorias
foreach ($categoriaoferta as $catOfertada)         // bucle para recorrer cada categorias sobre los usuarios
{
    mysql_select_db($database_basedatos, $basedatos);
    // busco todos los usuarios que cumplen con la categoria a buscar
    $query_usuarios = "SELECT * FROM usuarios WHERE categoriasusuario = '$catOfertada'";
    $usuarios = mysql_query($query_usuarios, $basedatos) or die(mysql_error());
    $row_usuarios = mysql_fetch_assoc($usuarios);

    do {
        // previamente he metido a todos los usuarios en la tabla tabla_usuarios_categorias
        // y ahora actualizo los usuario que cumplen tienen esa categoria, sumo +1 al contador numerocategorias e introduzco el id de la categoria en idcategorias.
        $updateSQL = "UPDATE tabla_usuarios_categorias SET numerocategorias=numerocategorias+1, idcategorias=CONCAT(idcategorias, '$catOfertada,') WHERE idusuario=$usuarios[idusurio]";
        mysql_select_db($database_basedatos, $basedatos);
        $Result1 = mysql_query($updateSQL, $basedatos) or die(mysql_error());
        } while ($row_usuarios = mysql_fetch_assoc($usuarios));
        
}    // fin de foreach

jucuegas dijo:
Es desde php, esta noche pongo la consulta que hago
Saludos

- - - Actualizado - - -

Resumo lo que hago, a ver si se entiende.
1.- extraigo las categorias del proyecto
2.- con un for las recorro
3.- busco los usuarios que cumplen con cada categoria
4.- actualizo la tabla paralela de usuario que he creado previamente con el contador de categorias

PHP: 
// CATEGORIAS  *********************************
$categoria = $row_proyecto['categoriasproyecto']; // listado de categorias en un campo, del tipo: id1,id2,id14,id23
$categoriaoferta = explode(",",$categoria);        // separo las categorias
for ($j=0;$j<count($categoriaoferta);$j++)         // bucle para recorrer cada categorias sobre los usuarios
{
    mysql_select_db($database_basedatos, $basedatos);
    // busco todos los usuarios que cumplen con la categoria a buscar
    $query_usuarios = "SELECT * FROM usuarios WHERE categoriasusuario LIKE '%$categoriaoferta[$j]%'";
    $usuarios = mysql_query($query_usuarios, $basedatos) or die(mysql_error());
    $row_usuarios = mysql_fetch_assoc($usuarios);

    do {
        // previamente he metido a todos los usuarios en la tabla tabla_usuarios_categorias
        // y ahora actualizo los usuario que cumplen tienen esa categoria, sumo +1 al contador numerocategorias e introduzco el id de la categoria en idcategorias.
        $updateSQL = "UPDATE tabla_usuarios_categorias SET numerocategorias=numerocategorias+1, idcategorias=CONCAT(idcategorias, '$categoriaoferta[$j],') WHERE idusuario=$usuarios[idusurio]";
        mysql_select_db($database_basedatos, $basedatos);
        $Result1 = mysql_query($updateSQL, $basedatos) or die(mysql_error());
        } while ($row_usuarios = mysql_fetch_assoc($usuarios));
        
}    // fin de for ($j=0;$j<count($categoriaoferta);$j++)
Hacer clic para expandir...
 
Última edición:
Hola,
muchas gracias por vuestros comentarios.
Por el momento el LIKE es necesario ya que las categorias dentro de la tabla usuarios las guardo todas en un mismo campo separado por comas
EJ. (categoria1, categoria5, categoria14, categoria26)
por eso para buscar la categoria correspondiente uso el LIKE.
Otra opción como habéis comentado es cambiar el sistema y guardar esa relación en otra tabla en la que esté el id_usuario y el id_categoria unicamente
 
Hay que estar conectado o registrado para responder aquí.
Atrás
Arriba