Making sales statistics for my ERP system, but performance is bad

I have a ERP system programmed in PHP with a mySQL database, with all my orders for the past 4 years in it. Now I would like to make a function to generate sales statistics. It should be possible to set search criteria like Salesman, Department and year/period.

The sales statistics should be grouped by customer. Just like the illustration on this link: http://maabjerg.eu/illustration_stat.png

My customers table:

customers
--------------------
id - int - auto - primary
name - varchar(100)

My orders table:

orders
-------------------
id - int - auto - primary
customerId - int
departmentId - int
salesmanId - int
orderdate - datetime
invoicedate - datetime
quantity - int
saleprice - decimal(10,2)

I had no trouble making this, but the performance is very bad. The way I had made it before was like:

foreach($customers as $customer)
{

foreach($months as $month)
{
    $sql = mysql_query("select sum(quantity*saleprice) as amount from orders where DATE_FORMAT(invoicedate, '%m-%Y') = '".$month."-".$_REQUEST["year"]."' AND customerId='".$customer->id."'",$connection) or die(mysql_error());
$rs = mysql_fetch_assoc($sql);

$result[$customerId][$month] = $rs["amount"];

}

}

I hope someone can give me advice how to make this the best way.

Thanks in advance.

Steffen

Solution

This is your query:

select sum(quantity*saleprice) as amount
from order
where DATE_FORMAT(invoicedate, '%m-%Y') = '".$month."-".$_REQUEST["year"]."' AND 
      customerId='".$customer->id."'

As written, if you want to speed it up, add an index on order(customerId).

You should also do this as one query:

select c.name, sum(quantity*saleprice) as amount
from customers c left outer join
     order o
     on c.id = o.customerId
where DATE_FORMAT(invoicedate, '%m-%Y') = '".$month."-".$_REQUEST["year"]."' AND 
      customerId='".$customer->id."'
group by c.name;

You can rewrite the query a bit, and build an index on order(customerId, invoicedate). This would require creating constants for the beginning and ending of the period and then doing something like:

select c.name, sum(quantity*saleprice) as amount
from customers c left outer join
     order o
     on c.id = o.customerId
where invoicedate $StartDate and $EndDate AND 
      customerId='".$customer->id."'
group by c.name;

MySQL cannot use an index when there is a function call on the column.