26 dic 2009

Registros aleatorios con PHP y MySQL

El problema

Obtener un registro al azar de una tabla es útil en diversas ocasiones, por ejemplo para mostrar imágenes o anuncios diferentes en cada carga de una página. Sin embargo no existe una forma eficiente de lograr esto en los motores de bases de datos, al menos no en MySQL.

A continuación se describen varios métodos que encontré buscando en Internet sobre este tema.

Método 1: ORDER BY RAND()

Esta es la forma ampliamente usada, se podría decir que es una solución normal para el problema. Sin embargo, en la mayoría de los casos, es la forma más lenta y menos eficiente de hacerlo. En la página del manual de MySQL sobre esta función, puede leerse:

Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is executed.

Eso quiere decir que para cada registro que cumple con las cláusulas WHERE, se ejecuta una vez la función RAND(). Luego, cuando se tienen todos los registros, MySQL los ordena según el valor que RAND devolvió para cada uno. Cuando se usa junto a LIMIT, entra en juego lo que se dice en la página de optimización de LIMIT.

If you use LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause must be selected, and most or all of them must be sorted, before it can be ascertained that the first row_count rows have been found. In either case, after the initial rows have been found, there is no need to sort any remainder of the result set, and MySQL does not do so.

Lo que esto quiere decir es que MySQL detendrá el proceso de selección y orden de las filas en cuanto encuentra la cantidad de filas pedidas, sobretodo si la consulta indica buscar y ordenar por un campo índice. Pero si necesita hacer un orden extra (como cuando se usa RAND()) no podrá hacer esta optimización: deberá procesar todas las filas de la tabla y ordenar luego, para finalmente devolver el número de filas pedidas.

El texto indica también que MySQL ordena solamente hasta encontrar los primeros row_count resultados (es decir, el número de resultados pedidos en la consulta). Si bien esto es una mejora de rendimiento, en general se cumple lo que la página dice, most or all of them must be sorted: deben ordenarse todos o la mayoría de los registros.

En tablas grandes puede ser muy lento. Por otro lado, en tablas pequeñas puede no notarse la diferencia. Pero en cualquier caso es útil saber que no es el método más eficiente.

Método 2: Usando COUNT y 2 consultas

Este método funciona bien cuando se necesita solamente un registro, o se necesitan n registros pero no importa si se obtienen consecutivos a partir de uno al azar.

Hace uso de COUNT(), una función que devuelve el número de filas afectadas por la consulta.

1 SELECT COUNT(*) as cantidad_de_filas
2 FROM tabla
3 WHERE .....

Nota: Con el asterisco, COUNT() devuelve el número de filas afectadas. También puede usarse COUNT(nombre_de_columna), que devuelve el número filas afectadas en las que el valor almacenado en nombre_de_columna no es nulo.

El valor de cantidad_de_filas se guarda en una variable, por ejemplo $cantidad.

Luego, se genera un número aleatorio entre 0 y $cantidad (ejemplo en PHP)

<?php
$aleatorio = rand(0,$cantidad-1);

(se usa cantidad -1 porque el número de resultados se numera de 0 a cantidad -1, en vez de de 1 a cantidad)

Finalmente:

SELECT ..... LIMIT $aleatorio, X

Cuando X es 1, se devuelve un registro aleatorio. Cuando es mayor que 1, se devuelven X registros consecutivos, comenzando desde $aleatorio. No es lo mismo que X registros aleatorios, pero sigue siendo más rápido que el método anterior en tablas grandes.

Cuidado con los casos de borde: si la cantidad total de registros es $resultados, $aleatorio debe ser menor que $resultados-X o el índice será rebasado y solo se devolverán ($resultados - $aleatorio) filas. Por ejemplo, si el máximo ID ($resultados) es 50 y la consulta queda

LIMIT 45,10

Los resultados devueltos serán solamente 5. Hay que asegurarse de que el programa sabe manejar esta situación.

Método 3: Generando los números aleatorios con PHP

Este método requiere conocer el mayor ID de la tabla, esto se puede hacer de 3 formas (al menos):

