CODEWITHSUNDEEP
mysqlsql
Niels
Niels

Reputation: 1006

Select a random row with where statement is taking to long

I want to select a random row with a specific where statement but the query is taking to long (around 2.7 seconds)

SELECT * FROM PIN WHERE available = '1' ORDER BY RAND() LIMIT 1

The database contains around 900k rows

Thanks

Upvotes: 0

Views: 55

Answers (1)

dognose
dognose

Reputation: 20899

SELECT * FROM PIN WHERE available = '1' ORDER BY RAND() LIMIT 1

means, that you are going to generate a random number for EVERY row, then sort the whole result-set and finally retrieve one row.

That's a lot of work for querying a single row.

Assuming you have id's without gaps - or only little of them - you better use the programming language you are using to generate ONE random number - and fetch that id:

Pseudo-Example: 

result = null;
min_id = queryMinId();
max_id = queryMaxId();
while (result == null){
  random_number = random_beetween(min_id, max_id);

  result = queryById(randomNumber);
}

If you have a lot of gaps, you could retrieve the whole id-set, and then pick ONE random number from that result prior: 

id_set = queryAllIds();
random_number = random_beetween(0, size(id_set)-1);

result = queryById(id_set[random_number])

The first example will work without additional constraints. In your case, you should use option 2. This ensures, that all IDs with available=1 are pre-selected into an 0 to count() -1 array, hence ignoring all invalid ids.

Then you can generate a random number between 0 and count() -1 to get an index within that result-set, which you can translate to an actual ID, which you are going to fetch finally.

id_set = queryAllIdsWithAvailableEqualsOne(); //"Condition"
random_number = random_beetween(0, size(id_set)-1);

result = queryById(id_set[random_number])

Upvotes: 2

Related Questions