Re: how to speedup mysql queries?

[Rex Edwin Robards]

You're spending too much time "preparing" SQL. You show two SQL queries, but they are essentially the same statement. Try this:


$sth = $dbh->prepare( "SELECT a.id FROM a, a_b, b WHERE a.id=? AND ...")
or die "Can't prepare SQL statement: $DBI::errstr\n";


foreach $c1(@c1) {
foreach $c2(@c2) {
$sth->execute($c1);
$row = $sth->fetchrow();
$sth->execute($c2);
$row1 = $sth->fetchrow();
if ($row == $row1) {
push @row3, $row
}
}
}
$sth->finish();

If your two SQL queries are really significantly different, you should still move the preparation outside of the loops - "prepare" is an expensive call and should be repeated as infrequently as possible. If you need two queries (i.e. the WHERE clauses of the two statements are really different), try this:


$sth1 = $dbh->prepare( "SELECT a.id FROM a, a_b, b WHERE a.id=? AND ...")
or die "Can't prepare SQL statement: $DBI::errstr\n";
$sth2 = $dbh->prepare( "SELECT a.id FROM a, a_b, b WHERE a.id=? AND ...")
or die "Can't prepare SQL statement: $DBI::errstr\n";

foreach $c1(@c1) {
foreach $c2(@c2) {
$sth1->execute($c1);
$row = $sth1->fetchrow();
$sth2->execute($c2);
$row1 = $sth2->fetchrow();
if ($row == $row1) {
push @row3, $row
}
}
}
$sth1->finish();
$sth2->finish();

Of course, you should probably choose some more meaningful name for your statement handles.



Edited 1 time(s). Last edit at 10/19/2006 11:16AM by Rex Edwin Robards.