CODEWITHSUNDEEP
mysqldatabase
GreyHat
GreyHat

Reputation: 11

MySQL order by rand() performance issue

I have a rather inefficient MySQL query that has been working OK until recently as the database has grown in size. I am trying to select a random record using the ORDER BY RAND() syntax, but it is extremely slow (30+ seconds) and causes issues with my PHP script. Is there a more efficient way to write this query?

    SELECT * FROM accountcampaign, accounts WHERE 
    accountcampaign.status='ACTIVE' 
    AND accountcampaign.dateLocked IS NULL
    AND accountcampaign.campaignID='1' 
    AND ( (accountcampaign.lockedIPAddress IS NULL)  
          OR (accountcampaign.lockedIPAddress='') 
        ) 
    AND ( (accountcampaign.dateLastEntry IS NULL) 
          OR (DATE(accountcampaign.dateLastEntry) < DATE(NOW())) 
        ) 
    ORDER BY RAND() LIMIT 1

Upvotes: 1

Views: 454

Answers (1)

adrien
adrien

Reputation: 4439

You don't want to use ORDER BY RAND() LIMIT 1 : it will generate one random number for every row in your table.

You should try using 2 queries :

  • the first one count the number of rows in your table
  • then in PHP you generate a random number between 0 and this count
  • then the second query get this row only

More here

Upvotes: 4

Related Questions