SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ORDER BY + LIMIT performance

    I would like to show random 5 results from all rows.

    If I try LIMIT 5 without ORDER BY, it is for 2000% faster than using ORDER BY rand() LIMIT 5. But I need to show random 5 results and not first 5 results.

    I was thinking to first do a query SELECT COUNT and than use my script to get random number in a range of number of rows and than use LIMIT in another query. But also COUNT takes similar time as selecting all rows and using ORDER BY.

    Is there any better solution than using ORDER BY rand()?
    Dilster.com - Dating blog where you can find new dating tips, advices and ideas or your partner.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    run a simple query to get MIN(id) and MAX(id), then use php to generate a dozen or so potential numbers randomly inside this range, and use those to pull rows -- no ORDER BY, no LIMIT

    the reason for "dozen or so" is because you want to allow for deletions, i.e. id numbers that aren't there anymore, and of course pulling a dozen rows is only a teensie weensie bit more work than pulling five, and you wanna make sure you get at least five

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It takes about the same time to make SELECT MAX(k2.who) or to make SELECT COUNT(k2.who). Also I can not use MAX id because ids won't be by increment order.

    What do you think about the following solution:
    PHP Code:
    $counter=1;
    $randnumber=100000;
    while(
    $counter>0){
      
    $random=rand(5,$randomnumber);
      
    $result=mysql_query("SELECT who .... WHERE who>$random LIMIT 5");
      
    $numrows=mysql_num_rows($result);
      if(
    $numrows>4){
        
    //OK print results
        
    $counter=0
      
    }else{
        
    $randomnumber=$randomnumber/2;
      }

    Dilster.com - Dating blog where you can find new dating tips, advices and ideas or your partner.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ernest1a View Post
    Also I can not use MAX id because ids won't be by increment order.
    i don't understand what this means

    what kind of primary key does the table have?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe I wrong in wrong words. id will be selected depend on criteria. So ids which matches will be like 11,234,25234,23,64545 and not like 11,12,13,14. So Max doesn't help me anything because if I select id between max and min, it can happen this id won't match criteria as not all ids in this range are matching.
    Dilster.com - Dating blog where you can find new dating tips, advices and ideas or your partner.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    11,12,13,14 are not random

    you said you wanted random
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes they are not, that is why I said not like 11,12,13,14 because I didn't wrote in the first post enough information. I wrote 5 results from all rows but the thing is I need 5 results from all rows that are matching WHERE condition.

    So SELECT id FROM tablename WHERE testing>100 AND ... would give 200.000 results and they would be like 12,2354,6654,234,34, ...
    Dilster.com - Dating blog where you can find new dating tips, advices and ideas or your partner.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i think we don't understand each other

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Non-Member
    Join Date
    Mar 2010
    Location
    UK
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I want to be able to submit Limit Entry Orders and the Stop Loss Orders for those Limit Entries at the same time. I dont want to wait for the Limit Entry Order to be filled before the Stop Order is entered.

    Can I do this in some way?

    Thanks.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    dordhs, you have hijacked a thread with a completely unrelated question

    perhaps you should start a new thread and provide more background information on what you're trying to do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ernest1a: First thing you want to do, is to make sure your ORDER BY field is an index.

    See how long that takes, if it's still to slow, you will need to do this in a few queries, but for this you need a sequential primary key:

    Code:
    SELECT MAX(id), MIN(id) FROM foo;
    If you have no holes in your sequence:
    Code:
    my $ids = array();
    for ($i =0; $i < 5; $i++) {
      array_push($ids, rand($minId, $maxId));
    }
    SELECT * FROM foo WHERE id IN join(',',$ids)
    If you have a FEW holds in there (less than 25%), and don;t care that you always get 5 results.
    Code:
    my $ids = array();
    for ($i =0; $i < 10; $i++) {
      array_push($ids, rand($minId, $maxId));
    }
    SELECT * FROM foo WHERE id IN join(',',$ids) LIMIT 5
    If you have allot of holes in there:
    Code:
    SELECT * FROM foo WHERE id > rand($minId, $maxId) LIMIT 1
    UNION
    SELECT * FROM foo WHERE id > rand($minId, $maxId) LIMIT 1
    UNION
    SELECT * FROM foo WHERE id > rand($minId, $maxId) LIMIT 1
    UNION
    SELECT * FROM foo WHERE id > rand($minId, $maxId) LIMIT 1
    UNION
    SELECT * FROM foo WHERE id > rand($minId, $maxId) LIMIT 1
    If you have to have a semi-random results (say you don't care that they are 100% random), you can do:
    Code:
    SELECT * FROM (
      SELECT * FROM foo WHERE id > max(min((rand($minId, $maxId), $maxId - 100), $minId) LIMIT 100
    ) ORDER BY RAND LIMIT 5;
    Just test and see what works best for you.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    some good techniques in there, vali, nice job

    i would point out that the part in red here isn't actually SQL --

    SELECT * FROM foo WHERE id IN join(',',$ids)

    however, it's pretty clear what you meant

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for all answers! I realized that I don't even need random of all rows. I will just use LIMIT 500 and than do random with PHP.

    ...
    $rnd=rand(1,500);
    while($row=mysql_fetch_assoc($result)){

    if($rnd==$counter){
    echo $row[whom];
    }
    $counter++;
    }

    I hope it is not too wasteful to have 500 fetch_assocs.
    Dilster.com - Dating blog where you can find new dating tips, advices and ideas or your partner.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •