MySQL: alternative a ORDER BY RAND ()

Ho letto alcune alternative alla funzione ORDER BY RAND() di MySQL, ma la maggior parte delle alternative si applica solo a dove è necessario un singolo risultato casuale.

Qualcuno ha idea di come ottimizzare una query che restituisce più risultati casuali, come questo:

  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 

AGGIORNAMENTO 2016

Questa soluzione funziona al meglio usando una colonna indicizzata .

Ecco un semplice esempio di banco query ottimizzato contrassegnato con 100.000 righe.

OTTIMIZZATO: 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 su quantità limite : limite 4 e 4 / conteggio (*). I 4 devono essere lo stesso numero. Cambiare il numero di persone che si torna non influisce molto sulla velocità. Il benchmark al limite 4 e al limite 1000 sono gli stessi. Limite di 10.000 ha preso fino a 600ms

nota su join : Randomizzare solo l'id è più veloce di randomizzare un'intera riga. Dal momento che deve copiare l'intera riga in memoria, quindi randomizzarlo. Il join può essere qualsiasi tabella collegata alla sottoquery Its per impedire tabelle.

nota dove clausola : il conteggio in cui limita l'ammontare dei risultati che sono stati randomizzati. Prende una percentuale dei risultati e li ordina piuttosto che l'intera tabella.

note sub query : le clausole if doing joins e extra where dove è necessario inserirle sia nella sottoquery che nella sottoquery. Per avere un conteggio accurato e ritirare i dati corretti.

NON OTTIMIZZATO: 1200ms

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

PROFESSIONISTI

4x più veloce di order by rand() . Questa soluzione può funzionare con qualsiasi tabella con una colonna indicizzata.

CONS

È un po 'complesso con query complesse. È necessario mantenere 2 basi di codice nelle subquery

Ecco un’alternativa, ma è ancora basata sull’uso di 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 

Questo è leggermente più complesso, ma offre una migliore distribuzione dei valori 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 

Non è il modo più veloce, ma più veloce del comune ORDER BY RAND() :

ORDER BY RAND() non è così lento, quando lo si utilizza per trovare solo la colonna indicizzata. Puoi prendere tutti i tuoi ID in una query come questa:

 SELECT id FROM testTable ORDER BY RAND(); 

per ottenere una sequenza di ID casuali e JOIN il risultato a un’altra query con altri parametri SELECT o WHERE:

 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; 

nel tuo caso sarebbe:

 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 

È un metodo molto smussato e può non essere corretto con tabelle molto grandi, ma è comunque più veloce del comune RAND() . Ho ottenuto 20 volte più veloce tempo di esecuzione alla ricerca di 3000 righe casuali in quasi 400000.

Mi sono imbattuto in questo oggi e stavo cercando di usare “DISTINCT” insieme a JOINs, ma ottenevo i duplicati presumo perché il RAND stava distinguendo ogni riga JOINed. Mi sono confuso un po ‘e ho trovato una soluzione che funziona, come questa:

 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 

Crea una colonna o partecipa a una selezione con numeri casuali (generati ad esempio in php) e ordina da questa colonna.

La soluzione che sto usando è anche pubblicata nel link sottostante: Come posso ottimizzare la funzione ORDER BY RAND () di MySQL?

Suppongo che la tabella degli utenti sarà più grande della tabella dei profili, altrimenti la cardinalità 1 a 1.

Se è così, vorrei prima fare una selezione casuale sulla tabella utente prima di unirmi alla tabella dei profili.

Prima fare la selezione:

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

Quindi da questo pool, selezionare le righe casuali attraverso la probabilità calcasting. Se il tuo tavolo ha M righe e vuoi selezionare N righe casuali, la probabilità di selezione casuale dovrebbe essere N / M. Quindi:

 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) 

Dove N è $ limitCount e M è la sottoquery che calcola il conteggio delle righe della tabella. Tuttavia, poiché stiamo lavorando sulla probabilità, è ansible avere meno di $ limitCount di righe restituite. Pertanto dovremmo moltiplicare N di un fattore per aumentare la dimensione del pool casuale.

vale a dire:

 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) 

Di solito imposto $ factor = 2. È ansible impostare il fattore su un valore inferiore per ridurre ulteriormente la dimensione del pool casuale (ad es. 1.5).

A questo punto, avremmo già limitato una tabella di dimensioni M fino a circa 2N. Da qui possiamo fare un JOIN quindi 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 

Su una tabella di grandi dimensioni, questa query supererà la normale query ORDER di RAND ().

Spero che questo ti aiuti!

Order by rand() è molto lento su tabelle grandi,

Ho trovato la seguente soluzione alternativa in uno script php:

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

Quindi fai random in php

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

Poi

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

Sembra essere abbastanza veloce ….