I have a really simple database consisting of around 30,000 names, and an id row. All I want to do is grab 20 of these names randomly. This works, but it's slow and clumsy. $result = mysql_query("SELECT * FROM names order by rand() limit 20"); Code (markup): Let's say I pick 20 random numbers between 1 and 30,000 using PHP. What is the query for using my 20 random numbers to select the corresponding rows? Can it be done with 1 call? EDIT, answering my own question: $x = 0; $max=30000; $myrand=rand(1,$max); while ($x<20){ $x+=1; $myrand.=','.rand(1,$max);} $result = mysql_query("SELECT * FROM mynames WHERE myid IN ($myrand)"); Code (markup):
The first issue with the script as it stands is that firstly you state its "around" 30,000 records but you have now hard coded that number into your logic, what happens in the future if the number of names doubles? or halves? (obviously not knowing all the details of the site/application making some general assumptions). The other possible issue is around the fact that you are calling individual IDs, are all IDs going to be valid? You will never delete or disable an ID for any reason at all?
A bunch of random thoughts: How much faster is this query: $result = mysql_query("SELECT * FROM names"); PHP: Than this one: $result = mysql_query("SELECT name FROM names order by rand() limit 20"); PHP: I know the first one doesn't do what you want, but it will give you an idea of how much the rand() is costing you in time and will allow you to test other query methods against it to see how effective a method is. If all you want are names, then quit using the * in your query. $result = mysql_query("SELECT name FROM names order by rand() limit 20"); What if you have multiple Jane Smiths in your data--its possible both of those might be pulled. To eliminate legitimate dupes you would need to use a GROUP BY clause.
@ AstarothSolutions - it's a static database. Once I've finished compiling it, I'll never edit it again. Which allows for a certain level of laziness in my queries. Hard coding the number into PHP is kinda ugly, but it's oh so easy @ plog - yep, I've changed the *. It's no big deal if duplicates are pulled. I did a recount after finalizing the database last night - it's more like 230,000 entries ... rand() would take forever. It's all up and running now. Seems to be working.
I agree the the php to generate the list of random id's is a better way of doing it. I would immediately switch rand for mt_rand - http://php.net/manual/en/function.mt-rand.php.