Optimizing rand performance with inner join

Question

I have a SQL query like this:

SELECT `users`.`name`, `users`.`username`, `users`.`bio`, `users`.`city`, `users`.`photo` 
FROM (`onlines`) JOIN `users` 
ON `onlines`.`username`=`users`.`username` 
WHERE `users`.`offline_status` = 0 
AND `users`.`perma_ban` = 0 
AND `users`.`is_premium` = 1 
GROUP BY `onlines`.`username` 
ORDER BY RAND() LIMIT 27

I am using RAND but I am not happy with performance. How can I optimize this query?

I read this:

How can i optimize MySQL's ORDER BY RAND() function?

But I am using inner join, so this question didn't help me.

score 0 · Answer 1 · answered Jan 31 '16 at 23:48

0

One method is to add a where clause. So, if you know approximately how many rows will match the conditions, then you can reduce the data. So, if you know that about 1000 do, then randomly take about 50 of them before the final order by:

and rand()*1000 < 50

Of course, you need the estimates of the total.

answered Jan 31 '16 at 23:48

Gordon Linoff

1,242,037
58
646
786

That condition goes in the `where` clause on the `group by` clause. – Gordon Linoff Jan 31 '16 at 23:55
Do I need to remove "order by rand()" ? – Tolgay Toklar Jan 31 '16 at 23:59
If remove the `order by rand()`, the ~27 will be in a predictable order. If you want to shuffle them, then wrap it inside `SELECT * FROM ( ... ) ORDER BY RAND()` to do the shuffling after find the rows. – Rick James Feb 01 '16 at 01:27
@TolgayToklar . . . No. You want the `order by rand()`. – Gordon Linoff Feb 01 '16 at 03:26

score 0 · Accepted Answer · answered Feb 01 '16 at 01:24

0

First pick 27 random usernames at random from users, given this restriction:

WHERE `users`.`offline_status` = 0 
  AND `users`.`perma_ban` = 0 
  AND `users`.`is_premium` = 1

Then you are almost through. onlines provides almost nothing to the query; all it does is eliminate any usernames that don't show up in onlines. So simply add

  AND NOT EXISTS ( SELECT * FROM onlines WHERE username = users.username )

Then go off to the link mentioned, or to my blog, which scales better. It provide 5 efficient answers. But without answering some more questions, I can't tell you which of the answer(s) best suit your situation.

answered Feb 01 '16 at 01:24

Rick James

135,179
13
127
222

I have 350K records in users table and approximate 1k in onlines table. Getting 27 random usernames from users table does it make sense? What do you think about this? – Tolgay Toklar Feb 01 '16 at 11:47
That gets messy -- You either have to get 27 out of 1K plus verify 3 flags, or get 27 out of 350K plus verify that they are online. Either way, you may need to look a lot more than 27 before the criteria are met. Does "offline_status" match "existence in `onlines`? That could simplify the query _a lot_. – Rick James Feb 01 '16 at 17:21
Let's back up a step -- What is the purpose of the 27? Maybe we can tweak the goal to be something more easily achieved. – Rick James Feb 01 '16 at 17:22
Let me explain you: I am getting 27 online users. "onlines" table is storing online users because this is a chat web site. Also "offline_status" is like invisible status. A user can select invisible mode and when he do this, we can't see him on the online list. I don't have "offline_status" on "onlines" – Tolgay Toklar Feb 01 '16 at 17:44
Sounds like [_FLOAT case_](http://mysql.rjweb.org/doc.php/random#case_extra_float_column_for_randomizing) might work best. – Rick James Feb 02 '16 at 00:51

Optimizing rand performance with inner join

2 Answers2