CODEWITHSUNDEEP
mysqlrandom
MrEyes
MrEyes

Reputation: 13690

Optimising a MySQL select query to get a single random row with where clauses

I have the following table (contains over a million rows):

**MyTable**
id - int(10) 
externalId - smallint(5)
description - varchar(250)
status - smallint(6)
entrydate - int(10) (unix timestamp)

I would like to be able to select a single random row (I only need the id value in the resultset) from this table with the following where clause:

/* The number of external ids here can range from 1 to around 50 */
WHERE externalId in (1,2,3,4,x,x,x)
/* Status is fixed at 2 for all calls */
AND status = 2
/* Entry date is typically (now - 30 days) or may be excluded all together */
AND entrydate > 1279887765

The obvious solution is to use rand() however I have found that the performance of this is somewhat lacking and this is fairly well documented. So does anybody have any alternative solutions?

To give a little background: I intend to use this query on a PHP driven website and the input parameters depend on the user so these will change from user to user. It is also worth mentioning that I cannot change the table structure at all.

Thanks in advance.

Upvotes: 1

Views: 231

Answers (2)

Ross Snyder
Ross Snyder

Reputation: 1975

You could do two queries - one to get the count of total possible rows, then use that count to generate a random integer from zero to ($count - 1) in PHP, and then add "LIMIT $rand, 1" to the end of the second query.

Upvotes: 0

DrColossos
DrColossos

Reputation: 12998

When you say, you use PHP, why not do it with the language?

Say you know you have 100.000 rows, generate a random number in PHP that's within this range, and perform a LIMIT query

SELECT * FROM mytable ORDER BY id LIMIT $randomNumber, 1;

Upvotes: 1

Related Questions