De-duplicate 'smart' random row query?

Dev Shed Forums Sponsor:

March 11th, 2013, 05:03 PM

ETbo

Registered User

Join Date: Mar 2013

Posts: 3

Time spent in forums: 25 m 6 sec
Reputation Power: 0

De-duplicate 'smart' random row query?

Hi guys,

following some research, I found a query suitable for my needs: it randomly returns IDs from the table. The ID field is an auto-increment, so there are no holes.

Code:

SELECT `mydb`.`myTable`.id
FROM   (SELECT Floor (Rand() * (SELECT Count(*) 
								FROM   `mydb`.`myTable`)) num, 
			   @num := @num + 1 
		FROM   (SELECT @num := 0) a, 
			   `mydb`.`myTable` 
		LIMIT  2000000) b, 
	   `mydb`.`myTable` 
WHERE  b.num = `mydb`.`myTable`.id

The issue I experience is that the target table (myTable) contains 30-400M records, depending on the situation. In the LIMIT, I want to retrieve 2M randomly selected IDs, however I get a lot of duplicates (which is expected).

Is it possible to de-duplicate the query and yet receive 2M records? I thought to create a table and let it manage the UNIQUE values, but again I will get less than expected.

Any thoughts? Many thanks!

March 13th, 2013, 05:33 AM

Problem Solver

Join Date: Jan 2001

Location: Stockholm, Sweden

Posts: 4,430

Time spent in forums: 3 Weeks 4 Days 7 h 26 m 18 sec
Reputation Power: 532

If you want to do it this fast way by first selecting id's and then run a distinct on them the only suggestion I can give is to use two LIMIT.
One for the inner SELECT with a slightly higher LIMIT to compensate for the "loss" of duplicates (depending on how many duplicates you usually have in the subset, which is entirely dependent on your data.

And then the outer LIMIT part of the SELECT DISCTINCT chooses the exact amount of rows you want returned.

__________________
/Stefan

Viewing: Dev Shed Forums > Databases > MySQL Help > De-duplicate 'smart' random row query?

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump