List:General Discussion« Previous MessageNext Message »
From:Dan Bolser Date:April 11 2005 3:50pm
Subject:Re: SCRAMBLE(A,B) (was UDF:Request).
View as plain text  
On Mon, 11 Apr 2005, Dan Bolser wrote:

>
>Requirement: 
>
>Given two columns of a table (Column1 and Column2) of length x, return two
>columns (Column1_Scram and Column2_Scram) such that the distributions of
>values in Column1 and Column2 are preserved in Column1_Scram and
>Column2_Scram, but the pairs of values are randomized.
>
>
>Solution suggested by Shawn Green: 
>
>Create a table with two columns, and populate this table with random pairs
>of primary keys picked from the original table. Additionally, allow no
>duplicate primary keys within either column. Select x rows from this
>table, and link both primary keys (the primary key pair) back to the
>original table to get the appropriate number of randomized pairs of
>Column1 and Column2.
>
>He suggests doing the above like this (more or less):
>
>OriginalTable
>PK	A	B
>1	a	c
>2	a	d
>3	b	e
>...
>
>CREATE TEMPORARY TABLE IntermediateTable (
>  PK1  INT     NOT NULL,
>  A    CHAR(1) NOT NULL,
>  PK2  INT     NOT NULL,
>  B    CHAR(1) NOT NULL,
>  #
>  UNIQUE INDEX (PK1,A),
>  UNIQUE INDEX (PK2,B)
>);
>
>INSERT IGNORE INTO IntermediateTable
>SELECT 
>  x.PK, x.A,
>  y.PK, y.B
>FROM 
>  OriginalTable x,
>  OriginalTable y
>ORDER BY
>  RAND();
>
>SELECT 
>  x.A,
>  y.B
>FROM
>  IntermediateTable 
>INNER JOIN 
>  OriginalTable x ON (PK1 = x.PK) INNER JOIN
>  OriginalTable y ON (PK2 = y.PK)
>LIMIT 
>  the_length_of_OriginalTable;
>
>
>The problem with this solution: 
>
>Its too slow on reasonable sized tables! 


Their is also a problem with the way RAND() works...

SELECT
  x.PK, x.A,
  y.PK, y.B
FROM
  OriginalTable x,
  OriginalTable y
ORDER BY
  RAND()
LIMIT 
  1;

This takes soooooo long to pick a random row. Some cleaver 'LIMIT'
optimization could pick a results set almost instantly, instead of taking
in excess of half an hour with ~50,000 rows.




Thread
SCRAMBLE(A,B) (was UDF:Request).Dan Bolser11 Apr
  • Re: SCRAMBLE(A,B) (was UDF:Request).Dan Bolser11 Apr
    • Re: SCRAMBLE(A,B) (was UDF:Request).SGreen11 Apr
      • Re: SCRAMBLE(A,B) (was UDF:Request).Dan Bolser24 May