471,050 Members | 1,433 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,050 software developers and data experts.

Efficient generation of random data subsets


I have a database table that contains a set of numbers (set X). I need to be
able to generate/export a random set of survey numbers (set C) from set A
with IDs
that aren't in set B. Where set A is a subset of numbers from set X that
match the filters for the survey, and set B contains the set of number IDs
for numbers that have already been exported for the survey.

The idea is to repeatedly generate a random sample of non-duplicate numbers
from set X filtering on the survey templates.

My database comprises of the following tables:

Table Numbers
ID INT PK
Number VARCHAR

Table Templates
ID INT PK
Template VARCHAR

Table Survey
ID INT PK
Name VARCHAR
Description VARCHAR
Started DATETIME

Table Filters
ID INT PK
SurveyID INT FK (Survey:ID)
TemplateID INT FK (Templates:ID)

Table NumberExportedSurveyJoin
ID INT PK
NumberID INT FK (Numbers:ID)
SurveyID INT FK (Survey:ID)
Exported DATETIME
This is the SQL query that I'm using to generate set C (the random set of
numbers) :

SELECT * FROM Numbers WHERE Number LIKE 'filter1%' OR Number LIKE 'filter2%'
AND Numbers.ID NOT IN (SELECT Numbers.ID FROM Numbers LEFT JOIN
NumberExportedSurveyJoin ON Numbers.ID = NumberExportedSurveyJoin.NumberID
WHERE NumberExportedSurveyJoin.SurveyID = S) ORDER BY RAND() LIMIT N;
The problem is that MySQL takes a long time to generate set C even for
moderate sizes of set A and set B. As the number of entries in table
NumberExportedSurveyJoin (set B) increases so does the time to generate the
next
random set of numbers. Is there a better/more efficient approach to
generating set C ?

Thanks,

Tony


Jul 20 '05 #1
0 1367

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Maurice LING | last post: by
10 posts views Thread by Ioannis Vranos | last post: by
13 posts views Thread by Roy Gourgi | last post: by
4 posts views Thread by Dimos | last post: by
22 posts views Thread by gagan.singh.arora | last post: by
4 posts views Thread by Patrick | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.