Professional Web Applications Themes

faster/less intensive way of getting a random row from db? - MySQL

Hello I am noob about mysql so I am not sure if there exists a better command/method to solve my problem. I read online about how using rand() and the typical SELECT FROM table LIMIT rand(),1 is very slow on a big table. The current method I was using is: SELECT count(*) AS max FROM table then use PHP code to generate a number from 0 to max, call it $rand_row then SELECT FROM table LIMIT $rand_row,1 This method has been working fine, until...sigh...i had to undergo major site revision. and now I cannot just select from the table, I ...

  1. #1

    Default faster/less intensive way of getting a random row from db?

    Hello

    I am noob about mysql so I am not sure if there exists a better
    command/method to solve my problem. I read online about how using
    rand() and the typical SELECT FROM table LIMIT rand(),1 is very slow
    on a big table. The current method I was using is:

    SELECT count(*) AS max FROM table
    then use PHP code to generate a number from 0 to max, call it
    $rand_row
    then SELECT FROM table LIMIT $rand_row,1


    This method has been working fine, until...sigh...i had to undergo
    major site revision. and now I cannot just select from the table, I
    have to check fields to only select the rows that contain certain
    fields, so I do it like this:

    SELECT count(*) AS max FROM table WHERE option1 = 'y'
    then use PHP code to generate 0 to max, call it $rand_row
    then SELECT * FROM table WHERE option1 = 'y', set the results to
    $result
    then random_id = mysql_result($result, $rand_row, 'id')

    My question is, would this go crazy once my table is populated with
    1000000 rows? I am doing a SELECT * but really only want to use 1 row.
    Is there a better way?

    flagman5@gmail.com Guest

  2. #2

    Default Re: faster/less intensive way of getting a random row from db?

    SELECT * FROM my_table ORDER BY RAND() LIMIT 1; ?

    strawberry Guest

  3. #3

    Default Re: faster/less intensive way of getting a random row from db?

    On May 31, 3:10 pm, strawberry <com> wrote: 

    sir, u missed my points completely. and also I need to avoid rand()
    function as it is doented to be very slow on big tables.

    flagman5 Guest

  4. #4

    Default Re: faster/less intensive way of getting a random row from db?


    "flagman5" <com> wrote in message
    news:googlegroups.com... 
    >
    > sir, u missed my points completely. and also I need to avoid rand()
    > function as it is doented to be very slow on big tables.
    >[/ref]

    How the hell do you expect to get a random entry without using random
    numbers?

    Why not generate a list of random numbers before hand and use them if your
    concerned with the speed of rand()?


    Jon Guest

  5. #5

    Default Re: faster/less intensive way of getting a random row from db?

    com wrote: 
    it depends! what database engine are you using? for myisam, this is not
    gong to be a huge problem but for innodb, yes, it is! your biggest
    problem is this code << select count(*) as max from table where
    option1='y' >>
    also, wouldn't use max as the column alias since it is a reserved word.
    lark Guest

  6. #6

    Default Re: faster/less intensive way of getting a random row from db?

    > On May 31, 3:10 pm, strawberry <com> wrote: 
    >
    > sir, u missed my points completely. and also I need to avoid rand()
    > function as it is doented to be very slow on big tables.[/ref]

    The rand() function itself has nothing to do with tables, so it cannot
    be "slow on large tables". What is slow is sorting an entire table by
    random numbers and than just picking only one of them. Things would be
    sped up tremendously if you could just use an indexed field for the
    selection. One approach it to determine the maximum value of, for
    instance, the primary key field. Then use the rand() function to
    generate a ramdom number in the range covered by the existing key
    values. If the generated key value does not exist, try again. If your
    table does not have many "holes", this might be a fast option. You could
    write a stored procedure to further minimize the queries.

    Best regards,
    --
    Willem Bogaerts

    Application smith
    Kratz B.V.
    http://www.kratz.nl/
    Willem Guest

Similar Threads

  1. intensive Course
    By Tsui Lai Yi in forum Adobe Acrobat Macintosh
    Replies: 2
    Last Post: May 29th, 09:31 AM
  2. DataSet too Memory Intensive!
    By ziondreams in forum ASP.NET Web Services
    Replies: 10
    Last Post: December 1st, 11:34 PM
  3. How resource-intensive are these?
    By Brent in forum PHP Development
    Replies: 1
    Last Post: February 28th, 01:42 PM
  4. DuplicateMovieClip goes faster and faster
    By sylvie in forum Macromedia Flash
    Replies: 0
    Last Post: September 30th, 06:11 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139