Re^2: how to speed up querys to mysql

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;
              }
[download]

Comment on Re^2: how to speed up querys to mysql Download Code

Replies are listed 'Best First'.
Re^3: how to speed up querys to mysql by Melly (Chaplain) on Oct 19, 2006 at 14:07 UTC
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). Tom Melly, tom@tomandlu.co.uk	[reply]
Re^4: how to speed up querys to mysql by rycagaa (Initiate) on Oct 19, 2006 at 18:41 UTC
thx for your answers. I need all results where $row == $row1, so "LAST" method dont helps. also the @c1 and @c2 arrays comes not from mysql queries but from files , which i parsed before. and of coursed my script checks for the same queries.	[reply]
Re^5: how to speed up querys to mysql by Melly (Chaplain) on Oct 19, 2006 at 20:11 UTC
Then have you thought about using an "in" structure - mssql at least is suprisingly efficient, even when given huge lists - e.g. (cut-down, and untested) `my $list = join ",", @a1; foreach(@b1){ $sql = "select x from y where v = $_ and z in($list)"; print $sql . "\n"; }` [download] Tom Melly, tom@tomandlu.co.uk	[reply] [d/l]
Re^3: how to speed up querys to mysql by ptum (Priest) on Oct 19, 2006 at 13:51 UTC
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 }` [download] 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.	[reply] [d/l]