This content originally appeared on Bram.us and was authored by Bramus!
Doing a ORDER BY RAND() in MySQL is bad. Very bad. As Tobias Petry details (and Bernard Grymonpon always used to tell at local meetups):
Ordering records in a random order involves these operations:
- Load all rows into memory matching your conditions
- Assign a random value
RANDOM()to each row in the database- Sort all the rows according to this random value
- Retain only the desired number of records from all sorted records
His solution is to pre-add randomness to each record, in an extra column. For it he uses a the Geometric Datatype POINT type. In Postgres he then uses the following query that orders the records by distance from a new random point.
SELECT * FROM repositories ORDER BY randomness <-> point(0.753,0.294) LIMIT 3;~
For MySQL you also have a POINT class (ever since MySQL 5.7.6), but don’t really see how that would work there as ST_Distance would need to be called for each row:
SET @randomx = RAND();
SET @randomy = RAND();
SELECT *, ST_X(randomness), ST_Y(randomness), ST_Distance(POINT(@randomx, @randomy), randomness) AS distance FROM repositories ORDER BY distance DESC LIMIT 0,3;In that scenario I’d simply rely one single float value that contains the pre-randomness …
ALTER TABLE `repositories` ADD `randomness` FLOAT(17,16) UNSIGNED NOT NULL AFTER `randomness`;
ALTER TABLE `repositories` ADD INDEX(`randomness`);
UPDATE `repositories` SET randomness = RAND() WHERE 1; -- Update existing records. New records would have this number pre-generated before inserting… and then do something like this:
SET @randomnumber = RAND(); -- This number would typically be generated by your PHP code
SELECT * FROM repositories WHERE randomness < @randomnumber ORDER BY randomness DESC 0,3;Unlike the query using POINT(), this last query is written in such a way that it can leverage the index created on the randomness column ?
~
How to optimize ORDER BY RANDOM() →
Via Freek
This content originally appeared on Bram.us and was authored by Bramus!
Bramus! | Sciencx (2021-06-07T21:20:00+00:00) How to optimize ORDER BY RANDOM(). Retrieved from https://www.scien.cx/2021/06/07/how-to-optimize-order-by-random/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.