CODEWITHSUNDEEP
phpmysql
StudentX
StudentX

Reputation: 2323

Select random row where condition apply effectvely

I have a table of names with structure likes this :

id int(11) -Auto Increment PK
name varchar(20)
gender varchar(10)
taken tinyint - bool value

I want to get a random name of a single row where gender is say male and taken is false. How can I do that without slowing down ?

What comes to mind is, SELECT all the rows where gender = male and taken = false. Then use php's rand(1, total_rows) and use the name from that randomly generated record number for the array of results.

Or I can use, but RAND() is going to slow down the process (taken from other questions on stackoverflow)

SELECT * FROM xyz WHERE (`long`='0' AND lat='0') ORDER BY RAND() LIMIT 1

Upvotes: 0

Views: 406

Answers (1)

charlee
charlee

Reputation: 1369

You can take the following approach:

  1. select the id list that meet your criteria, like SELECT id FROM table WHERE name=...
  2. choose an id randomly with php
  3. fetch whole data with that id, like SELECT * FROM table WHERE id=<id>

This approach would maximize the query cache in MySQL. The query in step 3 has a great chance of hitting the same id, in which case query cache can accelerate database access. Further more, if caching like memcached or redis is used in the future, step 3 can also be taken care of by them, without even going to db.

Upvotes: 1

Related Questions