# Improve a query to select random rows, but without repeating records

**URL:** <https://forums.percona.com/t/improve-a-query-to-select-random-rows-but-without-repeating-records/724>\
**Category:** Other MySQL® Questions\
**Created:** [April 20, 2008, 2:50am UTC](https://forums.percona.com/t/improve-a-query-to-select-random-rows-but-without-repeating-records/724 "2008-04-20T02:50:50Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![santisaez](https://avatars.discourse-cdn.com/v4/letter/s/7ea924/32.png) [@santisaez](https://forums.percona.com/u/santisaez)\
**Post date:** [April 20, 2008, 2:50am UTC](https://forums.percona.com/t/improve-a-query-to-select-random-rows-but-without-repeating-records/724/1 "2008-04-20T02:50:50Z")

</div>

Dear Srs,

I have a table with near 1.000.000 rows, and I want to select random rows, but without repeating records.

If I don’t prevent repeating records MySQL works fine, this is a PHP code fragment:

$result = mysql\_query(“SELECT MIN(id) AS min , MAX(id) AS max FROM tabla”);$rango = mysql\_fetch\_object($result);$rand\_id = mt\_rand($rango-\>min, $rango-\>max);$result = mysql\_query(“SELECT \* FROM tabla WHERE id \>= $rand\_id LIMIT 0,1”);

This is soo fast, MySQL rocks 😉

For preventing repeating rows, I’m making this:

1. There’s another column to mark if a record was selected previosly or not

2. Select rows not market at selected

3. Get the record and mark it as selected

4. Go to 1)

The step 2) has a WHERE statement that slows de query to more than 10 seconds…

This is the table definition:

mysql\> SHOW CREATE TABLE ip\_assigned \G\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* Table: ip\_assignedCreate Table: CREATE TABLE `ip_assigned` ( `id` int(11) NOT NULL auto\_increment, `ip` varchar(256) NOT NULL, `assigned_time` timestamp NOT NULL default ‘0000-00-00 00:00:00’, `crawler` int(11) NOT NULL, PRIMARY KEY USING BTREE (`id`), KEY `index_ip` (`ip`(16)), KEY `index_crawler` (`crawler`,`ip`)) ENGINE=MyISAM AUTO\_INCREMENT=871539 DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql\>

This is the query to get not market records:

// Get COUNT(\*) FROM cache, cache-\>total\_ips$sql = “SELECT val AS total\_ips FROM cache WHERE param = ‘total\_ips’”;$result = mysql\_query($sql);$cache = mysql\_fetch\_object($result);// Calculate a random offset for the LIMIT$offset = mt\_rand(1, $cache-\>total\_ips);// Get $total\_requested\_ips random IPs$sql = “SELECT ip FROM ip\_assigned WHERE crawler = 0 LIMIT $offset,$total\_requested\_ips”;$result = mysql\_query($sql);

This last query is very slow, because MySQL needs to scan a lot of rows, this is the output of EXPLAIN command:

mysql\> DESCRIBE SELECT ip FROM ip\_assigned WHERE crawler = 0 LIMIT 705607,10240 ;±—±------------±------------±-----±--------------±--------------±--------±------±-------±------------+| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |±—±------------±------------±-----±--------------±--------------±--------±------±-------±------------+| 1 | SIMPLE | ip\_assigned | ref | index\_crawler | index\_crawler | 4 | const | 813308 | Using index | ±—±------------±------------±-----±--------------±--------------±--------±------±-------±------------+1 row in set (0.00 sec)

What can I do to improve this query? perhaps another method/idea to prevent repeating random registers? Thanks!

Regards,

---

<div class="post-metadata">

