mysql select rand() running slow

Question

I have 12 queries which run in one page, one for each month. They select one random user for each month. It's running pretty slow, is there a way to speed it up? db has over 5 millions records and it's growing fast.... Here's the query:

$result = mysql_query('SELECT * FROM monthly where month = "jan" and a="0" ORDER BY RAND() LIMIT 0,1;');

while($row = mysql_fetch_array($result))
{
$id = $row['id'];
$ip = $row['ip'];
$title = $row['tile'];
$user = $row['user'];
$email = $row['email'];
$phone = $row['phone'];
    }

    .
    .
    .
    .
    .
$result = mysql_query('SELECT * FROM monthly where month = "dec" and a="0" ORDER BY RAND() LIMIT 0,1;');

while($row = mysql_fetch_array($result))
{
$id = $row['id'];
$ip = $row['ip'];
$title = $row['tile'];
$user = $row['user'];
$email = $row['email'];
$phone = $row['phone'];
    }

Do not use `mysql_query`. Use the PDO: https://websitebeaver.com/php-pdo-prepared-statements-to-prevent-sql-injection#named-parameters — Artfaith, Oct 02 '20 at 15:13

score 0 · Answer 1 · answered Feb 01 '16 at 22:36

The problem with performance is that the RAND() function has to be evaluated for every row that satisfies the predicates (conditions in the WHERE clause.) Then MySQL has to perform a "Using filesort" operation to order the rows. (With the LIMIT clause, fortunately, MySQL shouldn't have to sort all of the rows; it really only needs to find the lowest value.

With the given query, as it's written, about all you can do is optimize access to the rows by making sure an appropriate index available.

... ON monthly (`month`,a)

If you tweaked the query to return only the columns you need to return, and that is a small subset of the columns in the row... replace the *

SELECT m.id
     , m.ip
     , m.tile
     , m.user
     , m.email
     , m.phone
  FROM monthly m 
 WHERE m.month = 'jan'
   AND m.a = '0'

Then add a covering index:

 ... ON monthly (`month`,a,id,ip,tile,user,email,phone)

This index would render the index ON monthly (\month`,a)` redundant, so that index could be dropped.

i'm not that good with coding, please show me a complete query, thx! — Mikey, Feb 01 '16 at 23:32

mysql select rand() running slow

1 Answers1