How can I speed up my query?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

How can I speed up my query?

Post 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
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: How can I speed up my query?

Post 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.
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

Re: How can I speed up my query?

Post 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?
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

Re: How can I speed up my query?

Post 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
User avatar
micknc
Forum Contributor
Posts: 115
Joined: Thu Jan 24, 2008 11:13 pm

Re: How can I speed up my query?

Post 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'];
}
 
Post Reply