Fastest Random Selection Where Column X Is Y (null)

Currently I am using: SELECT * FROM table AS t1 JOIN ( SELECT (RAND() * (SELECT MAX(id) FROM table where column_x is null)) AS id ) AS t2 WHERE t1.id >= t2.id

Solution 1:

Getting a genuinely random record can be slow. There's not really much getting around this fact; if you want it to be truly random, then the query has to load all the relevant data in order to know which records it has to choose from.

Fortunately however, there are quicker ways of doing it. They're not properly random, but if you're happy to trade a bit of pure randomness for speed, then they should be good enough for most purposes.

With that in mind, the fastest way to get a "random" record is to add an extra column to your DB, which is populated with a random value. Perhaps a salted MD5 hash of the primary key? Whatever. Add appropriate indexes on this column, and then simply add the column to your ORDER BY clause in the query, and you'll get your records back in a random order.

To get a single random record, simply specify LIMIT 1 and add a WHERE random_field > $random_value where random value would be a value in the range of your new field (say an MD5 hash of a random number, for example).

Of course the down side here is that although your records will be in a random order, they'll be stuck in the same random order. I did say it was trading perfection for query speed. You can get around this by updating them periodically with fresh values, but I guess that could be a problem for you if you need to keep it fresh.

The other down-side is that adding an extra column might be too much to ask if you have storage constraints and your DB is already massive in size, or if you have a strict DBA to get past before you can add columns. But again, you have to trade off something; if you want the query speed, you need this extra column.

Anyway, I hope that helped.

Solution 2:

I don't think you need a join, nor an order by, nor a limit 1 (providing the ids are unique).

SELECT*FROM   myTable
WHERE  column_x ISNULLAND id = ROUND(RAND() * (SELECTMAX(Id) FROM myTable), 0)

Solution 3:

Have you ran explain on the query? What was the output?

Why not store or cache the value of : SELECT MAX(id) FROM table where column_x is null and use that as a variable. your query would then become:

$rand = rand(0, $storedOrCachedMaxId);

SELECT*FROMtableAS t1
WHERE 
  t1.id >= $rand
  and column_x isnullORDERBY t1.id ASC
LIMIT 1

A simpler query will likely be easier on the db.

Know that if your data contains sizable holes - you aren't going to get consistently random results with these kind of queries.

Solution 4:

I'm new to MySQL syntax, but digging a little further I think a dynamic query might work. We select the Nth row, where the Nth is random:

SELECT@r :=CAST(COUNT(1)*RAND() AS UNSIGNED) FROMtableWHERE column_x isnull;

PREPARE stmt FROM'SELECT * 
FROM table
WHERE column_x is null
LIMIT 1 OFFSET ?';

EXECUTE stmt USING@r;