Improve MySQL random

Question

I have this query:

$sql = "SELECT 
    catalogs_values.name as word, catalogs.name as catalog
FROM
    catalogs_values
        INNER JOIN
    catalogs ON catalogs_values.catalog_id = catalogs.id
WHERE
    catalogs_values.id NOT IN (SELECT 
            valueid
        FROM
            monitor
        WHERE
            userid = $user_id)
        AND catalogs_values.checked = 0
ORDER BY RAND()
LIMIT 1";

In my table I have about 1 million records and my query is very slow. Can you suggest some improvements to it?

Answer 1

Ordering by Mysql's RAND is ALWAYS slow, I use a very fast way to sort this:

1. select min and max ID from the table
2. run a php rand(min_Id, max_Id)
3. make a loop until you find an id that's really existing, believe me it's really fast if the id is a unique key as it should

4. once you are sure the ID exists exit the loop, there you have your random ID

   $SQL = " SELECT MAX( id ) as x FROM table ";
   $query = mysql_query($SQL);
   $x = mysql_fetch_assoc($query);
   $max = $x['x'];
   $ok = false;
   
   while($ok == false){
   
       $id = rand(1, $max);
       $SQL = "SELECT id FROM table WHERE id = ".$id." LIMIT 1";
   
       $query = mysql_query($SQL);
       $record = mysql_fetch_assoc($query);
   
       if((int)$record['id'] > 0){
           $ok = true;
       }
   
   }
   
   //your ID is: $record['id'];
   

Answer 2

MediaWiki (think Wikipedia's random article page) does this by assigning every row a random value, adding that to an index, and then using an indexed select:

SELECT * from `some_table` where `my_rand_column` >= RAND() LIMIT 1;

Answer 3

Try replacing the not in with a left outer join or not exists:

SELECT cv.name as word, c.name as catalog
FROM catalogs_values cv INNER JOIN
     catalogs c
     ON cv.catalog_id = c.id LEFT JOIN
     monitor m
     on cv.id = m.valueid and userid=$user_id
WHERE m.valueid is null and cv.checked = 0
ORDER BY RAND()
LIMIT 1;

That might solve the performance problem.

If it doesn't you might need another way to get a random row. One simplish approach is to select a subset of random rows and then choose just one:

select word, catalog
from (SELECT cv.name as word, c.name as catalog
      FROM catalogs_values cv INNER JOIN
           catalogs c
           ON cv.catalog_id = c.id LEFT JOIN
           monitor m
           on cv.id = m.valueid and userid=$user_id
      WHERE m.valueid is null and cv.checked = 0 and rand() < 0.001
     ) t
ORDER BY RAND()
LIMIT 1;

The inner query chooses about one in one thousand rows (the proportion may need to change depending on how many rows match the various other conditions). This reduced set is then passed to the order by rand() method for choosing just one.

Answer 4

you can take a random number like this:

$whoToTake = rand(1, 1000000);

or if you want it to be dynamic, do a simple count

select count(id) from table;

$max = THE_RESULT_OF_THE_QUERY
$whoToTake = rand(1, $max);

now do a simple query

select * 
from table
limit 1 offset {$whoToTake}

this will be much much faster

Answer 5

This is a recurring question on #mysql at freenode IRC.

Take a look at this blog post by Jan Kneschke.

It outlines how you can optimize the ORDER BY RAND() LIMIT 1 so you don't have to first order all rows, and then throw everything away, except for that one row you're keeping.

Jan explains this in more detail than I can here without totally ripping off his blog post.