How does MySQL’s ORDER BY RAND() work?

by

While there’s no such thing as a “fast order by rand()”, there is a workaround for your specific task.

For getting any single random row, you can do like this german blogger does: http://web.archive.org/web/20200211210404/http://www.roberthartung.de/mysql-order-by-rand-a-case-study-of-alternatives/ (I couldn’t see a hotlink url. If anyone sees one, feel free to edit the link.)

The text is in german, but the SQL code is a bit down the page and in big white boxes, so it’s not hard to see.

Basically what he does is make a procedure that does the job of getting a valid row. That generates a random number between 0 and max_id, try fetching a row, and if it doesn’t exist, keep going until you hit one that does. He allows for fetching x number of random rows by storing them in a temp table, so you can probably rewrite the procedure to be a bit faster fetching only one row.

The downside of this is that if you delete A LOT of rows, and there are huge gaps, the chances are big that it will miss tons of times, making it ineffective.

Update: Different execution times

SELECT * FROM table ORDER BY RAND() LIMIT 1; /30-40 seconds/

SELECT id FROM table ORDER BY RAND() LIMIT 1; /0.25 seconds/

SELECT id, username FROM table ORDER BY RAND() LIMIT 1; /90 seconds/

I was sort of expecting to see approximately the same time for all three queries since I am always sorting on a single column. But for some reason this didn’t happen. Please let me know if you any ideas about this.

It may have to do with indexing. id is indexed and quick to access, whereas adding username to the result, means it needs to read that from each row and put it in the memory table. With the * it also has to read everything into memory, but it doesn’t need to jump around the data file, meaning there’s no time lost seeking.

This makes a difference only if there are variable length columns (varchar/text), which means it has to check the length, then skip that length, as opposed to just skipping a set length (or 0) between each row.

More Related Contents:

  1. quick selection of a random row from a large table in mysql
  2. How to select one row randomly taking into account a weight?
  3. quick selection of a random row from a large table in mysql
  4. How can I select data from different tables?
  5. Select all columns except one in MySQL?
  6. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  7. Simple Random Samples from a Sql database
  8. MySQL: Select Random Entry, but Weight Towards Certain Entries
  9. What is MySQL row order for “SELECT * FROM table_name;”?
  10. Select mysql query between date?
  11. mysql select from n last rows
  12. MySQL select with CONCAT condition
  13. Checking multiple columns for one value
  14. Sort by order of values in a select statement “in” clause in mysql
  15. MySQL – How to search for exact word match using LIKE?
  16. MySQL – How to select data by string length
  17. Return a default value if single row is not found
  18. MySQL – Select the last inserted row easiest way
  19. Sum results of a few queries and then find top 5 in SQL
  20. MySQL SELECT LIKE or REGEXP to match multiple words in one record
  21. Syntax error when using except in a query
  22. Simple Random Samples from a MySQL Sql database
  23. SQL query to check if a name begins and ends with a vowel
  24. Using index, using temporary, using filesort – how to fix this?
  25. MySQL : retrieve a large select by chunks
  26. MySQL – insert data from another table merged with constants
  27. SELECT INTO Variable in MySQL DECLARE causes syntax error?
  28. How to SELECT by MAX(date)?
  29. MySQL matching unicode characters with ascii version
  30. mysql unique number generation

Leave a Comment