I have to select 4 rows randomly from a column.
Is is better to generate randomly 4 id and to perform 4 requests 'select column from database where id = ... '
Or to select all the rows in one request and to choose after?
Asked
Active
Viewed 317 times
2
-
You want to randomly pick 4 rows from your database? It's better to select them all in one request. – Anujan Jun 02 '13 at 16:41
-
What exactly are you trying to achieve? Do you have any code to show? Which approach are you using now and why? – bestprogrammerintheworld Jun 02 '13 at 17:02
3 Answers
0
If you are capable of generating random existing id's, I think the best approach is to use a clause like where id in (id1, id2, id3, id4). This will result in getting 4 records in one query, so no unnecessary query's or records are fetched.
Patrick Kostjens
- 5,065
- 6
- 29
- 46
-
Depends on the nomenclature of the IDs, the OP might have to pre-fetch a list of IDs from the entire table, pick 4 by random, and then re-query the database ;) Something like this - http://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast – Terry Jun 02 '13 at 16:45
-
True, but that is probably still more efficient than fetching all records and then pick 4 at random, unless the cost of multiple query's is very high (i.e. high latency). – Patrick Kostjens Jun 02 '13 at 16:48
-
Ah, I think I used the word re-query wrongly - I mean, you join the current database back to itself, but that is done by joining to a list of randomly selected ID from the same database (see the link I have in my previous comment) – Terry Jun 02 '13 at 16:58
0
As told before, where id in (id1, id2, id3, id4) is the fastest way from the MySQL perspective. How ever, you will need some logic in the application generating those IDs : All 4 IDs shall exist, be randomly distributed, and you want to avoid duplicates. In worst case you will be retrieving a list of all existend IDs with a huge query, extracting 4 random values, and querying again.
With all that logic to be done, it can be wise to move selection into MySQL:
SELECT * FROM foobar
ORDER BY RAND()
LIMIT 4;
You must understand that this is slow in mysql, but you have a speed gain in the application logic and can be sure to get random values equally seed all over your table.
EDIT: The comment asks if PHP is fasten in this task then MySQL. Answer is no. It is not done by "using rand". You need to have an array containing all those IDs in PHP. That is a huge query, lots of TCP traffic, huge array to be buildt in php, huge btree to be buildt by zend engine. Then, with the IDs, you must fire a second query to get the rows for those IDs.
Zsolt Szilagyi
- 4,741
- 4
- 28
- 44
-
selecting all the rows and use rand in php is it faster than order by rand? – user2409399 Jun 02 '13 at 16:51
-
@user2409399 I updated my answer and retagged your question with "php" – Zsolt Szilagyi Jun 02 '13 at 16:59
-
-
@bestprogrammerintheworld Yes. The huge query s to get all availible Ids prior to select 4. – Zsolt Szilagyi Jun 02 '13 at 17:28
-
@ZsoltSzilagy - yes, but that would depend on how many id's there are in the table? – bestprogrammerintheworld Jun 02 '13 at 17:50
-
@bestprogrammerintheworld True. If we talk about as low as 10k rows, it makes really no difference what approach he uses. In that case I prefer the SQL above for beeing more convenient and straight-forward, reducing 3 steps to 1. – Zsolt Szilagyi Jun 02 '13 at 17:52
-
@ZsoltSzilagy - I agree, but it's hard to know what is the best approach without having more info from OP. – bestprogrammerintheworld Jun 02 '13 at 17:54
0
Although the RAND() function may be slow, so far I have not had significant problems with speed. MY strategy is actually to join the database back to a query of itself returning a list of random IDs with a limit.
SELECT *
FROM table AS t1
JOIN (
SELECT rowID
FROM table
ORDER BY RAND()
LIMIT 4
) AS t2
WHERE t1.rowID = t2.rowID
There is also a more robust solution that exist - try checking out this question (asked in 2010).
