0

I have the following query and I know that I will hopefully be working with a large data set to query. I noticed that the rand() adds significant time to the query and after reading some people say its pretty slow.

Just wondered how I could adjust my query to help speed things up.

SELECT * 
FROM users
WHERE instagram_id NOT 
IN (
 SELECT following
 FROM user_follows WHERE
 instagram_id = 'insta123' 
) AND 
instagram_id != 'insta123' AND
current_credits > 0
ORDER BY RAND()
LIMIT 0 ,1
Micha Wiedenmann
  • 19,979
  • 21
  • 92
  • 137
ORStudios
  • 3,157
  • 9
  • 41
  • 69
  • possible duplicate of [MySQL: Alternatives to ORDER BY RAND()](http://stackoverflow.com/questions/1823306/mysql-alternatives-to-order-by-rand) – Micha Wiedenmann Aug 19 '15 at 09:08
  • Instead of a "NOT IN SELECT" you could try using a LEFT JOIN join on the user_follows table with join criteria beeing user_follows.instagram_id = 'insta123' and user_follows.following = users.instagram_id and test that the join gives you a null in your where condition. Might be more efficient but depends on your dta set. You also will have to change the select * by a select distinct users.*... – Dan Aug 19 '15 at 09:13
  • furthermore: http://stackoverflow.com/questions/2663710/how-does-mysqls-order-by-rand-work which suggests: SELECT * FROM Table T JOIN (SELECT CEIL(MAX(ID)*RAND()) AS ID FROM Table) AS x ON T.ID >= x.ID LIMIT 1; – Micha Wiedenmann Aug 19 '15 at 09:15
  • Tried the solution in the suggested answer with no difference to the performance of the query. At the minute I am searching around 10,000 users and over 1,000,000 relationships and it is taking around 2 seconds. If I strip the rand out and select all the rows I am getting a time of 0.0004 sec – ORStudios Aug 19 '15 at 11:03

0 Answers0