-2

Possible Duplicate:
MySQL: How to retrieve a random row or multiple random rows?
Pull 5 random records from mysql database

I have a large list of domains in my database in the table "list" under the column "website".

There are about 140 million rows and I just need to know how to echo a random one.

Basically like this:

<?php include('directory/database.php'); ?>

WHATEVER CODE WORKS FOR GETTING A RANDOM DOMAIN FROM THE DATABASE

<?php
echo $domain;
?>

There's just a huge amount of records so I need to know the quickest way to just have one of them randomly selected and displayed on a page. Thanks!

Community
  • 1
  • 1
user1848777
  • 551
  • 1
  • 4
  • 9
  • 1
    And http://stackoverflow.com/questions/211329/quick-selection-of-a-random-row-from-a-large-table-in-mysql – Michael Berkowski Nov 25 '12 at 03:11
  • Everything that I've found on Google and StackOverflow have answers like simply using `SELECT * from TABLE order by RAND() LIMIT 1` - I need something that will work on a table with 140 million rows. – user1848777 Nov 25 '12 at 03:14

1 Answers1

0

You definitely don't want to use ORDER BY RAND(). MySQL has to build a temporary table. If your table has a unique id column, something like this is much better:

SELECT * FROM `table`
WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` )
ORDER BY id LIMIT 1;

See this blog post for other approaches that work well in php.

Note: This simply repeats my answer on this thread.

Community
  • 1
  • 1
Ted Hopp
  • 232,168
  • 48
  • 399
  • 521