Re: how to speedup mysql queries?
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.
Subject
Written By
Posted
Re: how to speedup mysql queries?
October 19, 2006 10:57AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.