Seleccione valores que cumplan diferentes condiciones en diferentes filas

Ok, me votaron en contra de esto, así que decidí probarlo:

CREATE TABLE userrole (
  userid INT,
  roleid INT,
  PRIMARY KEY (userid, roleid)
);

CREATE INDEX ON userrole (roleid);

Ejecuta esto:

<?php
ini_set('max_execution_time', 120); // takes over a minute to insert 500k+ records

$start = microtime(true);

echo "<pre>\n";
mysql_connect('localhost', 'scratch', 'scratch');
if (mysql_error()) {
    echo "Connect error: " . mysql_error() . "\n";
}
mysql_select_db('scratch');
if (mysql_error()) {
    echo "Selct DB error: " . mysql_error() . "\n";
}

$users = 200000;
$count = 0;
for ($i=1; $i<=$users; $i++) {
    $roles = rand(1, 4);
    $available = range(1, 5);
    for ($j=0; $j<$roles; $j++) {
        $extract = array_splice($available, rand(0, sizeof($available)-1), 1);
        $id = $extract[0];
        query("INSERT INTO userrole (userid, roleid) VALUES ($i, $id)");
        $count++;
    }
}

$stop = microtime(true);
$duration = $stop - $start;
$insert = $duration / $count;

echo "$count users added.\n";
echo "Program ran for $duration seconds.\n";
echo "Insert time $insert seconds.\n";
echo "</pre>\n";

function query($str) {
    mysql_query($str);
    if (mysql_error()) {
        echo "$str: " . mysql_error() . "\n";
    }
}
?>

Producción:

499872 users added.
Program ran for 56.5513510704 seconds.
Insert time 0.000113131663847 seconds.

Eso suma 500.000 combinaciones aleatorias de roles de usuario y hay aproximadamente 25.000 que coinciden con los criterios elegidos.

Primera consulta:

SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP by userid
HAVING COUNT(1) = 3

Tiempo de consulta: 0.312s

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3
AND t1.roleid = 1

Tiempo de consulta: 0.016s

Así es. La versión de unión que propuse es veinte veces más rápida que la versión agregada.

Lo siento, pero hago esto para ganarme la vida y trabajar en el mundo real y en el mundo real probamos SQL y los resultados hablan por sí solos.

La razón de esto debería ser bastante clara. La consulta agregada aumentará su costo con el tamaño de la tabla. Cada fila se procesa, agrega y filtra (o no) a través de la HAVINGcláusula. La versión de unión seleccionará (usando un índice) un subconjunto de usuarios según un rol determinado, luego comparará ese subconjunto con el segundo rol y finalmente ese subconjunto con el tercer rol. Cada selección (en términos de álgebra relacional ) funciona en un subconjunto cada vez más pequeño. De esto se puede concluir:

El rendimiento de la versión para unirse mejora aún más con una menor incidencia de coincidencias.

Si solo hubiera 500 usuarios (de la muestra de 500.000 anterior) que tuvieran los tres roles indicados, la versión para unirse será significativamente más rápida. La versión agregada no lo hará (y cualquier mejora en el rendimiento es el resultado de transportar 500 usuarios en lugar de 25k, que obviamente también obtiene la versión unida).

También tenía curiosidad por ver cómo una base de datos real (es decir, Oracle) abordaría esto. Básicamente, repetí el mismo ejercicio en Oracle XE (ejecutándose en la misma máquina de escritorio con Windows XP que MySQL del ejemplo anterior) y los resultados son casi idénticos.

Las uniones parecen estar mal vistas, pero como he demostrado, las consultas agregadas pueden ser un orden de magnitud más lentas.

Actualización: Después de algunas pruebas exhaustivas, el panorama es más complicado y la respuesta dependerá de sus datos, su base de datos y otros factores. La moraleja de la historia es prueba, prueba, prueba.

Jan 25 '2009 01:01 cletus