#method 1 - Perl intersection using @col arrays $sql = "SELECT col_2 FROM theTable WHERE col_1 = ?"; $sth = $dbh->prepare($sql) or die("Could not prepare!" . $dbh->errstr); $start_1 = new Benchmark; for $i (0 .. $iterations) { $sth->execute($value_1) or die("Could not execute 1!" . $dbh->errstr); while ($data = $sth->fetchrow_array()) { push(@col_1, $data); } $sth->execute($value_2) or die("Could not execute 2!" . $dbh->errstr); while ($data = $sth->fetchrow_array()) { push(@col_2, $data); } foreach $e (@col_1) { $union{$e} = 1 } foreach $e (@col_2) { if ( $union{$e} ) { $isect{$e} = 1 } } @isect = keys %isect; } $end_1 = new Benchmark; $diff = timediff($end_1, $start_1); #method 2 - Perl intersection using 'undef' $sql = "SELECT col_2 FROM theTable WHERE col_1 = ?"; $sth = $dbh->prepare($sql) or die("Could not prepare!" . $dbh->errstr); $start_2 = new Benchmark; for $i (0 .. $iterations) { $sth->execute($value_1) or die("Could not execute 1!" . $dbh->errstr); while ($data = $sth->fetchrow_array()) { $union{$data} = undef; } $sth->execute($value_2) or die("Could not execute 2!" . $dbh->errstr); while ($data = $sth->fetchrow_array()) { $isect{$data} = undef if exists $union{$data}; } @isect = keys %isect; } $end_2 = new Benchmark; $diff = timediff($end_2, $start_2); #method 3 - MYSQL $sql = "SELECT COUNT(*) FROM theTable A JOIN responses B ON (A.col_2 = B.col_2) WHERE A.col_1 = ? AND B.col_1 = ?"; $sth = $dbh->prepare($sql) or die("Could not prepare!" . $dbh->errstr); $start_3 = new Benchmark; for $i (0 .. $iterations) { $sth->execute($value_1, $value_2) or die("Could not execute 1!" . $dbh->errstr); } $end_3 = new Benchmark; $diff = timediff($end_3, $start_3);