Most efficient way of pulling a random row from a MySQL Database? (Not RAND())

Discussion in 'MySQL' started by Nathan Malone, Jun 22, 2006.

0
  1. #1
    What would you all here do to select a random row out of a MySQL table? I have about 5,000 records in one table that I need to select a random row out of, and "ORDER BY RAND()" works alright. However, for another project, I have about 2,000,000 records in a table, and RAND() simply wouldn't work because it is so slow for a table that size.

    Any suggestions on how to efficiently extract a random row out of the database?
     
    Nathan Malone, Jun 22, 2006 IP
  2. fsmedia

    fsmedia Prominent Member

    Messages:
    5,163
    Likes Received:
    262
    Best Answers:
    0
    Trophy Points:
    390
    #2
    fsmedia, Jun 22, 2006 IP
  3. Nathan Malone

    Nathan Malone Well-Known Member

    Messages:
    369
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    110
    #3
    Thanks! I'll try that code tomorrow morning.
     
    Nathan Malone, Jun 22, 2006 IP
  4. Owen

    Owen Peon

    Messages:
    10
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Please note that the technique mentioned above will only work accurately if your data has no holes in it. I.e. 1,2,3,4,5,6,7,8,9,10. If it goes 1,2,3,4,5,30,31,32,33 then the second half will be much more likely to be selected. However, this (from the comments) would work:
    Where $x is a number you select in PHP between 0 and the number of rows.

    Owen
     
    Owen, Jun 22, 2006 IP