0

Right now I'm selecting 3 random rows from a table using ORDER BY Rand(); as pointed out all over the web this method is very slow. I am using it on a database with only 30 rows and it takes a long time to return the value. Most of the other solutions i've found only return one row. What's the best way to return multiple random rows?

$get_projects_query = 
  mysql_query(
    "SELECT p_id FROM project_data WHERE p_featured='1' ORDER BY Rand() LIMIT 3"
  ) or die(mysql_error());

while($project_row = mysql_fetch_array($get_projects_query)) {?>
  //Do stuff
} 
//end three random featured projects loop)
ajreal
  • 46,720
  • 11
  • 89
  • 119
Brooke.
  • 3,691
  • 13
  • 49
  • 80
  • 2
    @BandonRandon - You should verify which part/method is slow. `order by rand()` would not slow on table with 30 rows only. – ajreal Dec 02 '10 at 07:37
  • Are you sure it’s actually the query that takes that long? – Gumbo Dec 02 '10 at 07:39
  • @ajreal, I wonder if it's my server being slow then or the fact that I'm loading images. humm..... – Brooke. Dec 02 '10 at 07:40
  • @BandonRandon - yup, images .. how many bytes ? re-think – ajreal Dec 02 '10 at 07:42
  • @ajreal, not horribly big. Are you thinking `rand()` will be faster than the pulling all the data into a php array and using `shuffle()? – Brooke. Dec 02 '10 at 07:48
  • @BandonRandon - I assume, is shared-hosting ? Any bandwidth throttling? Of course doing in PHP is efficient if you still think order by rand is slow. Some references - http://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast – ajreal Dec 02 '10 at 08:02

4 Answers4

1

First select the table count. Order by an index column (probably the primary key). Then select limit 1 with offset = to a random integer in the range of 0 to the table count.

drewrobb
  • 1,574
  • 10
  • 24
1

If there are only 30 rows, you have a couple other options.

Option #1

  1. fetch all 30 rows.
  2. call shuffle()
  3. read the first 3 off the pile

Option #2

  1. Generate 3 random numbers from 0 to 29. Call them r1, r2, r3.
  2. SELECT ... WHERE p_id IN (r1, r2, r3)

Option #3

  1. SELECT p_id FROM ...
  2. while ($row = mysql_fetch_assoc($result)) $idList[] = $row["p_id"];
  3. shuffle($idList)
  4. SELECT ... WHERE p_id IN ($idList[0], $idList[1], $idList[2])
Paul Schreiber
  • 12,531
  • 4
  • 41
  • 63
  • 1
    Option #2 has to assume the row primary keys are sequential and without gaps. For such a small number of rows Option #1 seems very reasonable, even storing the results in memcache or similar. – Fanis Hatzidakis Dec 02 '10 at 07:22
  • Certainly. But if there are only 30 rows, you would hope the IDs are monotonically increasing. – Paul Schreiber Dec 02 '10 at 07:25
  • Sorry i'm pretty new at this, with option #3 I'm assuming that $idList is a php array? – Brooke. Dec 02 '10 at 07:37
  • Yes. You create the array (not shown), fill it with the ID numbers from the table (step 2) and then use it to generate the second query. – Paul Schreiber Dec 02 '10 at 15:30
0

If you only have 30 rows why not download all 30 rows to your client and then generate 3 random row indexes

cordialgerm
  • 8,403
  • 5
  • 31
  • 47
0

As everyone else said. Having only 30 items in your query do like this:

  1. do your query without RAND()
  2. build an array / object with all the items - E.g.: items[]
  3. generate 3 random numbers (x1, x2, x3) between 1 and the length of the array and then display them: items[1], items[2], items[3]

Or your query has only for the moment 30 results but in time it will be bigger and bigger?

chris_so
  • 862
  • 1
  • 11
  • 20
  • Right now I only have 5 querys. It's for my portfolio so I don't see it getting much bigger than 30 that would be a lot of projects to look at. I was thinking 30 MAX. – Brooke. Dec 02 '10 at 07:40