Selecting random rows with MySQL

by

The ORDER BY RAND() solution that most people recommend doesn’t scale to large tables, as you already know.

SET @r := (SELECT FLOOR(RAND() * (SELECT COUNT(*) FROM mytable)));
SET @sql := CONCAT('SELECT * FROM mytable LIMIT 1 OFFSET ', @r);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

I cover this and other solutions in my book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming.

If you want to do this with PHP, you could do something like this (not tested):

<?php
$mysqli->begin_transaction();
$result = $mysqli->query("SELECT COUNT(*) FROM mytable")
$row = $result->fetch_row(); 
$count = $row[0]; 
$offset = mt_rand(0, $count);
$result = $mysqli->query("SELECT * FROM mytable LIMIT 1 OFFSET $offset");
...
$mysqli->commit();

More Related Contents:

  1. MySQL select 10 random rows from 600K rows fast
  2. How can i optimize MySQL’s ORDER BY RAND() function?
  3. quick selection of a random row from a large table in mysql
  4. Simple Random Samples from a Sql database
  5. How does MySQL’s ORDER BY RAND() work?
  6. How to select one row randomly taking into account a weight?
  7. quick selection of a random row from a large table in mysql
  8. Simple Random Samples from a MySQL Sql database
  9. UPDATE all records with a random number from a range
  10. Insert/ Update random date in MySQL
  11. MYSQL Select One Random record from each Category
  12. How to get mysql random integer range?
  13. mysql unique number generation
  14. convert mysql query to laravel query builder
  15. how to generate report for attendance system
  16. How can I return pivot table output in MySQL?
  17. ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
  18. Does COUNT(*) always return a result?
  19. What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?
  20. How to send email from MySQL 5.1
  21. How to see indexes for a database or table in MySQL?
  22. Order by COUNT per value
  23. How to delete a MySQL record after a certain time
  24. Exporting a table from Amazon RDS into a CSV file
  25. Force InnoDB to recheck foreign keys on a table/tables?
  26. Error: Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT
  27. Let MySQL users create databases, but allow access to only their own databases
  28. Second SELECT query if first SELECT returns 0 rows
  29. How can I import a database with MySQL from terminal?
  30. MySQL Error: #1142 – SELECT command denied to user

Leave a Comment