**Author:** ![santisaez](https://avatars.discourse-cdn.com/v4/letter/s/7ea924/32.png) [@santisaez](https://forums.percona.com/u/santisaez)\
**Post date:** [April 20, 2008, 2:58am UTC](https://forums.percona.com/t/improve-a-query-to-select-random-rows-but-without-repeating-records/724/2 "2008-04-20T02:58:28Z")

</div>

Dear Srs,

This is the output for mysqldumpslow:

~# mysqldumpslow /var/log/mysql/mysql-slow.logReading mysql slow query log from /var/log/mysql/mysql-slow.logCount: 3 Time=2.00s (6s) Lock=0.00s (0s) Rows=10240.0 (30720), root[root]@localhost SELECT ip FROM ip\_assigned WHERE crawler = N LIMIT N,N

Regards,

---

<div class="post-metadata">

**Author:** ![barryhunter](https://avatars.discourse-cdn.com/v4/letter/b/6a8cbe/32.png) [@barryhunter](https://forums.percona.com/u/barryhunter)\
**Post date:** [April 24, 2008, 9:49am UTC](https://forums.percona.com/t/improve-a-query-to-select-random-rows-but-without-repeating-records/724/3 "2008-04-24T09:49:01Z")

</div>

I was comming here looking for how to do the same (effiecnty get random rows), your idea looks very promising, as for avoiding duplicates, how about just keeping track of the ‘done’ in php?

$result = mysql\_query(“SELECT MIN(id) AS min , MAX(id) AS max FROM tabla”);$rango = mysql\_fetch\_object($result);$done = array(0);for($i=0;$i\<10;$i++) { $rand\_id = mt\_rand($rango-\>min, $rango-\>max); $result = mysql\_query(“SELECT \* FROM tabla WHERE id \>= $rand\_id AND id NOT IN (”.implode(‘,’,$done).“) LIMIT 0,1”); $resobj = mysql\_fetch\_object($result); //do something with the object… $done = $resobj-\>id;}

(note the 0 in the definition of the array - that is so there is something - that will never match - you could do it by making IN clause only appear if something (amongst other ways) - but I like this simple way - should not add to the query time?)

– note this is untested - about to go and try it!

(edited as already spotted a mistake!)

---

<div class="post-metadata">

**Author:** ![rmatakajr](https://avatars.discourse-cdn.com/v4/letter/r/13edae/32.png) [@rmatakajr](https://forums.percona.com/u/rmatakajr)\
**Post date:** [June 5, 2008, 4:24pm UTC](https://forums.percona.com/t/improve-a-query-to-select-random-rows-but-without-repeating-records/724/4 "2008-06-05T16:24:17Z")

</div>

Hey,

I too was faced with the same problem with over 17,000 rows,  
i needed to return a rand() without duplicates but decided to use PHP for rand() when i got the array i needed.

So I ended up using limit clauses to grab the result set  
then I used NOT IN condition to avoid duplicates

$feed\_array=$dbc-\>query(“select m.market\_feed\_id from market\_feed m WHERE m.market\_feed\_id not in(select p.processed\_id from que\_processed p) LIMIT 1000”);if($dbc-\>db\_num\_rows($feed\_array) \>0){ $count=0; $max\_que=1000; while($Mvalues=$dbc-\>db\_fetch\_array($feed\_array)) { $count++; // Insert into ads processed $insert\_array=array(‘processed\_id’=\>$Mvalues[‘market\_feed\_id’], ‘processed\_type’=\>‘market’); $dbc-\>db\_insert(‘que\_processed’,$insert\_array); if($count == $max\_que) { break; } }}else{ echo ‘NO MORE IDS TO GRAB!!!  
  
’;}

I have 2 tables

1. Where the data exists
2. A holding table (que\_processed)

As you can see I have selected only the id’s  
not in(que\_processed)

So now i have 1000 fresh IDs to work with in an  
array, to do all types of nasty crap too!

You should use a db\_num\_rows() \> 0 to check if the  
above query has processed all the id’s have been  
processed are not

Preformance wise?? Not sure yet, but the code SNAPS  
very fast, mabee its because im limiting 1000 at a time?

Have Fun  
Rick  
[http://www.americashostingnetwork.com](http://www.americashostingnetwork.com)