0

I have a database with 3 million records. I want it to randomly fetch 10 records. I read somewhere that you can randomly select records from a range of 1000 records so that it does not search the entire database.

I am currently using the following code which is very heavy.

$result = mysqli_query($conn, "SELECT FLOOR(RAND() * COUNT(*)) AS offset FROM miasta"); 
$offset_row = mysqli_fetch_object($result); 
$offset = $offset_row->offset; 
$result = mysqli_query($conn, "select * from (SELECT * FROM miasta) as podseld ORDER BY RAND() LIMIT $offset, 10");
$row = mysqli_fetch_array($result)

Please help me to speed up this query. Thank you in advance.

Randomly selected records take a long time to upload.

chrimaho
  • 580
  • 4
  • 22
Maciej Poznanski
  • 19
  • 1
  • If your table has a unique or primary key, e.g. row_id AUTO_INCREMENT, then you could just do M = SELECT MAX(row_id) and put in your query SELECT.... WHERE row_id IN (list of 10 +ve random numbers from 0 to M inc.). If you've deleted rows or inserted/loaded rows such that row_ids aren't contiguous, then this won't work well, but otherwise it should be fast. – Simon Goater Dec 31 '22 at 21:01
  • The answers in the marked dupe doesn't give every row an equal chance, does it? – nice_dev Jan 01 '23 at 06:43

1 Answers1

1

With updated statistics, you should be pretty quick at getting the total row count. So, avoid an ORDER BY.

Try:

SELECT 
  *
FROM miasta
WHERE RAND() <= 
  10 / (SELECT COUNT(*) FROM miasta)
LIMIT 10

As we use RAND(), here, you might get a higher number than 10 rows, so LIMIT 10. And, sometimes, you might get fewer than 10 rows (it's random), but then just re-run it until you have 10 rows.

marcothesane
  • 6,192
  • 1
  • 11
  • 21