mercredi 7 avril 2021

Quickly Select Random Rows With Where Condition

Is it possible to quickly select random rows from a table, while also using a where condition?

Example:

SELECT * FROM geo WHERE placeRef = 1 ORDER BY RAND() LIMIT 1

This can take 10+ seconds.

I found this, which is sometimes quick, sometimes very slow:

(SELECT *
FROM geo
INNER JOIN ( SELECT RAND() * ( SELECT MAX( nameRef ) FROM geo ) AS ID ) AS t ON geo.nameRef >= t.ID
WHERE geo.placeRef = 1
ORDER BY geo.nameRef
LIMIT 1)



Aucun commentaire:

Enregistrer un commentaire