• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

Can this script be made quicker?

Hello

I have this script it works fine but is a little slow, I have indexed all the corect mysql colums.

Is there anway the script could be writtern better to work faster?


<?php
$query1 = "SELECT SUM(sales_table.Rebate) AS Rebate, sales_table.MemberID, user_table_cash.fname, user_table_cash.lname, user_table_cash.add1, user_table_cash.town, user_table_cash.county, user_table_cash.postcode, user_table_cash.payee, user_table_cash.email
FROM sales_table, user_table_cash
WHERE
user_table_cash.userid=sales_table.MemberID
AND
sales_table.DeleteFlag != 1
AND
sales_table.DeleteFlag != 2
AND
sales_table.DeleteFlag != 3
AND
sales_table.CreateDate < '$to'
GROUP BY
sales_table.MemberID";

$result1 = mysql_query($query1);

while($row1 = mysql_fetch_array($result1,MYSQL_ASSOC))
{
extract($row1);//extract row

$Rebate2 = number_format($Rebate, 2, '.', ','); //format Rebate
$Rebate4 = explode(".", $Rebate2); //divde the sum
$fname2 = ucfirst(strtolower($fname)); //makes the first letter uppercase
$lname2 = ucfirst(strtolower($lname)); //makes the first letter uppercase
$add12 = ucfirst(strtolower($add1)); //makes the first letter uppercase
$add13 = str_replace(",", "", $add12); // get rid off the ,
$town2 = ucfirst(strtolower($town)); //makes the first letter uppercase
$town3 = str_replace(",", "", $town2); // get rid off the ,
$county2 = ucfirst(strtolower($county)); //makes the first letter uppercase
$county3 = str_replace(",", "", $county2); // get rid off the ,
$postcode2 = strtoupper ($postcode); //makes all letters uppercase
$payee2 = ucfirst(strtolower($payee)); //makes the first letter uppercase

if ($Rebate > 25)
{
if ($add12 == "")
{
echo "<b><font color=\"#CC0000\">$Rebate4[0],$Rebate4[1]****,$fname2,$lname2,$add13,$town3,$county3,$postcode2,$payee2****,$MemberID,$date</font><br></b>";
}
else
{
echo "$Rebate4[0],$Rebate4[1]****,$fname2,$lname2,$add13,$town3,$county3,$postcode2,$payee2****,$MemberID,$date<br>";
}
}
}
?>
0
submissiontechnology
Asked:
submissiontechnology
2 Solutions
 
RoonaanCommented:
Small things, but nothing actually would improve speed I guess:

1. change mysql_fetch_array into mysql_fetch_assoc.
2. drop the extract and just use $row1['fieldname'];
3. change the 'AND sales_table.DeleteFlag != 1 AND sales_table.DeleteFlag != 2 AND sales_table.DeleteFlag != 3' to something like:
- if maxvalue = 3: AND sales_table.DeleteFlag == 0
- if minvalue = 1: AND sales_table.DeleteFlag > 3
- if netiher: AND NOT (sales_table.DeleteFlag BETWEEN 1 AND 3)

4. You could benchmark the effect of having the '< $to' as first item in the whereclause, but I am not sure if this will effect things in a major fasion.

Regards

-r-
0
 
_GeG_Commented:
$query1 = "SELECT SUM(sales_table.Rebate) AS Rebate, sales_table.MemberID, user_table_cash.fname, user_table_cash.lname, user_table_cash.add1, user_table_cash.town, user_table_cash.county, user_table_cash.postcode, user_table_cash.payee, user_table_cash.email
FROM sales_table, user_table_cash
WHERE
user_table_cash.userid=sales_table.MemberID
AND
sales_table.DeleteFlag NOT IN (1,2,3)
AND
sales_table.CreateDate < '$to'
GROUP BY
sales_table.MemberID";

In case you don't want to show all rows, use LIMIT

I agree with Roonan, I wouldn't use extract.
Also you might put an "echo time();" in the first line, before and after the query and in the end, so that you can find out if the query is slow or the PHP execution.

CU
GeG
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now