[RESOLVED] Random Rows

[Derokorian]

Didn't know if I should attach this to the sticky, but I've read a lot about retrieving a single random row. However, my question is what if i want to return X number of random rows (not just random offset, but non-sequential).

IE: I store photographs in a table, not the image, just the image name.. anyway I want to show 5 of them across the top of the page in a banner style, however even though I want it to start "randomly" and simply adding to the offset could be beneficial if there were enough visits to the site. I don't want someone to see:

First page: 1 | 2 | 3 | 4 | 5
then if they click another link before someone else loads a page they would then see:
Second load: 2 | 3 | 4 | 5 | 6
Or worse
Third load: 3 | 4 | 5 | 6 | 7

Am I correct that ORDER BY rand() is really the only solution to this problem? while they may see the same image or two, they won't be in the same order even if its the same 5 images?

[Derokorian]

Never mind I'm a jerk Ignore me...

[dagon]

please post your solution, some one else may ins this thread and want to know how to do it.

[Derokorian]

I read the thread started by ethan22 - And decided that as long as I have less than 10k pictures (I have no where near that atm) then order by rand() really is the best way to go. At around 10k the query gets slow, but I don't forsee me ever having 10K+ photo's on my webpage to choose from.

[dagon]

SELECT id
FROM table
ORDER BY RAND( )
LIMIT 5

using a table with 10,000 rows

Query took 0.0248 sec(uncached)

using a table with 60,000 rows
Query took 0.1438 sec

500,000 rows
Query took 1.3935 sec

[Derokorian]

Table1 - 1,000 records - 0.0011608600616455 s
Table2 - 5,000 records - 0.0031518936157227 s
Table3 - 10,000 records - 0.0061018466949463 s
Table4 - 50,000 records - 0.033190965652466 s
Table5 - 100,000 records - 0.078307151794434 s

// Setup

<?php

include("./includes/conn.php");

function insertTest($table,$count) {
	GLOBAL $db;
	for( $i=1; $i<=$count; $i++ ) {
		$db->query('INSERT INTO '. $table .' SET data=\''. md5($i*rand()) .'\'');
	}
	return;
}

$db->query('CREATE TABLE IF NOT EXISTS table1 (
		id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
		data VARCHAR(100)
	)');
$db->query('CREATE TABLE IF NOT EXISTS table2 (
		id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
		data VARCHAR(100)
	)');
$db->query('CREATE TABLE IF NOT EXISTS table3 (
		id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
		data VARCHAR(100)
	)');
$db->query('CREATE TABLE IF NOT EXISTS table4 (
		id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
		data VARCHAR(100)
	)');
$db->query('CREATE TABLE IF NOT EXISTS table5 (
		id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
		data VARCHAR(100)
	)');

insertTest('table1',1000);
insertTest('table2',5000);
insertTest('table3',10000);
insertTest('table4',50000);
insertTest('table5',100000);

// Testing

<?php

include("./includes/conn.php");

$start1 = microtime(TRUE);
$db->query('SELECT id FROM table1 ORDER BY rand() LIMIT 1');
$finish1 = microtime(TRUE);
$table1 = $finish1 - $start1;

$start2 = microtime(TRUE);
$db->query('SELECT id FROM table2 ORDER BY rand() LIMIT 1');
$finish2 = microtime(TRUE);
$table2 = $finish2 - $start2;

$start3 = microtime(TRUE);
$db->query('SELECT id FROM table3 ORDER BY rand() LIMIT 1');
$finish3 = microtime(TRUE);
$table3 = $finish3 - $start3;

$start4 = microtime(TRUE);
$db->query('SELECT id FROM table4 ORDER BY rand() LIMIT 1');
$finish4 = microtime(TRUE);
$table4 = $finish4 - $start4;

$start5 = microtime(TRUE);
$db->query('SELECT id FROM table5 ORDER BY rand() LIMIT 1');
$finish5 = microtime(TRUE);
$table5 = $finish5 - $start5;

echo '
Table1 - 1,000 records - '. $table1 .' s <br />
Table2 - 5,000 records - '. $table2 .' s <br />
Table3 - 10,000 records - '. $table3 .' s <br />
Table4 - 50,000 records - '. $table4 .' s <br />
Table5 - 100,000 records - '. $table5 .' s <br />
';

Results on my personal computer. Which are better than my webhost - probably because its on a shared machine. So definitely doesn't seem to matter to, for my usage to use RAND() maybe if you get millions of rows...

Almost identical results with limit 5 or 10 or 15...

[Derokorian]

Further Testing with larger tables, Results:

Table1 - 10,000 records - 0.0065109729766846 s
Table2 - 50,000 records - 0.035086154937744 s
Table3 - 100,000 records - 0.079008102416992 s
Table4 - 500,000 records - 0.40544509887695 s
Table5 - 1,000,000 records - 0.9854371547699 s

[Derokorian]

Still more benchmarking:

Table1 - 1,000,000 records - 0.99223208427429 s
Table2 - 2,000,000 records - 1.9542500972748 s
Table3 - 3,000,000 records - 2.9294540882111 s
Table4 - 4,000,000 records - 3.9025168418884 s
Table5 - 5,000,000 records - 5.4086110591888 s

[bradgrafelman]

If you want these benchmarks to be realistic, try filling the table with more data than just the id column, and try simulating 10-20 simultaneous requests.

[Derokorian]

It's got data in it, in the form of md5 string. Example data, pulled direclty from the tables.

id DATA
1485412 f60c11881acab36caec5d6e6ce308caf
1132831 8060c6bde89e79104ed9d389b982fbb3
1482996 f76c088137f636b038ce86b63ff164b4
1328701 eca7f8e3477cd44e3b49ff1e50fa27b5
1509826 9f2f19cb10e03cb5504ad6c742cd77e1

I don't know how to simulate that many simultaneous connections

[johanafm]

If your php has support for pcntl_fork, you could create several child threads, let them wait until a specific time, e.g. using mt_srand for each child, and then usleep(200 + mt_rand(0,200)) until the specified time is reached, for example $start = time() + 4 which is set before forking begins.

Once the specified time is past, each child will being working, within less than 1ms of each other, which should be sufficient for queries that take far longer than that. This is where you execute (and time) the query.

Just remember that each child needs to create it's own connection to the db, using new PDO, new mysqli or whatever you use.

If you're lacking pcntl_fork support in your php installation, you could achieve the same by creating a script which basically does the same thing, i.e. create a db connection, sleep until a specific time is reached, and then executes (and times) the query.
At this point you simply open up several terminals (if working locally), or several SSH connections / browser connections, and run the script in all of them. If you specify a time 1 minute in the future, you will have ample time to request the page before the query is executed, i.e.

$start = '1 minute in the future, but hardcoded, not using time()';
while ($start < time)
{
    usleep(4000);
}
# execute and time query