More efficient way to perform multiple MySQL queries in PHP

Question

I have an online store with thousands of orders and I'm writing some queries to figure out how much money each supplier (brand) has made on the site. I have the following queries, one for every month of the selected year:

$filterJan = "$filterYear-01";
$queryJan = "SELECT price, quantity FROM order_items WHERE productID='$productID' AND timestamp LIKE '%$filterJan%' LIMIT 10000";
$suppliersQueryFilter = mysql_query($queryJan, $connection) or die(mysql_error());

while($rowF = mysql_fetch_assoc($suppliersQueryFilter)) {
    $price = $rowF["price"]*$rowF["quantity"];
    $totalJan = $totalJan+$price;
}

** and so on for each month **

It takes ages to load (we're talking over 60 seconds at least) and I know it is not efficient in any shape or form. For each month these queries are searching through thousands of records. Is there a more efficient way or writing this to:

a) Reduce the amount of code to maybe 1 query b) Make it more efficient to increase loading times

$filterYear contains a year, like 2009.

So what this query does is it selects how much money has been made for each month for a selected year (which is assigned to $filterYear). So the result it generates is a table with Jan, Feb, March... with how much money has been made each month, so £2345, £2101, etc...

One query will generally be more efficient than 12, especially with those horrible loops over the results to get the total price for each month (use SQL's SUM() aggregation function); but have you looked at the indexes on your order_items table? — Mark Baker
– Mark Baker, Commented Jul 31, 2014 at 21:30
@Strawberry How do I turn this into 1 query? (I'm not the best) — user3897343
– user3897343, Commented Jul 31, 2014 at 21:31
And use an other filter for your timestamp column. LIKE '%anything%' can't be helped by indexes. You could use a simple BETWEEN ... AND or some such. — VMai
– VMai, Commented Jul 31, 2014 at 21:31

Marc B · Accepted Answer · 2014-07-31 21:35:23Z

2

You should be storing your timestamp as an actual mysql datetime value, which would make things like

GROUP BY YEAR(timestamp), MONTH(timestamp)
WHERE timestamp BETWEEN $initialtime AND $finaltime

trivially possible. That'd reduce your multiple essentially identical repeated queries to just one single query.

You can use derived values for this, but it'll be less efficient than using a native datetime field:

GROUP BY SUBSTR(timestamp, 0, 4), SUBSTR(timestamp, 6,2)

answered Jul 31, 2014 at 21:35

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

spencer7593 Over a year ago

+1 This is the right approach. OP will also want to include YEAR(timestamp) and MONTH(timestamp), and use a SUM(price*qty) aggregate. (OP code appears to be just adding price, and ignoring qty, which doesn't seem right.) Also not clear why there's a LIMIT 10000 on the OP queries; is OP really only interested in the first 10,000 rows for each month? That doesn't seem right either.

spencer7593 · Accepted Answer · 2014-07-31 22:12:55Z

For best performance, you'd want to submit a query something like this to the database:

SELECT DATE_FORMAT(i.timestamp,'%Y-%m') AS `month`
     , SUM(i.price*i.qty)               AS `total`
  FROM order_items i 
 WHERE i.productID  = 'foo'
   AND i.timestamp >= '2013-01-01'
   AND i.timestamp <  '2013-01-01' + INTERVAL 12 MONTH
 GROUP
    BY DATE_FORMAT(i.timestamp,'%Y-%m')

(This assumes that the timestamp column is MySQL datatype TIMESTAMP, DATETIME or DATE)

Using the deprecated mysql_ interface, you want to avoid SQL Injection vulnerabilities using the mysql_real_escape_string function. (A better option would be to use the mysqli or PDO interface, and use a prepared statement with bind placeholders.)

We want the predicates on the timestamp to be on the BARE column, so MySQL can make use of an available suitable index for a range scan operation, rather than requiring a full scan of every row in the table.

We also want to use the power of the server to quickly derive a total, and return just the total, rather than retrieving every flipping row, and processing each of those rows individually (RBAR = row by agonizing row)

The GROUP BY clause and the SUM() aggregate function are tailor made to suit this result.

With mysql_ interface, the query would look something like this:

$querytext = "
SELECT DATE_FORMAT(i.timestamp,'%Y-%m') AS `month`
     , SUM(i.price*i.qty)               AS `total`
  FROM order_items i 
 WHERE i.productID  = '" . mysql_real_escape_string($thisProductID) . "'
   AND i.timestamp >= '" . mysql_real_escape_string($filterYear) . "-01-01'
   AND i.timestamp <  '" . mysql_real_escape_string($filterYear) . "-01-01' + 
                      INTERVAL 12 MONTH
 GROUP BY DATE_FORMAT(i.timestamp,'%Y-%m')";

# for debugging
#echo $querytext;

Collectives™ on Stack Overflow

More efficient way to perform multiple MySQL queries in PHP

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related