number - select random in mysql

MySQL: Alternativas a ORDER BY RAND()

Question

He leído sobre algunas alternativas a la función ORDER BY RAND() MySQL, pero la mayoría de las alternativas se aplican solo a dónde se necesita un único resultado aleatorio.

¿Alguien tiene alguna idea de cómo optimizar una consulta que devuelve resultados aleatorios múltiples, como este:

   SELECT u.id, 
          p.photo 
     FROM users u, profiles p 
    WHERE p.memberid = u.id 
      AND p.photo != '''' 
      AND (u.ownership=1 OR u.stamp=1) 
 ORDER BY RAND() 
    LIMIT 18 

Answer 1

ACTUALIZACIÓN 2016

Esta solución funciona mejor con una columna indexada .

Aquí hay un ejemplo simple de banco de consulta optimizado marcado con 100.000 filas.

OPTIMIZADO: 300ms

SELECT 
    g.*
FROM
    table g
        JOIN
    (SELECT 
        id
    FROM
        table
    WHERE
        RAND() < (SELECT 
                ((4 / COUNT(*)) * 10)
            FROM
                table)
    ORDER BY RAND()
    LIMIT 4) AS z ON z.id= g.id

nota sobre el límite de tiempo : límite 4 y 4 / conteo (*). Los 4 deben ser el mismo número. Cambiar la cantidad que devuelve no afecta tanto la velocidad. El punto de referencia en el límite 4 y el límite 1000 son iguales. El límite de 10,000 lo llevó a 600ms

Nota sobre unirse : aleatorizar solo el ID es más rápido que aleatorizar una fila completa. Como tiene que copiar toda la fila en la memoria, aleatorícela. La unión puede ser cualquier tabla que esté vinculada a la subconsulta Its para evitar tablescans.

note where clause : El recuento donde limita la cantidad de resultados que se están aleatorizando. Toma un porcentaje de los resultados y los ordena en lugar de toda la tabla.

note sub query : las condiciones if junt y extra where cláususe las necesita para ponerlas tanto en la subconsulta como en la subsubquery. Tener un recuento preciso y recuperar los datos correctos.

NO OPTIMIZADO: 1200ms

SELECT 
    g.*
FROM
    table g
ORDER BY RAND()
LIMIT 4

PROS

4 veces más rápido que order by rand() . Esta solución puede funcionar con cualquier tabla con una columna indexada.

CONTRAS

Es un poco complejo con consultas complejas. Necesidad de mantener 2 bases de código en las subconsultas

Answer 2

Aquí hay una alternativa, pero todavía se basa en el uso de RAND ():

  SELECT u.id, 
         p.photo,
         ROUND(RAND() * x.m_id) ''rand_ind''
    FROM users u, 
         profiles p,
         (SELECT MAX(t.id) ''m_id''
            FROM USERS t) x
   WHERE p.memberid = u.id 
     AND p.photo != '''' 
     AND (u.ownership=1 OR u.stamp=1) 
ORDER BY rand_ind
   LIMIT 18

