Mostrar registros aleatorios con PHP MySQL

En este tutorial con magnetoxp <- yo XDD , vamos a ver como obtener registros de forma aleatoria
de una base de datos, lo cual tiene diferentes usos: mostrar una imagen
de forma aleatoria, mostrar publicidad que sea diferente para cada
página cargada, los enlaces "artículo aleatorio" de la Wikipedia, y
muchos más.

El problema

Hay muchos motivos por los que se podría querer obtener registros de forma aleatoria de una base de datos: mostrar una imagen de forma aleatoria, mostrar publicidad que sea diferente para cada página cargada, los enlaces "artículo aleatorio" de la Wikipedia, y muchos más.

El problema es que no existe un método eficiente dentro del motor de base de datos, por lo que con el tiempo se han ido utilizando diversos métodos. Algunos de estos métodos son ampliamente usados, otros son simplemente ideas resultado del ingenio de algún programador al que la solución "ampliamente usada" no le satisfizo.

En un intento por estudiar el tema, recopilé algunos métodos que me parecieron interesantes.
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.

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 la condición, se ejecuta una vez la función RAND(). Y 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 "intenta" detener 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 tiene otra opción más que procesar todas las filas de la tabla y ordenar luego, para finalmente devolver el número de filas pedidas.

Es la única forma de asegurarse de devolver los X primeros registros.

El texto indica también que MySQL ordena solamente hasta encontrar los primeros X resultados. 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 siempre, en todo caso, es mejor no usar métodos que se saben ineficientes.

Este método funciona bien cuando se necesita solamente 1 registro, o no
importa si se obtienen X registros consecutivos partiendo de un
registro aleatorio. Hace uso de COUNT(), una función que devuelve el
número de filas afectadas por la consulta.

SELECT COUNT(*) as cantidad_de_filas FROM tabla 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

$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, y cuando X es
mayor, 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 con PHP los números aleatorios


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

SELECT MAX(id) ....

Forma 2 (campos auto increment):

SHOW TABLE STATUS LIKE 'nombre_de_la_tabla'

Esta segunda forma solo funciona con MySQL (aunque otros motores
tienen métodos equivalentes). Devuelve devuelve información
"administrativa" de la tabla, uno de los campos devueltos es el próximo
auto-increment que se otorgará al próximo registro que se ingrese.
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 este método no necesita leer datos
de la tabla, por lo que es más rápido.
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:

$aleatorios = array();
while( sizeof($aleatorios) < X ) {
$nuevo = rand(0, $max);
if (!in_array($nuevo, $aleatorios))
$aleatorios[] = $nuevo;
}

$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 id 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


Es decepcionante ver cómo hay infinidad de manuales en internet que enseñan a hacer esto mismo usando una consulta como

SELECT * from nombre_tabla ORDER BY RAND() LIMIT 1

Que es probablemente la peor forma en la que se puede hacer.
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. Además de que requiere más trabajo con PHP o con el lenguaje
que sea. Sin embargo, evita hacer búsquedas y ordenamientos
innecesarios en MySQL.
Nótese que el método 3 funciona bien siempre y cuando todas las
filas de la tabla puedan ser seleccionadas. Incluir una cláusula WHERE
sería como fragmentar la tabla, disminuyendo la posibilidad de éxito.
Podríamos intentar obtener primero los ID de todas las tablas que
coincidan con la condición y luego elegir X al azar, pero esto podría
tener aún peor rendimiento que el método 1.
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.