How to get multiple random rows in MySQL without id / auto_increment column?

Question

My goal

I am trying to get some random rows that contain only unique userid , but for a type column - random - type can only be 0 or 1 . The table under consideration will indicate less than 1000 rows at any given time.

My table

 CREATE TABLE tbl_message_queue ( userid bigint(20) NOT NULL, messageid varchar(20) NOT NULL, `type` int(1) NOT NULL, PRIMARY KEY (userid,messageid,`type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Data examples

 userid | messageid | type --------------------------------------------------- 4353453 | 518423942 | 0 4353453 | 518423942 | 1 2342934 | 748475435 | 0 2342934 | 748475435 | 1 7657529 | 821516543 | 0 7657529 | 821516543 | 1 0823546 | 932843285 | 0 0823546 | 932843285 | 1 

What to exclude

Using ORDER BY RAND() impractical, because at least 18,000 of these types of queries are executed by applications at any time and cause a high load. Using SELECT DISTINCT or GROUP BY (obviously) more efficient and will always select a unique userid , but type will always be 0 with an acceptable load.

A common method is to create an id column, but I'm looking for an alternative way only . The primary key of the group cannot be changed as needed and is deeply integrated into our application, however, the structure of each column can be changed.

Thanks.

Answer 1

My understanding of your question is that for each userid you have two entries, but you want to extract only one random case.

To achieve this, you must create a random value from 0 to 1 for each unique userid , and then CONNECT this list with the start list:

 SELECT a.* FROM tbl_message_queue AS a JOIN ( SELECT userid, FLOOR(2*RAND()) AS type FROM tbl_message_queue GROUP BY userid ) AS b ON ( a.userid = b.userid AND a.type = b.type ); 

But if ORDER BY RAND() does not work for you, perhaps we should compromise.

In the above sequence, any two user identifiers will be uncorrelated - that is, the fact that user A gets type 0 says nothing about what user B will be added to.

Depending on the use case, a less random (but "seemingly random") sequence can be obtained by two queries:

 SELECT @X := FLOOR(2*RAND()), @Y := POW(2,FLOOR(2+14*RAND()))-1; SELECT * FROM tbl_message_queue WHERE (((userid % @Y) & 1) XOR type XOR @X); 

That way, you can get what seems like random extraction. What really happens is that the user IDs are correlated, and you only have a few dozen different extractions. But, using only simple operators and without JOIN, this query is very fast.