How to prevent MySQL random picks 0 result

Databases
1

Hi,

I use these codes to pick up a value at random:


$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `table` ");
$offset_row = mysql_fetch_object( $offset_result );
$offset = $offset_row->offset;
$result = mysql_query( " SELECT * FROM `table` LIMIT $offset, 1 " );

I take it from here: http://akinas.com/pages/en/blog/mysql_random_row/

It’s fast and very good.

However, some times the results are equal to zero.

How do I always pick up at least 1 value or a constant number such as 10 results.

Thanks

2

My first guess is because COUNT() returns the number of rows, so if you have 20 rows, your max value will be 20, LIMIT 20, 1 would result in no rows. So you likely want (COUNT() - 1), so your new max value is 19, so LIMIT 19, 1 is valid.

3

I have put together a stored procedure which takes a single parameter and number of rows to be returned as parameters. This in essence does everything on the server so that there is no back and forth and will be slightly more efficient in the stored procedure. I have a white paper describing the procedure and the way to call it http://www.it-iss.com/mysql/mysql-stored-procedure-that-returns-random-rows-from-a-table/


DROP PROCEDURE IF EXISTS sp_random;

DELIMITER $$
CREATE PROCEDURE sp_random(IN pTableName VARCHAR(64), IN pLimit SMALLINT UNSIGNED) 
NOT DETERMINISTIC
SQL SECURITY INVOKER
COMMENT 'A procedure that returns a random set of rows from a table'
BEGIN
	DECLARE lCount INT UNSIGNED;

	SET @uQuery := CONCAT('SELECT COUNT(1) INTO @uCount FROM ', pTableName);
	PREPARE stmt FROM @uQuery;
	EXECUTE stmt;

	IF @uCount <= pLimit THEN
		-- Return all values
		SET @query := CONCAT('SELECT * FROM ', pTableName);
	ELSE
		-- Return limited number of entries
		SET @offset := ROUND((@uCount - pLimit) * RAND(),0);
		SET @query := CONCAT('SELECT * FROM ', pTableName, ' LIMIT ', @offset, ',', pLimit);
	END IF;

	PREPARE stmt FROM @query;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END;
$$
DELIMITER ;
4

Just wanted to point out this very important line, which lines up with my assumption earlier exactly. :wink: Notice how he is subtracting the number of random items he wants returned from the COUNT()

5

For grabbing a random row from the table, have you considered just grabbing the possible rows and then using array_rand() to grab the number of random rows required?

Have a read of this thread where different methods for grabbing a number of rows in a random order is discussed.

6

Depending on how many rows there are in the table and how many you want to randomly retrieve it may be worth considering simply using ORDER BY RAND() and then set the LIMIT to the number of entries you want. For tables with under about 1000 rows this is reasonably efficient. Similarly if you want to retrieve 30 rows at random from a table with fewere than about 50,000 rows.

7

Thanks guys,

cpradio method works great. I don’t have a chance to test what felgall explains, because my table records is still low. I’m looking forward to test SpacePhoenix method as well. rcashell codes look strange.