Inconsistent Script Performance Between Servers with Join Queries

[Joel.DNSVault]

Gentlemen,

 

I am having a rather puzzling problem with a script I am trying to write for a client. He has a Stock Control system I developed that integrates with his Shopping Cart. It has three levels of categories because the product datbase is so huge. He wants to be able to get a report that tells him what his sales have been from a particular Master Category (the very top level, eg PET FOODS), organised by supplier.

 

I wrote a script that worked fine on my local server which is running PHP 5 and MySQL 5 (both with version 4 compatibility on), presenting a report over a 4 month date range in a matter of seconds. However when the script is run on the server (which is running MySQL 4 and PHP 4) and even when given a smaller date range, the script hangs and refuses to process.

 

The script uses joins quite a bit. For example the first query that finds the relevant product IDs from the request master category and the supplier is ....

 

select * from $Cat, $Sub, $Stk where

(

$Cat.Master_Category_ID = '$Master_Category_ID' or

$Cat.Master_Category_ID_2 = '$Master_Category_ID' or

$Cat.Master_Category_ID_3 = '$Master_Category_ID'

)

and

(

$Cat.Category_ID = $Sub.Source_Category_ID or

$Cat.Category_ID = $Sub.Source_Category_ID_2 or

$Cat.Category_ID = $Sub.Source_Category_ID_3

)

and

(

$Sub.Category_ID = $Stk.Category_ID or

$Sub.Category_ID = $Stk.Category_ID_2 or

$Sub.Category_ID = $Stk.Category_ID_3 or

$Sub.Category_ID = $Stk.Category_ID_4

)

and

$Stk.Supplier_ID = '{$Supplier['Supplier_ID']}'

 

What $Cat is the categories table (which come after the master categories) and $Sub is the sub categories table (which comes after the categories) and $Stk is the stock database itself. The code is looping through the suppliers table trying to find matching products for the supplier, hence the $Supplier['Supplier_ID'].

 

Then when it has the Product IDs, it loops through them with the following query...

 

select distinct $Ord.Order_ID, $Pur.Price, $Pur.Quantity from $Ord, $Pur, $Pay where

$Ord.Order_ID = $Pur.Order_ID and

$Ord.Order_ID = $Pay.Order_ID and

($Ord.Order_Status = 'Processed' or $Ord.Courier_Number != '') and

$Pur.Product_ID = '{$Product['Product_ID']}' and

($Pay.Date_Paid >= '$From_Date' and $Pay.Date_Paid <= '$To_Date')

order by $Pay.Date_Paid desc

 

Where $Ord is the orders table, $Pur contains the individual purchases of an order, and $Pay is the payments table. An order can have more than one payment, hence the distinct Order_ID and sort by Date_Paid desc (yes I know this is not perfect if the final payment fell outside the date range but it is all I have to work with). An order is determined complete by either the Status or the presence of a courier number.

 

I am going to change the way things are done a bit with the orders such as creating a Date Processed field and tightening up the use of the Order Status so I am not relying on the courier number presence as well, but this is not the first example I have seen of this server's performance being considerably poorer than it's specifications would suggest so this is why I decide to present you guys with this case.

 

The tables being used are identical - I download copies of the latest ones of the server to my local machine. The only difference I know of is that the server is running version 4 software while I am running version 5. Is this the answer, or would tweaking a buffer setting somewhere give better results.

 

Thanks for reading, I know it's a lot

Joel.

[fenway]

Well, first things first -- that's not a normalized DB.  You have repeated category columns.

 

Second, as a result, the first multi-table query won't be able to use a proper index, so it will be VERY slow.