php - mysql order by rand() performance issue and solution

Question

i was using order by rand() to generate random rows from database without any issue but i reaalised that as the database size increase this rand() causes heavy load on server so i was looking for an alternative and i tried by generating one random number using php rand() function and put that as id in mysql query and it was very very fast since mysql was knowing the row id but the issue is in my table all numbers are not availbale.for example 1,2,5,9,12 like that.

if php rand() generate number 3,4 etc the query will be blank as there is no id with number 3 , 4 etc.

what is the best way to generate random numbers preferable from php but it should generate the available no in that table so it must check that table.please advise.

$id23=rand(1,100000000);
    SELECT items FROM tablea where status='0' and id='$id23' LIMIT 1

the above query is fast but generate sometimes no which is not availabel in database.

    SELECT items FROM tablea where status=0 order by rand() LIMIT 1

the above query is too slow and causes heavy load on server

Answer 1

First of, all generate a random value from 1 to MAX(id), not 100000000.

Then there are at least a couple of good solutions:

1. Use > not =

   SELECT items FROM tablea where status='0' and id>'$id23' LIMIT 1
   

   Create an index on (status,id,items) to make this an index-only query.

2. Use =, but just try again with a different random value if you don't find a hit. Sometimes it will take several tries, but often it will take only one try. The = should be faster since it can use the primary key. And if it's faster and gets it in one try 90% of the time, that could make up for the other 10% of the time when it takes more than one try. Depends on how many gaps you have in your id values.