PHP Execution Speed

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

I have a small app that I would like some advice on. I am pulling a
rather large amount of data from a MySQL table and sometimes the
execution can be as long as 15 to 18 minutes. I would like to know if
anyone has any suggestions for a different approach that might lead to
faster results.

Here is an overview . .

Data table contains sales information for all customers and is similar
to this.

Customer#, Customer Dept#, Item, Qty, Price

I want to see a report by specified customer, broken and subtotaled by

I am currently accomplishing this as follows.

First query = select c_dept, count(p_c_dept) as Cnt from 25sales
where c_nbr=$customer group by p_c_dept

Returns a list of all Departments for the specified customer that have
made a purchase.

I then loop through each record returned and run separate queries on
each to get a usage report for each department. Something like this.

$result = mysql_query($query, $connection);
while ( $row = mysql_fetch_array($result))
#Here I run and display a second query that returns all sales for the
customer where the dept matches the current row form the first query.
#Then run and display a third query that totals each department

End result looks something like this.

Dept     Item      Qty     Price
01       MR123     5       $60.00
01       MR456     2       $30.00
         Total for Dept    $90.00

Dept     Item      Qty     Price
02       MR123     3       $45.00
02       MR456     2       $30.00
         Total for Dept    $75.00

And so on for each dept for the particular customer.

Works great and gives me exactly what I'm looking for . . but . .

I have one customer with around 350 departments and each department
purchases between 200 and 300 different items. When extracting this
info from a database of over 1 million records, it takes a long, long

Can anyone suggest a more efficient way to do this?

Re: PHP Execution Speed

On Tue, 19 Apr 2005 13:30:56 -0700, wrote:

Quoted text here. Click to load it

This is whats killing your report!

Quoted text here. Click to load it

For unusual values of the word great ...

Quoted text here. Click to load it

Yes I can quite imagine.

Quoted text here. Click to load it

You can do this quite simply with one query and one pass through the data

You should also check that you've indexed the right columns and that the
column types match in the various tables.

Re: PHP Execution Speed

Works perfectly. Cut the execution time down to about 20 seconds. Not
sure why I didnt think to do all the subtotals in code rather than
query. I guess I need to remove MS Access from my machine (and my
thought process). Thanks for the giraffe kick in the right direction.

Re: PHP Execution Speed

Quoted text here. Click to load it
than query.

Rather than sub-totaling in code, you might get even faster performance
if you use a UNION ALL query to retrieve the summary rows in the same
query as the detail rows.  The general technique is described in my ASP
speed tricks article on this page:

Applies equally to PHP. So for example, your query might look like

select 1 as rowtype, dept, price from items
  union all
select 2 as rowtype, dept, sum(price) from items group by rowtype, dept
order by dept, rowtype

The number and type of the columns in each component query must match.

Re: PHP Execution Speed

On Tue, 19 Apr 2005 13:30:56 -0700, wrote:

Quoted text here. Click to load it

I think that you need to use a relational database as such. Let it do the
selection work, not php.

You may well want to do some basic maintenance and dba work on the
database itself.


Site Timeline