¿Cómo puedo optimizar la función ORDER BY RAND() de MySQL?

Me gustaría optimizar mis consultas, así que investigo mysql-slow.log.

La mayoría de mis consultas lentas contienen archivos ORDER BY RAND(). No puedo encontrar una solución real para resolver este problema. Hay una posible solución en MySQLPerformanceBlog pero no creo que sea suficiente. En tablas mal optimizadas (o actualizadas con frecuencia, administradas por el usuario), no funciona o necesito ejecutar dos o más consultas antes de poder seleccionar mi PHPfila aleatoria generada.

¿Existe alguna solución para este problema?

Un ejemplo ficticio:

SELECT  accomodation.ac_id,
        accomodation.ac_status,
        accomodation.ac_name,
        accomodation.ac_status,
        accomodation.ac_images
FROM    accomodation, accomodation_category
WHERE   accomodation.ac_status != 'draft'
        AND accomodation.ac_category = accomodation_category.acat_id
        AND accomodation_category.acat_slug != 'vendeglatohely'
        AND ac_images != 'b:0;'
ORDER BY
        RAND()
LIMIT 1

Aug 07 '09 19:08 fabrik

Prueba esto:

SELECT  *
FROM    (
        SELECT  @cnt := COUNT(*) + 1,
                @lim := 10
        FROM    t_random
        ) vars
STRAIGHT_JOIN
        (
        SELECT  r.*,
                @lim := @lim - 1
        FROM    t_random r
        WHERE   (@cnt := @cnt - 1)
                AND RAND(20090301) < @lim / @cnt
        ) i

Esto es especialmente eficiente MyISAM(ya que COUNT(*)es instantáneo), pero incluso en InnoDBocasiones es 10más eficiente que ORDER BY RAND().

La idea principal aquí es que no ordenamos, sino que mantenemos dos variables y calculamos el valor running probabilityde una fila que se seleccionará en el paso actual.

Consulte este artículo en mi blog para obtener más detalles:

Seleccionar filas aleatorias

Actualizar:

Si necesita seleccionar un solo registro aleatorio, intente esto:

SELECT  aco.*
FROM    (
        SELECT  minid + FLOOR((maxid - minid) * RAND()) AS randid
        FROM    (
                SELECT  MAX(ac_id) AS maxid, MIN(ac_id) AS minid
                FROM    accomodation
                ) q
        ) q2
JOIN    accomodation aco
ON      aco.ac_id =
        COALESCE
        (
        (
        SELECT  accomodation.ac_id
        FROM    accomodation
        WHERE   ac_id > randid
                AND ac_status != 'draft'
                AND ac_images != 'b:0;'
                AND NOT EXISTS
                (
                SELECT  NULL
                FROM    accomodation_category
                WHERE   acat_id = ac_category
                        AND acat_slug = 'vendeglatohely'
                )
        ORDER BY
                ac_id
        LIMIT   1
        ),
        (
        SELECT  accomodation.ac_id
        FROM    accomodation
        WHERE   ac_status != 'draft'
                AND ac_images != 'b:0;'
                AND NOT EXISTS
                (
                SELECT  NULL
                FROM    accomodation_category
                WHERE   acat_id = ac_category
                        AND acat_slug = 'vendeglatohely'
                )
        ORDER BY
                ac_id
        LIMIT   1
        )
        )

Esto supone que los suyos ac_idse distribuyen de manera más o menos uniforme.

Aug 07 '2009 13:08 Quassnoi

Depende de qué tan aleatorio necesites ser. La solución que vinculó funciona bastante bien en mi opinión. A menos que tenga grandes espacios en el campo ID, sigue siendo bastante aleatorio.

Sin embargo, debería poder hacerlo en una consulta usando esto (para seleccionar un valor único):

SELECT [fields] FROM [table] WHERE id >= FLOOR(RAND()*MAX(id)) LIMIT 1

Otras soluciones:

Agregue un campo flotante permanente llamado randoma la tabla y rellénelo con números aleatorios. Luego puedes generar un número aleatorio en PHP y hacer"SELECT ... WHERE rnd > $random"
Tome la lista completa de ID y guárdelas en caché en un archivo de texto. Lea el archivo y elija una identificación aleatoria.
Guarde en caché los resultados de la consulta como HTML y guárdelos durante unas horas.

Aug 08 '2009 23:08 DisgruntledGoat

Así es como lo haría:

SET @r := (SELECT ROUND(RAND() * (SELECT COUNT(*)
  FROM    accomodation a
  JOIN    accomodation_category c
    ON (a.ac_category = c.acat_id)
  WHERE   a.ac_status != 'draft'
        AND c.acat_slug != 'vendeglatohely'
        AND a.ac_images != 'b:0;';

SET @sql := CONCAT('
  SELECT  a.ac_id,
        a.ac_status,
        a.ac_name,
        a.ac_status,
        a.ac_images
  FROM    accomodation a
  JOIN    accomodation_category c
    ON (a.ac_category = c.acat_id)
  WHERE   a.ac_status != ''draft''
        AND c.acat_slug != ''vendeglatohely''
        AND a.ac_images != ''b:0;''
  LIMIT ', @r, ', 1');

PREPARE stmt1 FROM @sql;

EXECUTE stmt1;

Aug 07 '2009 21:08 Bill Karwin

(Sí, me criticarán por no tener suficiente carne aquí, pero ¿no puedes ser vegano por un día?)

Caso: AUTO_INCREMENT consecutivo sin espacios, 1 fila devuelta
Caso: AUTO_INCREMENT consecutivo sin espacios, 10 filas
Caso: AUTO_INCREMENT con espacios, 1 fila devuelta
Caso: Columna FLOAT adicional para aleatorización
Caso: UUID o columna MD5

Esos 5 casos pueden resultar muy eficientes para mesas grandes. Vea mi blog para más detalles.

Feb 01 '2016 01:02 Rick James