Esto es un poco más complejo, pero dio una mejor distribución de los valores de random_ind:

  SELECT u.id, 
         p.photo,
         FLOOR(1 + RAND() * x.m_id) ''rand_ind''
    FROM users u, 
         profiles p,
         (SELECT MAX(t.id) - 1 ''m_id''
            FROM USERS t) x
   WHERE p.memberid = u.id 
     AND p.photo != '''' 
     AND (u.ownership=1 OR u.stamp=1) 
ORDER BY rand_ind
   LIMIT 18

Answer 3

Crea una columna o únete a una selección con números aleatorios (generados en, por ejemplo, php) y ordena por esta columna.

Answer 4

La solución que estoy utilizando también se publica en el siguiente enlace: ¿Cómo puedo optimizar la función ORDER BY RAND () de MySQL?

Supongo que su tabla de usuarios va a ser más grande que su tabla de perfiles, si no es así, es cardinalidad de 1 a 1.

Si es así, primero haré una selección aleatoria en la tabla de usuario antes de unirme a la tabla de perfil.

Primero haz la selección:

SELECT *
FROM users
WHERE users.ownership = 1 OR users.stamp = 1

Luego, desde este grupo, seleccione filas aleatorias a través de la probabilidad calculada. Si su tabla tiene M filas y desea seleccionar N filas al azar, la probabilidad de selección al azar debe ser N / M. Por lo tanto:

SELECT *
FROM
(
    SELECT *
    FROM users
    WHERE users.ownership = 1 OR users.stamp = 1
) as U
WHERE 
    rand() <= $limitCount / (SELECT count(*) FROM users WHERE users.ownership = 1 OR users.stamp = 1)

Donde N es $ limitCount y M es la subconsulta que calcula el recuento de filas de la tabla. Sin embargo, dado que estamos trabajando en la probabilidad, es posible tener MENOS de $ limitCount de filas devueltas. Por lo tanto, debemos multiplicar N por un factor para aumentar el tamaño del conjunto aleatorio.

es decir:

SELECT*
FROM
(
    SELECT *
    FROM users
    WHERE users.ownership = 1 OR users.stamp = 1
) as U
WHERE 
    rand() <= $limitCount * $factor / (SELECT count(*) FROM users WHERE users.ownership = 1 OR users.stamp = 1)

Por lo general, configuro $ factor = 2. Puede establecer el factor en un valor inferior para reducir aún más el tamaño de la agrupación aleatoria (por ejemplo, 1.5).

En este punto, ya habríamos limitado una tabla de tamaño M hasta aproximadamente 2N de tamaño. Desde aquí podemos hacer un JOIN y luego LIMIT.

SELECT * 
FROM
(
       SELECT *
        FROM
        (
            SELECT *
            FROM users
            WHERE users.ownership = 1 OR users.stamp = 1
        ) as U
        WHERE 
            rand() <= $limitCount * $factor / (SELECT count(*) FROM users WHERE users.ownership = 1 OR users.stamp = 1)
) as randUser
JOIN profiles
ON randUser.id = profiles.memberid AND profiles.photo != ''''
LIMIT $limitCount

En una tabla grande, esta consulta superará a una consulta ORDER normal por RAND ().

¡Espero que esto ayude!

Answer 5

Me encontré con esto hoy y estaba tratando de usar ''DISTINCT'' junto con JOINs, pero estaba obteniendo duplicados, supongo porque el RAND estaba haciendo que cada fila de JOIN fuera distinta. Me confundí un poco y encontré una solución que funciona, así:

SELECT DISTINCT t.id, 
                t.photo 
       FROM (SELECT  u.id, 
                     p.photo,
                     RAND() as rand
                FROM users u, profiles p 
                 WHERE p.memberid = u.id 
                  AND p.photo != '''' 
                  AND (u.ownership=1 OR u.stamp=1)
                ORDER BY rand) t
       LIMIT 18

Answer 6

No es el modo ORDER BY RAND() más rápido, pero más rápido que el común:

ORDER BY RAND() no es tan lento, cuando lo usa para buscar solo la columna indexada. Puede tomar todos sus identificadores en una consulta como esta:

SELECT id
FROM testTable
ORDER BY RAND();

para obtener una secuencia de identificadores aleatorios, y JOIN el resultado a otra consulta con otros parámetros SELECCIONAR o DONDE:

SELECT t.*
FROM testTable t
JOIN
    (SELECT id
    FROM `testTable`
    ORDER BY RAND()) AS z ON z.id= t.id   
WHERE t.isVisible = 1
LIMIT 100; 

en tu caso sería:

SELECT u.id, p.photo 
FROM users u, profiles p 
JOIN
    (SELECT id
    FROM users
    ORDER BY RAND()) AS z ON z.id = u.id   
WHERE p.memberid = u.id 
  AND p.photo != '''' 
  AND (u.ownership=1 OR u.stamp=1) 
LIMIT 18 

Es un método muy contundente y puede no ser adecuado para tablas muy grandes, pero aún así es más rápido que el RAND() común RAND() . Obtuve 20 veces más tiempo de ejecución buscando 3000 filas aleatorias en casi 400000.

Answer 7

Order by rand() es muy lento en tablas grandes,

Encontré la siguiente solución en un script php:

Select min(id) as min, max(id) as max from table;

Entonces haz al azar en php

$rand = rand($min, $max);

Entonces

''Select * from table where id>''.$rand.'' limit 1'';

Parece ser bastante rápido ...