0
 SELECT name
  FROM random AS r1 JOIN
       (SELECT (RAND() *
                     (SELECT MAX(id)
                         FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1;

While looking up optimization for MySQL, I have came up with this query. But, it doesn't entirely serve my purpose because I want to pull random users from my database, but I also have a field for active. So I want to ensure that "active = 1", but I'm not sure where I would plug that into this query to make it work.

Indaleco
  • 49
  • 1
  • 5

2 Answers2

3

I think this should do it:

SELECT name
FROM random r1
JOIN (SELECT RAND()*MAX(id) randid
      FROM random
      WHERE active = 1) r2
WHERE r1.id >= r2.randid
AND r1.active = 1
ORDER BY r1.id
LIMIT 1

You need to test active = 1 in both the subquery and the main query.

Barmar
  • 741,623
  • 53
  • 500
  • 612
1
SELECT * FROM users WHERE active = 1 ORDER BY RAND() LIMIT 1;

While there are more optimized alternatives to RAND(), I'd encourage you to simplify your query starting with the above and optimize later.

Community
  • 1
  • 1
Jason McCreary
  • 71,546
  • 23
  • 135
  • 174
  • Based on the research I've done (been looking for about 2 hours), this offers performance issues so I was going to avoid using it. – Indaleco Jul 30 '13 at 03:14
  • 1
    Yes. Depending on the number of active records, indexes, etc the above may still be more efficient than your nested queries. – Jason McCreary Jul 30 '13 at 03:16
  • I will use it for the time being then - Until I need to optimize it more. Still open to suggestion though. – Indaleco Jul 30 '13 at 03:19
  • 1
    It's good to think about optimization. But not to the point where you're losing hours of work over it. Nonetheless, there are several answers in the link above. – Jason McCreary Jul 30 '13 at 03:22