|
|
Posted by Tom on 10/15/07 17:02
On Thu, 04 Oct 2007 22:21:05 GMT, Brian wrote...
>
>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>";
>
>}
>
>
>
>
I use EXPLAIN regularly to test my SELECTs. Sometimes the feedback is good for
detecting big delays.
Tom
--
Newsguy.com - Unlimited Accounts
Now with 32 concurrent connections
Navigation:
[Reply to this message]
|