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

random performance

Question

Me gustaría optimizar mis consultas para ver en mysql-slow.log .

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

¿Hay 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

Answer 1

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

Caso: AUTO_INCREMENTO consecutivo sin huecos, 1 fila devuelta
Caso: AUTO_INCREMENTO consecutivo sin huecos, 10 filas
Caso: AUTO_INCREMENT con espacios, 1 fila devuelta
Caso: columna Extra FLOAT para aleatorizar
Caso: columna UUID o MD5

Esos 5 casos pueden ser muy eficientes para tablas grandes. Ver mi blog para más detalles.

Answer 2

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;

Answer 3

Depende de lo aleatorio que necesite ser. La solución que vinculó funciona bastante bien IMO. A menos que tenga grandes lagunas en el campo ID, todavía es bastante aleatorio.

Sin embargo, debe poder hacerlo en una consulta usando esto (para seleccionar un solo valor):

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

Otras soluciones

• Agregue un campo flotante permanente llamado random a la tabla y llénelo con números aleatorios. A continuación, puede generar un número aleatorio en PHP y hacer "SELECT ... WHERE rnd > $random"
• Tome la lista completa de ID y guárdelos en un archivo de texto. Lee el archivo y elige una identificación aleatoria.
• Guarde en caché los resultados de la consulta como HTML y guárdelo durante unas horas.

Answer 4

Esto le dará una única sub consulta que usará el índice para obtener un ID aleatorio, luego la otra consulta se activará para obtener su tabla unida.

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;''
AND accomodation.ac_id IS IN (
        SELECT accomodation.ac_id FROM accomodation ORDER BY RAND() LIMIT 1
)

Answer 5

Estoy optimizando muchas consultas existentes en mi proyecto. ¡La solución de Quassnoi me ayudó a acelerar las consultas! Sin embargo, me resulta difícil incorporar dicha solución en todas las consultas, especialmente para consultas complicadas que involucran muchas subconsultas en múltiples tablas grandes.

Entonces estoy usando una solución menos optimizada. Fundamentalmente funciona de la misma manera que la solución de Quassnoi.

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;''
        AND rand() <= $size * $factor / [accomodation_table_row_count]
LIMIT $size

$size * $factor / [accomodation_table_row_count] calcula la probabilidad de elegir una fila aleatoria. El rand () generará un número aleatorio. La fila se seleccionará si rand () es más pequeño o igual a la probabilidad. Esto efectivamente realiza una selección aleatoria para limitar el tamaño de la tabla. Dado que existe la posibilidad de que regrese menos que el recuento límite definido, necesitamos aumentar la probabilidad para asegurar que estamos seleccionando suficientes filas. Por lo tanto, multiplicamos $ size por un $ factor (generalmente fijo $ factor = 2, funciona en la mayoría de los casos). Finalmente hacemos el limit $size

El problema ahora es resolver el accomodation_table_row_count . Si conocemos el tamaño de la tabla, PODRÍAMOS codificar el tamaño de la tabla. Esto funcionaría más rápido, pero obviamente esto no es ideal. Si usa Myisam, obtener el recuento de tablas es muy eficiente. Como estoy usando innodb, solo estoy haciendo una simple selección de count +. En tu caso, se vería así:

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;''
        AND rand() <= $size * $factor / (select (SELECT count(*) FROM `accomodation`) * (SELECT count(*) FROM `accomodation_category`))
LIMIT $size

La parte difícil es calcular la probabilidad correcta. Como puede ver, el siguiente código solo calcula el tamaño aproximado de la tabla temporal (De hecho, ¡demasiado áspero!): (select (SELECT count(*) FROM accomodation) * (SELECT count(*) FROM accomodation_category)) Pero puede refinar esta lógica para dar una aproximación de tamaño de tabla más cercana. Tenga en cuenta que es mejor seleccionar OVER que seleccionar las filas por debajo. es decir, si la probabilidad es demasiado baja, corre el riesgo de no seleccionar suficientes filas.

Esta solución es más lenta que la solución de Quassnoi, ya que necesitamos volver a calcular el tamaño de la tabla. Sin embargo, creo que esta codificación es mucho más manejable. Esto es una compensación entre precisión + rendimiento versus complejidad de codificación . Habiendo dicho eso, en tablas grandes, esto es mucho más rápido que Order by Rand ().

Nota: Si la lógica de consulta lo permite, realice la selección aleatoria tan pronto como sea posible antes de cualquier operación de unión.

Answer 6

La solución para tu ejemplo ficticio sería:

SELECT  accomodation.ac_id,
        accomodation.ac_status,
        accomodation.ac_name,
        accomodation.ac_status,
        accomodation.ac_images
FROM    accomodation,
        JOIN 
            accomodation_category 
            ON accomodation.ac_category = accomodation_category.acat_id
        JOIN 
            ( 
               SELECT CEIL(RAND()*(SELECT MAX(ac_id) FROM accomodation)) AS ac_id
            ) AS Choices 
            USING (ac_id)
WHERE   accomodation.ac_id >= Choices.ac_id 
        AND accomodation.ac_status != ''draft''
        AND accomodation_category.acat_slug != ''vendeglatohely''
        AND ac_images != ''b:0;''
LIMIT 1

Para leer más acerca de las alternativas a ORDER BY RAND() , debe leer este artículo .

Answer 7

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 en MyISAM (ya que el COUNT(*) es instantáneo), pero incluso en InnoDB es 10 veces más eficiente que ORDER BY RAND() .

La idea principal aquí es que no ordenamos, sino que mantenemos dos variables y calculamos la running probability de running probability de una fila para seleccionar en el paso actual.

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

• Seleccionar filas aleatorias

Actualizar:

Si necesita seleccionar un único 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 ac_id se distribuyen de manera más o menos uniforme.

Answer 8

function getRandomRow(){
    $id = rand(0,NUM_OF_ROWS_OR_CLOSE_TO_IT);
    $res = getRowById($id);
    if(!empty($res))
    return $res;
    return getRandomRow();
}

//rowid is a key on table
function getRowById($rowid=false){

   return db select from table where rowid = $rowid; 
}