how to speed up querys to mysql

[rycagaa]

Helo, i have one problem, i have two arrays and want to check its values in mysql databases. i wrote this script, but when here is 30000 queries and more it works very slow. each time it looks to mysql and gets results. how to speed up this proccess. Maybe need to write mysql trigger or here is another way?

 
foreach $a1(@a1) {
        foreach $b1(@b1)
        {
            $sth = $dbh->prepare( "SELECT a1.ID FROM a1, a1_b1, b1  WH
+ERE b1.id = '$c1                                    AND....);        
+                                           
             $sth->execute;
             my $row = $sth->fetchrow(); 
            $sth = $dbh->prepare( "SELECT a1.id  FROM a1, a1_b1, b1 WH
+ERE b1.id = '$c2 AND.....);                                          
+                                              
             $sth->execute;                                           
+       
             my $row1 = $sth->fetchrow( );
            if ($row1 = $row2)
              {
              push @row, $row1;
              }

[reneeb]

You should prepare the statements before you iterate through the loops and use the ?-notation:



my $statement = 'SELECT a1.ID FROM a1, a1_b1, b1 WHERE b1.id = ? AND a
+1 = ?';
my $sth = $dbh->prepare($statement) or die $dbh->errstr();
for my $a1(@array1){
    for my $b1 (@array2){
        $sth->execute($b1,$a1) or die $dbh->errstr();
        # ... other stuff ...
    }
}

[Samy_rio]

Hi rycagaa, try to use single query like this,

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect( "DBI:mysql:project") or die "Can't connect to 
+Oracle database: $DBI::errstr\n";
my $sth = $dbh->prepare("SELECT test.id, test1.id FROM test, test1 whe
+re test.value=? and test1.value=?");

$sth->execute('friend', 'india') || $sth->errstr();

while (my ($row, $row1) = $sth->fetchrow_array) { 
 print "$row\t$row1\n";
}

$dbh->disconnect;

if ($row1 = $row2)

And also use '==' for numeric compare.

[erix]

You don't say anything about indexes.

Make sure you have indexes on the (main) used equals clauses.

[rycagaa]

uhh sry the code was wrong, arrays elements are variables which i search for in database. so i dont know how to add all it to one query... the right code is:

 
foreach $c1(@c1) {
        foreach $c2(@c2)
        {
            $sth = $dbh->prepare( "SELECT a1.id FROM a1, a1_b1, b1  WH
+ERE b1.id = '$c1'AND a1_b1.a1_id=a1.id AND a1_b1.b1_id=b1.id );      
+                                                 
             $sth->execute;
             my $row = $sth->fetchrow(); 
            $sth = $dbh->prepare( "SELECT a1.id  FROM a1, a1_b1, b1 WH
+ERE b1.id = '$c2' AND a1_b1.a1_id=a1.id AND a1_b1.b1_id=b1.id );     
+                                                                     
+              
             $sth->execute;                                           
+       
             my $row1 = $sth->fetchrow( );
            if ($row1 == $row2)
              {
              push @row, $row1;
              }

[Melly]

Hmm, this is starting to look like more of a sql question than a perl question... one initial suggestion, do you expect more than one match per pair? If not, a LAST could be used to break out of the loop once you find a match, which might save some time.. oh, and your code should say "$row == $row1" IMHO...

Aside from that, a little more detail would help - for instance, where do you get the values for @c1 and @c2 from? Can you guarantee that no value in @c1 is the same as a value in @c2? If not, you are always going to get at least one match - where the two queries return the same row.

The main reason I ask is because if @c1 and @c2 are populated from the results of another query on the same db, you might want to rethink the initial query, or maybe using the key field for the tables to pull all rows where a1.id = a1.id but the keyfield <> the keyfield, and then working on the result-set in pure-perl (depending on the size of the returned data).

[ptum]

No, look again at the answers you've been given above. You want to prepare the SQL statement outside your two foreach blocks, and use bind parameters to substitute the $c1 and $c2 variables on the fly:

use strict;
my $sth = $dbh->prepare("SELECT blah from blah1 where blah1.field = ?"
+);
foreach my $c1(@c1) {
  $sth->execute($c1);
  # do stuff
}

Your SQL statements are identical, so if (for some reason) you want to spin through two levels of bind parameters, you still only need to prepare the statement once outside the loop.

[stonecolddevin]

The suggestions given are excellent, so try those out. Also, check out Speeding up the DBI. In the future, try searching the site. Most the time a simple search on this site will provide quite fruitful results.

[blazar]

Helo, i have one problem, i have two arrays and want to check its values in mysql databases. i wrote this script, but when here is 30000 queries
[SNIP]

foreach $a1(@a1) {
        foreach $b1(@b1)
        {
            $sth = $dbh->prepare( "SELECT a1.ID FROM a1, a1_b1, b1  WH

In addition to the excellent answers you got thus far, I have a pair of remarks that nobody seems to have done yet:

1. this is not "your script", but a portion of it. So far so fine, but to help people who will read your post it is generally recommended to build a minimal but complete example still exhibiting the problem (often in the course of doing so one actually solves the problem, but I doubt this could have been the case here);
2. you may have noticed that at the top of many people's example code they inserted the following two lines:

   use strict;
   use warnings;
   

   I recommed you to do the same. They instruct perl to restrict your freedom in a way that will help you to avoid common programming mistakes, hence implicitly to give you all the help it can. Of course you will then have to declare your (lexical) variables with my.