I have a MySQL database running for a multiplayer game server and I've been trying to identify a performance bottleneck. I got the slow query logs and the slowest queries by time are like this (the matchmaking system):

select `accountID` from GameAccountProfile where `active`=1 and 
`lastActiveTime`<1482549810 and `inTutorial`=0 and `defenseRating`>0 and
`league`=4 and `protectedPeriod`>1482550190 and `lastActiveTime`>1481945090 and
`accountID`!=986702 and `accountID`!=10 and `accountID`!=10 and
`accountID`!=463126 and `accountID`!=184374 and `defenseRating`>=3.3530639 and
`defenseRating`<=6.2271186 and `level`>=-8 and `level`<=2
order by rand() limit 1;

There are currently about 1.3 million rows in this table. I read that the rand() sorting may be a serious performance hit and when I ran an explain on it in MySQL Workshop on the query, it did highlight it in red as being a performance hit.

Also, I realized that the only index for any of these where conditions is on the account ID column. I'm considering adding a composite index on the "active", "inTutorial", "level", and "league" columns as they don't change often (arranged starting with the least often changed).

How can I get a random account ID with these conditions without using rand() in an order clause? I need to ensure all accounts get targeted equally but rand() seems to be a clear performance hit.

Is there any other way I can improve the performance of this query?

  • 1
    There are many good questions and articles out there about this topic... here are two from SO just as FYI in case you find methods mentioned (even in unaccepted other answers per each question).... stackoverflow.com/questions/1823306/… and stackoverflow.com/questions/1244555/… Commented Dec 27, 2016 at 1:03
  • can you add something like this WHERE [your conditions] AND [id or accountID] >= rand()*maxid LIMIT1; where maxid can be a old id so you not must get the actual maxid every time Commented Dec 27, 2016 at 20:22

2 Answers 2


There is no good way to do ORDER BY RAND() LIMIT 1, but this has several suggestions. Some depend on AUTO_INCREMENT, some based on an extra column.


If you only want one it's easy. Just choose a random offset and get that one. It's very fast and because you don't need to sort the table at all:

$total = query("SELECT COUNT(*) FROM GameAccountProfile WHERE blah");
$offset = rand(0, $total-1);
$id = query("SELECT accountID FROM GameAccountProfile WHERE blah LIMIT $offset, 1");

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.