CODEWITHSUNDEEP
phpmysql
erdeepak
erdeepak

Reputation: 415

Reduce MySQL query execution time

I am using two different databases tbl_data1 (has 10000 rows) and tbl_data2 (has 2.5 million rows). Updating using the source code below takes too long. Is there any way to reduce the time taken to update the values in first database?

 $query="select * from tbl_data1";
    $result=mysqli_query($con,$query);
    while($row = mysqli_fetch_array($result,MYSQLI_ASSOC))
    {   
        $account=$row["A_Account"];
        $query1="select * from tbl_data2 where D_Account='$account'";   
        $result1=mysqli_query($con1,$query1);
        while($row1 = mysqli_fetch_array($result1,MYSQLI_ASSOC))
        {
            $product=$row1["D_Account_Type"];
            $query3="update tbl_data set A_Product='$product' where A_Account='$account'";
            $result3=mysqli_query($con,$query3);
            if (!$result3) 
            {
                die('Invalid query2: ' . mysqli_error());
            }
        }

}

Upvotes: 0

Views: 78

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30819

You can write a single update query with JOIN, e.g.:

UPDATE tbl_data td
JOIN tbl_data2 td2 ON td.A_Account = td2.D_Account_Type
JOIN tbl_data1 ON td2.D_Account = td1.A_Account
SET td.A_Product = '$product';

This means you won't need to iterate both the tables (td1 and td2) and execution will be faster.

update

If you want to set the value of td.A_Product to the value of either td1 or td2's column then you can just do:

SET td.A_Product = td1.product

OR

SET td.A_Product = td2.product

Upvotes: 2

Related Questions