how to randomly sample rows from msql tables with no rowid

Question

I'm looking for a way to randomly sample for mysql tables, and was reading this article - http://jan.kneschke.de/projects/mysql/order-by-rand/

However, this doesn't work for my case because many tables in mysql don't have a "rowid"/autoincrement column. Is there still a way I can randomly sample rows in this case?

Here is the table structure:

    'CREATE TABLE `table_name` (
  `col1` date DEFAULT NULL,
  `col2` bigint(20) DEFAULT NULL,
  `col3` varchar(255) DEFAULT NULL,
  `col4` varchar(64) DEFAULT NULL,
  `col5` varchar(64) DEFAULT NULL,
  `col6` bigint(20) DEFAULT NULL,
  `col7` bigint(20) DEFAULT NULL,
  `col8` varchar(255) DEFAULT NULL,
  `col9` decimal(16,5) DEFAULT NULL,
  `col10` bigint(20) DEFAULT NULL,
  `col11` varchar(64) DEFAULT NULL,
  `col12` bigint(20) DEFAULT NULL,
  `col13` bigint(20) DEFAULT NULL,
  `col14` bigint(20) DEFAULT NULL,
  `col15` bigint(20) DEFAULT NULL
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8'

Do a select based on a "random" generated extra column in the select statement e.g. SELECT TOP 50 * FROM myTable WHERE rand() % 5 = 1 — Roger Willcocks, Sep 15 '15 at 23:06
I was actually looking at that post but I think one of the assumptions there was that there is a unique, indexed, primary key in the table, which I don't have. When I do select column1 from table order by rand() limit 10, I get the top 10 entries for that column in the table. — md1630, Sep 15 '15 at 23:33
@RogerWillcocks - Sounds like a good idea, but I'm not sure how to generate an extra column in my query, also what does rand()%5 = 1 do? — md1630, Sep 15 '15 at 23:34
Can you provide your table structure (create statement). I just tried it on a table without an auto increment column and it works. `select * from settings order by rand() limit 10;` gives a different random selection every time I run it. — David Fairbanks, Sep 16 '15 at 00:30
@DavidFairbanks - I just put up the table structure. I get the same data every time I run it. — md1630, Sep 16 '15 at 01:27
Didn't even knew this Engine: `ENGINE=BRIGHTHOUSE` Is it possible that is not randomizing it because of that? — Jorge Campos, Sep 16 '15 at 01:34
what a horrible table structure. How on earth do you plan to figure out which column is which? — e4c5, Sep 16 '15 at 02:55
The table structure nor the engine is the issue. I just created the table and populated it with data and ran the query stated before and got random results. The issue must be somewhere else in data or query that's not provided. — David Fairbanks, Sep 16 '15 at 15:22
@md1630 % is modulus, though not necessarily in MySQL (MOD(A,B), A MOD B and A % B are equivalent) , I was just dropping a comment in to get you started while checking the review queues. — Roger Willcocks, Sep 17 '15 at 01:22

Jorge Campos · Answer 1 · 2015-09-16T03:39:13.170

To generate a field into your sql do as this:

 select * 
   from (select @id:=@id+1, a.* 
           from `table_name` a,
                (select @id:=0) b
        ) x 
  order by rand() limit 10

This portion of the sql

from `table_name` a,
     (select @id:=0) b

Will create a cartesian plan between your table and the select with just one row. Then outside of it you just increment that variable @id:=@id+1. You will have your generated field.

Then wrap it as an subquery and order by rand.

See the SQLFiddle

To test it just keep hitting on the Run Sql button.

EDIT

As it did not work with the 'infobright Optimizer' try to use plain mysql functions and see what happens like this:

 select * from (
 select n id, a.* 
   from `table_name` a,
       (select RAND() n) b
  ) x order by rand() limit 5

See it here SQLFiddle 2

You will see that the random number will be the same but the col2 value will change for every hit on the "run sql" button.

Thank you, the SQLFiddle is awesome and it works on this, but when I try it on my database I get the error "The query contains syntax that is not supported by the infobright Optimizer" — md1630, Sep 16 '15 at 03:31

how to randomly sample rows from msql tables with no rowid

1 Answers1