Page 1 of 1

How can I speed up my query?

Posted: Fri Oct 03, 2008 2:35 pm
by micknc
I have a table that holds all of our products and the upcoming availability of each product. It is currently laid out with the following columns:
ProductID---WeekDate---ProductAvailability

We forecast around15 weeks of availability so that means for each product we are creating 15 rows. It is extremely slow to load and edit. Here is the cut down version of the current code:

Code: Select all

 
$today=date("N")-1;
$mon=strtotime('-'.$today.' days');
$monformat=date("Y-m-d",$mon); 
$baseweekorig=date("Y-m-d",strtotime("".$monformat." +0 week"));
$postamtweeks="8";
 
echo "<table border='1'><tr><td>Product Name</td>";
while ($i < $postamtweeks){
echo "<td>".date("Y-m-d",strtotime("".$baseweekorig." +".$i." week"));"</td>";
$i++;
}
echo "</tr>";
$resultprod=mysql_query("select ProductID, ProductName from product where ProdType='1' Order By ProductName");
while($rowprod=mysql_fetch_assoc($resultprod)){
$id=$rowprod['ProductID'];
echo "<tr><td>".$rowprod['ProductName']."</td>";
        $j="0";
        $Qty="0";
    while ($j < $postamtweeks){
        $baseweek=date("Y-m-d",strtotime("".$baseweekorig." +".$j." week"));
    $result=mysql_query("select * from availability where UnrootedID='$id' and WeekDate = '$baseweek'");
    while($row=mysql_fetch_assoc($result)){$Qty=$row['UnrootedAvailable'];}
        echo "<td>".number_format($Qty)."</td>";
        $j++;
      } 
echo "</tr>";
$Qty="0";
}
 
I have two questions.
First, would it be smarter to lay the table out differently?
ProductID--_ProductAvailability--Week1---Week2---Week3---Week4--etc

That layout would mean that I would only have to connect with one statement and then report the weeks I am looking for instead of having to connect multiple times.

Secondly, am I going about the current query correctly? I need a "spreadsheet" type of layout for the office guys and this achieves it but, again, is very slow (I suspect because of multiple connections).

Thanks for the help,
Mick

Re: How can I speed up my query?

Posted: Fri Oct 03, 2008 4:35 pm
by jaoudestudios
You are doing two queries and the second is run on every result from the first! So say you have 100 results from the first query, you will have 101 queries that go from php to mysql server and back to php. Try and do it in one query Use a JOIN.

Then also make sure you index the right columns. Not sure what your schema is but try using CHAR instead of VARCHAR, it will use more memory but will be faster for searching.

Re: How can I speed up my query?

Posted: Fri Oct 03, 2008 5:17 pm
by micknc
I think the ProductID is tinytext. The Availability is TinyInt.

The reason I am connecting inside of the first connection is that I need to show all of the products but they don't all show up in the second table.
If I write the connection like this:

Code: Select all

 
$result=mysql_query("select * from product Inner Join availability ON product.ProductID = availability.UnrootedID where product.ProdType='1' and availability.WeekDate = '$baseweek' Order By ProductName");
 
 
I would exclued all products that don't show up in the availability table.

I need to show all products that exist in the product table where ProdType='1' .
Then I need to show (out of availability) the numbers that correspond to that week and product. The product may not exist in availability at all (if it isn't available).

Is there something simple I am missing?

Re: How can I speed up my query?

Posted: Fri Oct 03, 2008 5:52 pm
by micknc
Ok... I see that I shouldn't limit the availability table during the query so now I have this:

Code: Select all

 
$resultprod=mysql_query("select product.ProductID, product.ProductName, availability.UnrootedAvailable, availability.WeekDate from product Inner Join availability ON product.ProductID = availability.UnrootedID where product.ProdType='1' Order By ProductName");
 
 
But now the question is how I display the Weekdates correctly. They are getting returned like this:

Product 1
Qty 100
Week 1

Product 1
Qty
Week 2

Product 1
Qty 200
Week3

So how do I break this out in the spreadsheet form that has been requested.
What I mean by that
Product---Week1---Week2---Week3
1----------100------0---------200

Re: How can I speed up my query?

Posted: Fri Oct 03, 2008 7:02 pm
by micknc
I think I have it all worked out. Would love to hear some feedback:

Code: Select all

 
$today=date("N")-1;
$mon=strtotime('-'.$today.' days');
$monformat=date("Y-m-d",$mon); 
$baseweekorig=date("Y-m-d",strtotime("".$monformat." +0 week"));
$lastweek=date("Y-m-d",strtotime("".$monformat." +6 week"));
$postamtweeks="6";
 
$i="0";
$oldid="0";
 
echo "<table border='1'><tr><td>Product Name</td>";
while ($i < $postamtweeks){
echo "<td>".date("Y-m-d",strtotime("".$baseweekorig." +".$i." week"));"</td>";
$i++;
}
 
$resultprod=mysql_query("select product.ProductID, product.ProductName, availability.UnrootedAvailable, availability.WeekDate from product Inner Join availability ON product.ProductID = availability.UnrootedID where product.ProdType='1' Order By ProductName");
while($rowprod=mysql_fetch_assoc($resultprod)){
$id=$rowprod['ProductID'];
$ProductName=$rowprod['ProductName'];
$Qty=$rowprod['UnrootedAvailable'];
$Qtyf=number_format($Qty);  
$WeekDate=$rowprod['WeekDate'];
 
      if ($id != $oldid){echo "</tr><tr><td>".$ProductName."</td>";}
      elseif ($WeekDate >= $baseweekorig and $WeekDate < $lastweek){echo "<td>".$Qtyf."</td>";}
            else{}
            
$oldid=$rowprod['ProductID'];
}