1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Pull 20 random results from table, please improve my query

Discussion in 'MySQL' started by Kerosene, Mar 5, 2011.

  1. #1
    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):
     
    Last edited: Mar 5, 2011
    Kerosene, Mar 5, 2011 IP
  2. AstarothSolutions

    AstarothSolutions Peon

    Messages:
    2,680
    Likes Received:
    77
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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?
     
    AstarothSolutions, Mar 7, 2011 IP
    Kerosene likes this.
  3. plog

    plog Peon

    Messages:
    298
    Likes Received:
    11
    Best Answers:
    1
    Trophy Points:
    0
    #3
    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.
     
    Last edited: Mar 7, 2011
    plog, Mar 7, 2011 IP
    Kerosene likes this.
  4. Kerosene

    Kerosene Alpha & Omega™ Staff

    Messages:
    11,366
    Likes Received:
    575
    Best Answers:
    4
    Trophy Points:
    385
    #4
    @ 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 :p

    @ 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 :D ... rand() would take forever.

    It's all up and running now. Seems to be working.
     
    Last edited: Mar 7, 2011
    Kerosene, Mar 7, 2011 IP
  5. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #5
    jestep, Mar 8, 2011 IP