PHP - Shorten time to run query for tables from different servers

Question

I have 2 tables with different databases and servers. Table 1 in Server 1 holds the destination and invoice no columns, and Table 2 in Server 2 holds Invoice No and Total columns.

I need to sum up total of all destination. The only matching value of both tables is invoice no, where WS10001 = 10001. The method I'm doing it, is select all values from Server 1 and Table 1, using foreach to loop the values and run the query in Table 2 in Server 2 to sum up the total.

$sql ="select * from table1 group by destination";
$result=mysql_query($sql) or die(mysql_error());

while($myrow=MySQL_fetch_array($result,MYSQL_ASSOC))
{
    extract($myrow);
    $invno=$myrow[2];

    sql2 = "select total from table2 where invoice_no like '%$invno%'";
    $result2 = mysqli_query($conn, $sql);

    while ($row=mysqli_fetch_row($result2)) {
        $value = $row[0];
    }   

    if (destination == "Washington")
    { $wstotal += $value;}
    else if (destination == "Hawaii")
    { $wstotal += $value;}
    else if (destination == "Budapest")
    { $wstotal += $value;}
}

This method works pretty quick when I use a sample table to run it with 100 records, however when i apply it to the actual tables which has more than 10000 records, it runs for hours to complete it because it has to run a query for every foreach loop.
Is there any ways that can shorten the time to complete it, or can I save down all values from both tables, sum up in php ?

you could also run a loop to extract all the invoice numbers. Then run a single query to extract all the data in one go. Opening connection is a quite time-expensive operation and in your case a come complex select would give you a speed improvement. — Simas Joneliunas, Jul 13 '18 at 08:15
Check [this](https://stackoverflow.com/questions/810349/mysql-cross-server-select-query/1546339#1546339). This may help to join cross `mysql` server. — Jaydeep Mor, Jul 13 '18 at 08:15

Martijn · Answer 1 · 2018-07-13T08:23:55.007

0

You can use GROUP BY and a SUM, to get a sum per destination:

SELECT SUM(total) as total 
FROM table2 
WHERE destination IN ('Washington','Hawaii','Budapest') 
      AND invoice_no LIKE '%$invno%'
GROUP BY destination

This way, the DB doesnt need to remember the whole dataset, just the sum grouped, which in this case is max 3 results (this is not 100% accurate, but enough for now).

You also have a IN() which reduces the dataset further, IN() is a whole lot faster than LIKE.

If you dont have an index on 'destination' (or another column which have various different values which you use a lot in queries): Add a index on it. This'll improve the lookup times.

edited Jul 13 '18 at 08:23

answered Jul 13 '18 at 08:14

Martijn

15,791
4
36
68

i need to do a loop because if have thousand records of $invno – hatched Jul 13 '18 at 08:18
It groups the result by destination, so internally it goes through all records, and in the end you get the totals, group, with a count. – Martijn Jul 13 '18 at 08:24

score 0 · Answer 2 · answered Jul 13 '18 at 08:25

Maybe just use 2 query !

$sql ="select * from table1 group by destination";
$result=mysql_query($sql) or die(mysql_error());

// Create an empty query
$sql = "";

while($myrow=MySQL_fetch_array($result,MYSQL_ASSOC))
{
    extract($myrow);
    $invno=$myrow[2];

    // Now you add each query to you "global" query : don't forget the ';' at the end
    sql2 .= "select total from table2 where invoice_no like '%$invno%';";
}

Now you just run this query ONCE using mysqli_multi_query (the documentation) and get each result using mysqli_next_result (the documentation).

I'm sorry can't write the code I use PDO, but the logic is :

1/ You create a "big query" with all your subquery

2/ You execute this "big query"

3/ You fetch all the result and do your "math" as you did before

4/ Then you test if there is a next_result

5/ You fetch the next set of result and do your "math"

6/ Repeat step 4 and 5 untill there is no more next_resut

Try it but it should be better than have one query executed for each loop !

Hope it's clear enough?

PHP - Shorten time to run query for tables from different servers

2 Answers2