|
|
Posted by Brian on 10/04/07 22:21
Hi there
First off sorry if this is a long posting but I need to give all the facts
in the hope to get an answer. 2 Years ago I did a site for a client to track
online orders and run reports. One of the main reports is now going so slow
it's of no use to anybody.
I am the first to admit that I am not the best PHP and MySQL coder so I may
be missing something here or I may have just met the limit of MySQL, all
scripts and DBs are on a modem new dedicated server.
The 2 main tables are "orders" and "productslist" The orders table has
7000000 (yes that is 6 million) records and the productslist has 6000, all
search fields have been indexed, the whole DB have 25 tables
The users select the search criteria, summits the form and the below PHP
code it run , it can take upwards of 20 mins to return the results. So my
question is, is there anything wrong with my code or the way I and running
this, or have I just hit the limit of MySQL
Many thanks
Brian
$query = "SELECT p.prod_code, p.description, SUM(o.`ord` ) AS totalor,
SUM(o.`del` ) AS totaldel, SUM(o.`total_cost_price`) AS totalcostprice,
SUM(o.`total_invoice_price`) AS totalinvoiceprice FROM productslist AS p
LEFT OUTER JOIN orders AS o ON ( p.prod_code = o.prod_code AND o.timestamp
>= $sdate AND o.timestamp <= $edate AND o.status <> "p" AND (status NOT IN
("u")) AND (status NOT IN ("n")) ) GROUP BY p.prod_code ORDER BY
p.prod_code";
$result = mysql_query($query,$db) or die ("Can't run getstore SQL ERROR: "
.. mysql_error () . "");
if ($myrow = mysql_fetch_array($result)) {
print "<h3 align=\"center\">Total number of products ordered and delivered
between $realstartdate and $realenddate for $brand</h3>";
print '<table width="" border="1" align="center" cellpadding="2"
cellspacing="2" bordercolor="#00CCFF" bgcolor="#F4F4F4"><tr><td>';
print "<table width=\"100%\" border=\"0\" ><tr><td>Product
Code</td><td>Description</td><td>Ordered</td><td>Delivered</td><td>Cost
Price</td><td>Invoice Price</td></tr>";
if ($myrow[totalor] == null) {$totalor = 0;} else {$totalor =
$myrow[totalor];}
$sumord = $totalor;
if ($myrow[totaldel] == null) {$totaldel = 0;} else {$totaldel =
$myrow[totaldel];}
$sumdel = $totaldel;
if ($myrow[totalcostprice] == null) {$cost = 0;} else {$cost =
$myrow[totalcostprice];}
if ($myrow[totalinvoiceprice] == null) {$invoice = 0;} else {$invoice =
$myrow[totalinvoiceprice];}
$sumcost = $cost;
$suminvoice = $invoice;
$cost = $cost / 100;
$invoice = $invoice / 100;
print
"<tr><td>$myrow[prod_code]</td><td>$myrow[description]</td><td>$totalor</td><td>$totaldel</td><td>£$cost</td><td>£$invoice</td></tr>";
while ($myrow = mysql_fetch_array($result)) {
if ($myrow[totalor] == null) {$totalor = 0;} else {$totalor =
$myrow[totalor];}
$sumord = $sumord + $totalor;
if ($myrow[totaldel] == null) {$totaldel = 0;} else {$totaldel =
$myrow[totaldel];}
$sumdel = $sumdel + $totaldel;
if ($myrow[totalcostprice] == null) {$cost = 0;} else {$cost =
$myrow[totalcostprice];}
if ($myrow[totalinvoiceprice] == null) {$invoice = 0;} else {$invoice =
$myrow[totalinvoiceprice];}
$sumcost = $sumcost + $cost;
$suminvoice = $suminvoice + $invoice;
$cost = $cost / 100;
$invoice = $invoice / 100;
print
"<tr><td>$myrow[prod_code]</td><td>$myrow[description]</td><td>$totalor</td><td>$totaldel</td><td>£$cost</td><td>£$invoice</td></tr>";
}
print "<tr><td colspan=5><hr></td></tr>";
$sumcost = $sumcost / 100;
$suminvoice = $suminvoice / 100;
print
"<tr><td> </td><td> </td><td>$sumord</td><td>$sumdel</td><td>£$sumcost</td><td>£$suminvoice</td><tr>";
print "</table></table>";
} else {
print "<h3 align=\"center\">Sorry there are no matches found on your
search, please try again</h3>";
}
Navigation:
[Reply to this message]
|