Forma 1:
```
SELECT MAX(id) ....
```

Forma 2 (campos auto increment):

SHOW TABLE STATUS LIKE 'nombre_de_la_tabla'

Forma 3:

1 SELECT AUTO_INCREMENT
2 FROM `information_schema`.`tables`
3 WHERE
4 TABLE_SCHEMA = 'nombreBBDD' AND TABLE_NAME = 'nombreTabla'

Las formas 2 y 3 solamente funcionan con MySQL (aunque otros motores tienen métodos equivalentes). Devuelven información administrativa de la tabla, en el caso de la segunda forma se devolverá un registro en el que uno de los campos es el valor de auto-increment que se otorgará al próximo registro que se ingrese, en la tercera forma se solicita este valor explícitamente. Basta reducirlo en 1 para obtener el mayor ID actual. El problema es que puede ser que este mayor id no exista por haber sido borrado, pero por contrapartida estos métodos no necesitan leer datos de la tabla, por lo que son más rápidos.

Sea cual sea la forma, se guarda el resultado en $max.

Suponiendo que se necesiten X registros, hay que generar X números aleatorios entre 1 y $max, para luego juntarlos en una lista separada por comas:

 1 <?php
 2 $aleatorios = array();
 3 while( sizeof($aleatorios) < X )
 4 {
 5     $nuevo = rand(0, $max);
 6     if (!in_array($nuevo, $aleatorios))
 7         $aleatorios[] = $nuevo;
 8 }
 9 
10 $lista = implode(',', $aleatorios);

Y finalmente hacer la consulta:

SELECT .... WHERE id IN ($lista)

El problema es que puede ser que algunos ID no existan, por haber sido borrados. Lo que implica que puede suceder que se devuelvan menos de X registros. La solución a este problema (parcial, ya que aún puede caerse en un valor faltante) es generar más de X números (5*X, por ejemplo), dependiendo de qué tan fragmentada esté la tabla. La probabilidad dice, sin embargo, que a menos que todos los ID estén en su lugar, o se generen $max números aleatorios, siempre existe la posibilidad de que se devuelvan menos de X registros.

No estoy seguro de que sea un método realmente más rápido, aunque no es necesario un orden sí es necesario comparar el ID con cada uno de los X (o X*N) valores (a menos que se lo encuentre antes, claro). Y sigue siendo necesario comprobar en cada paso de la generación de la lista, que el valor no esté en la matriz de valores.

Conclusión

Casi siempre que se pregunta cómo obtener un registro de forma aleatoria, la respuesta es algo similar al método 1. Que si bien es simple y directo para el programador, puede hacer que la base de datos tenga que trabajar mucho más de lo debido.

En mi opinión, y sin haber hecho pruebas (basicamente por no tener una tabla real lo suficientemente grande), nada mejor que el 2ª método para obtener un registro aleatorio. El 1ª sirve para tablas pequeñas, mientras que el 3ª método es un poco menos seguro, ya que no siempre devolverá la cantidad de registros pedida si la tabla está fragmentada (tiene valores borrados) o si se utiliza una cláusula WHERE (que para el caso resulta lo mismo que tener la tabla fragmentada). Requiere algo más de trabajo con el lenguaje de programación que se utilice, aunque por otro lado evita hacer búsquedas y ordenamientos innecesarios en MySQL.

Tengo entendido que en otros SGBD como Oracle, existe un índice numérico y secuencial de todas las filas generado por el gestor. Si existe esa posibilidad, el método 3 se vuelve ideal y pierde su desventaja principal. No he podido comprobar si esto mismo existe en MySQL, y luego de bastantes búsquedas asumo que no.

Pero más allá de estos truquitos, lo importante es intentar optimizar las consultas y aprender sobre cómo trabaja el SGBD que se esté usando. Es imprescindible para una aplicación eficiente generar los índices que necesite, para evitar que el gestor tenga que buscar en la tabla completa para encontrar lo que la consulta pide. El manual de MySQL tiene una sección dedicada a esto que vale la pena leer.

Enlaces relacionados

