SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jun 2003
    Location
    NYC
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MYSQL RAND() is too slow

    I have a Postal Mailing List of 3 Million names. How can I randomize the selection of 1 million names? It takes over 1:30 minutes for just 200 names.

  2. #2
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is your current query?

    What version of MySQL?

    MySQL 3 had some issues with rand... http://www.sitepointforums.com/showt...threadid=98872
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  3. #3
    Hi there! Owen's Avatar
    Join Date
    Jan 2000
    Location
    CA
    Posts
    1,165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, it's ONLY because rand() puts puts the data into a temporary table in memory in order to sort on the new column... right? (Check it out using EXPLAIN.)How else are they going to be able to sort all the names--you gotta set a random number for all 1 million then go back and sort them, and it needs to be stored somewhere. There's no other way to do it. What you can do is this (it'll should help a little): add a new column called randnumb, make it an index, then do an update to set each equal to a new random number then select from it. Or alternatively more RAM and faster temp disk.

    Edit: it shouldn't take 1:30 for 200 names... most likely something is wrong with the mySQL installation.

    Owen


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
  •