select <field> from <table> order by rand() limit <int>;

I have a problem that when I use this in the shell I appear to get an always random listing of fields, but when run on the browser in a php script I almost always get the field of the first record in that table.

Currently I'm limiting the search to 6, and, like I mentioned above, I'm getting the field of the first record, and almost always as the first listing when run in the browser. Is there a patch to stop that from happening, and to make it more random?

    You could try seeding the random number generator with [man]srand[/man]

      Originally posted by barand
      You could try seeding the random number generator with [man]srand[/man]

      that rand() is not a php function, but a mysql function, which is well known for it's non-random (but very fast, as per MySQL's focus) behaviour.

      Better way, if the table isn't really big, is to do a

      select count() from table;
      $somevar=countfromabove;
      seed with srand
      $offset = random(0,$somevar)
      select       from table limit $offset, 1;

        Originally posted by Sxooter
        that rand() is not a php function, but a mysql function, which is well known for it's non-random (but very fast, as per MySQL's focus) behaviour.

        Better way, if the table isn't really big, is to do a

        select count() from table;
        $somevar=countfromabove;
        seed with srand
        $offset = random(0,$somevar)
        select         from table limit $offset, 1;

        What if the table is on the big side? 😉

          If the table is on the big side you want to avoid using count(*) as it is a sequential scan in most databases.

          In a real database you can create a seperate table to hold the count value and create a trigger that updates that value on inserts/deletes and such, then select from there.

          On the other hand, using the rand() function in a sql query on a large table is also very slow, so it might still be much faster to run a select count(*) query first anyway.

          On Postgresql, due to its MVCC nature, count(*) is as slow as any other flavor of aggregate, i.e. not optimized for the simple case with no where clause.

          If the rows are <10,000 a select count(*) should be plenty fast by itself.

            Write a Reply...