Professional Web Applications Themes

Large number of random rows - MySQL

Hello, I have a database of about 130 million records. I need to be able to retrieve a random subset of those records (say random 100,000 records). I tried the simplest SELECT * FROM Table ORDER BY RAND() type of solutions but that is prohibitively slow with the database of this size. Are there any other solutions available that could help me with this task? If it's of any help the front end is PHP. Thanks, Marek...

  1. #1

    Default Large number of random rows

    Hello,

    I have a database of about 130 million records. I need to be able to
    retrieve a random subset of those records (say random 100,000
    records).

    I tried the simplest SELECT * FROM Table ORDER BY RAND() type of
    solutions but that is prohibitively slow with the database of this
    size. Are there any other solutions available that could help me with
    this task? If it's of any help the front end is PHP.

    Thanks,

    Marek

    mkarbarz@gmail.com Guest

  2. #2

    Default Re: Large number of random rows

    "com" <com> wrote in
    news:googlegroups.com:
     

    Can you give some more info on the table structure?

    --
    felix
    Felix Guest

  3. #3

    Default Re: Large number of random rows

    On 5 Feb, 09:28, Felix Geerinckx <com> wrote: 
    >
    > Can you give some more info on the table structure?
    >
    > --
    > felix[/ref]

    And just how slow IS 'prohibitively'?

    strawberry Guest

  4. #4

    Default Re: Large number of random rows

    On Feb 5, 6:45 am, "strawberry" <com> wrote: 
    > [/ref]


    >
    > And just how slow IS 'prohibitively'?[/ref]

    Table structure is very simple - a single table with about 20 columns
    in it (the database has already been normalized and there is no
    logical way to split it into multiple tables). Columns are either
    numeric or bit values. There are maybe 2 or 3 that are varchars but
    never longer than 10 characters.

    As for what is "prohibitively" slow, for a query where about 1.5
    million possible rows are returned (after the WHEREs are considered)
    it takes in excess of 15 minutes to return 100,000 rows even if I only
    try to return a single bit column.

    mkarbarz@gmail.com Guest

  5. #5

    Default Re: Large number of random rows

    "com" <com> wrote in
    news:googlegroups.com:

     

    What is the primary key? Can you randomize on that?

    --
    felix
    Felix Guest

  6. #6

    Default Re: Large number of random rows

    On 5 Feb, 14:04, "com" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > Table structure is very simple - a single table with about 20 columns
    > in it (the database has already been normalized and there is no
    > logical way to split it into multiple tables). Columns are either
    > numeric or bit values. There are maybe 2 or 3 that are varchars but
    > never longer than 10 characters.
    >
    > As for what is "prohibitively" slow, for a query where about 1.5
    > million possible rows are returned (after the WHEREs are considered)
    > it takes in excess of 15 minutes to return 100,000 rows even if I only
    > try to return a single bit column.[/ref]

    Does MySQL actually say something like '100000 total, Query took 900
    sec'?

    strawberry Guest

Similar Threads

  1. Replies: 8
    Last Post: March 16th, 09:20 AM
  2. Random Number
    By kt03 in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: June 4th, 02:51 AM
  3. random number between x and y
    By Dan in forum PERL Beginners
    Replies: 4
    Last Post: September 20th, 10:28 PM
  4. #22597 [Com]: PHP can't select random rows from MySql
    By justin at visunet dot ie in forum PHP Development
    Replies: 0
    Last Post: July 29th, 03:57 PM
  5. Random number for ASP
    By Aaron Bertrand - MVP in forum ASP
    Replies: 0
    Last Post: July 21st, 02:29 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