MySQL
MySQL: Función RAND()
MySQL: Optimización de LIMIT
MySQL: Optimización de consultas
PHP: Función rand()
Wikipedia: Sistema de Gestión de Bases de Datos
Una versión anterior de este mismo artículo, alojada en Taller Webmaster

Respuestas

cleptomano, usuario de ForoCreativo, contestó lo siguiente en tallerwebmaster:

Hola, como estan…

He leido el tutorial y me parece relamente interesante que hayan tantas formas de obtener un registro aleatorio

De los tres métodos propuestos, obviamente he descartado la tercera forma, quedandome solo la primera y la segunda.

Método 1: “ORDER BY RAND()” Esta es la forma ampliamente usada, no solo en MySQL pero en muchos otros sistemas también. Se podría decir que es una solución “normal” para el problema. Sin embargo, es probablemente la forma más lenta y menos eficiente de hacerlo.

Pues no… no es la más lenta ni la menos eficiente y voy a probarlo

 1 <?php
 2 function getUser(){
 3     $result = array();
 4 
 5     // Consulta SQL
 6     $sql = "
 7     SELECT
 8         c.id_contacto,
 9         c.email
10     FROM ts_contactos AS c
11     ORDER BY RAND()
12     LIMIT 1
13     ";
14 
15     $resp = mysql_query($sql);
16 
17     if(mysql_num_rows($resp) > 0){
18         $datos = mysql_fetch_assoc($resp);
19         $result = $datos;
20     }
21 
22     return $result;
23 }
24 
25 for($i=0; $i<100; $i++){
26     $user = getUser();
27     echo "<p>Registro <strong>".($i+1)."</strong></p>";
28     print_r($user);
29     flush();
30 }

Registros devueltos en 30 segundos: 9

 1 <?php
 2 function getUser(){
 3     $result = array();
 4 
 5     // Consulta SQL
 6     $sql = "
 7     SELECT
 8         COUNT(*) AS total
 9     FROM ts_contactos AS c
10     ";
11 
12     $resp = mysql_query($sql);
13     $total = mysql_result($resp,0);
14 
15     // Consulta SQL
16     $sql = "
17     SELECT
18         c.id_contacto,
19         c.email
20     FROM ts_contactos AS c
21     LIMIT ".mt_rand(0,$total-1).",1
22     ";
23 
24     $resp = mysql_query($sql);
25 
26     if(mysql_num_rows($resp) > 0){
27         $datos = mysql_fetch_assoc($resp);
28         $result = $datos;
29     }
30 
31     return $result;
32 }
33 
34 for($i=0; $i<100; $i++){
35     $user = getUser();
36     echo "<p>Registro <strong>".($i+1)."</strong></p>";
37     print_r($user);
38     flush();
39 }

Registros devueltos en 30 segundos: 4

Las 2 pruebas la hize sobre una tabla con 780mil registros, ahora… ¿cúal es mejor?

Ante esto, mi respuesta fue:

Hola cleptomano, Soy el autor del “tutorial”, me gusta mucho ver un comentario fundamentado y que me obliga a investigar más del tema.

Creo que la frase de “peor y menos eficiente” no debería estar ahí, es una de las cosas que voy a quitar cuando edite el artículo (estoy pensando en rediseñar mi sitio y reescribir algunas cosas). Estas cosas siempre dependen de la tabla y de los datos que tenga.

Me gustaría ver, en realidad, una prueba más: ¿qué pasa si mueves el COUNT() fuera del bucle? Debería cambiar las cosas

Otra cosa que he encontrado por ahí, investigando a raíz de tu comentario, es que las tablas InnoDB no guardan (o al menos, no guardaban) un número de filas, como sí lo hace MyISAM. Por lo que el rendimiento en consultas que incluyan COUNT sin WHERE siempre requiere un ‘full table scan’, lo cual lo hace más lento. ¿Será que tu tabla es InnoDB? http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/ Este mismo artículo sugiere (en el último comentario) que se puede forzar a MySQL a utilizar un índice.

Saludos y gracias de nuevo :-)

Activa Javascript para para cargar los comentarios, basados en DISQUS