need help with optimizing order history query

juniorprg · February 7, 2007

hello

i have changed the order history box query to make it a page where customers can see their products ordered, but now with the graphics and all its taking too much time even to load the page there is a add to cart, each click on that and back to refresh the page is taking more than 2 mins...which as always my customer runs out of patience...here is the query please help me

<tr>

<tr>

</tr>

</table></td>

</tr>

<tr>

</tr>

<?php if (tep_session_is_registered('customer_id')) {

// retreive the last x products purchased

$orders_query = tep_db_query("select distinct op.products_id from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_PRODUCTS . " p where o.customers_id = '" . (int)$customer_id . "' and o.orders_id = op.orders_id and op.products_id = p.products_id and p.products_status = '1' group by products_id order by o.date_purchased DESC limit " . MAX_DISPLAY_PRODUCTS_IN_ORDER_HISTORY_BOX);

if (tep_db_num_rows($orders_query)) {

?>

<?php

// Most of this file is changed or moved to BTS - Basic Template System - format.

$product_ids = '';

while ($orders = tep_db_fetch_array($orders_query)) {

$product_ids .= (int)$orders['products_id'] . ',';

}

$product_ids = substr($product_ids, 0, -1);?>

<?php

$products_query = tep_db_query("select p.products_id, pd.products_name, pd.products_description, p.products_image, p.products_price, p.products_tax_class_id, op.products_id, o.orders_id, o.date_purchased from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where o.orders_id = op.orders_id and op.products_id = p.products_id and p.products_id=pd.products_id and p.products_id in (" . $product_ids . ") and language_id = '" . (int)$languages_id . "' group by p.products_id order by o.date_purchased DESC, pd.products_name ASC");

while ($products = tep_db_fetch_array($products_query)) {

if ($new_price = tep_get_products_special_price($products['products_id'])) {

$products_price = '<s>' . $currencies->display_price_nodiscount($products['products_price'], tep_get_tax_rate($products['products_tax_class_id'])) . '</s> <span class="productSpecialPrice">' . $currencies->display_price_nodiscount($new_price, tep_get_tax_rate($products['products_tax_class_id'])) . '</span>';

} else {

$products_price = $currencies->display_price_nodiscount($products['products_price'], tep_get_tax_rate($products['products_tax_class_id']));

}

?>

<tr>

<td width="<?php echo SMALL_IMAGE_WIDTH; ?>" valign="top" class="smallText"><?php echo '<script></script><noscript><a href="' . tep_href_link(DIR_WS_IMAGES . $products['products_image']) . '" target="_blank">' . tep_image(DIR_WS_IMAGES . 'icon_zoom.gif', TEXT_CLICK_TO_ENLARGE) . TEXT_CLICK_TO_ENLARGE . '</a></noscript>'; ?></td>

<td valign="top" class="smallText"><?php echo '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $products['products_id']) . '"><b><u>' . $products['products_name'] .'</u></b></a> <br>';?>

<?php echo tep_flatten_product_description($products['products_description'], ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $products['products_id']) . '"></a>').'</u></b></a> <br>' . TEXT_PRICE . ' ' . $products_price . '<br>';?>

<td align="center" valign="middle" class="smallText"><?php echo '<form name="cart_quantity" method="post" action="' . tep_href_link(FILENAME_ORDERHISTORY, tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $products['products_id']) . '">Qty: <input type="text" name="quantity" value="1" maxlength="2" size="2"><br>' . tep_image_submit('button_in_cart.gif', IMAGE_BUTTON_IN_CART) . '</form>'; ?></td></td>

</tr>

<?php

}

else { ?>

<?php

} } ?>

</table>

Velveeta · February 7, 2007

If you want to nail the problem query in that bunch, you're going to have to do some scenario testing... Use an actual customer id and test those queries separately in phpMyAdmin, it will tell you how long each query takes to execute... If you're having load times of 2+ minutes, I highly doubt it's the database's (or queries') fault, but it could be a slow server, if it's shared with other web accounts, which many are, or it could be a crappy transfer rate between points A (you or your customers) and B (the server), which causes slow page transmission, so the queries could execute in a snap, and take 2 minutes to push down to the end user... I'd start with running those queries in phpMyAdmin, and see what it says the execution time is, so you can either rule that out, or pursue optimizing the one that's screwing everything up...

Richard.

♥bruyndoncx · February 7, 2007

the logic of the queries is wrong.

You should combine the two queries into one as the second query does not filter on the customer id or his/her specific orders.

Also the way the tables , rows and cell data is constructed will give strange resuults when there aren't any products.

I'll bet with one query it will run much faster.

juniorprg · February 7, 2007

thanks guys , i agree with you about combining both queries to one, and also running them in phpmyadmin, i will get back to it as soon as im done

juniorprg · February 7, 2007

ok i have combined the query to one and ran on phpmyadmin its showing 540 records and took 6.8 seconds for showing 0-29 records, so when i ran it for a customer who has around 50 records, it takes 7 seconds which is too much, i want it be around 2-3 seconds, coz apparently people run out of patience very fast. this is the order history page with new sql, please tell me how to further optimize this page, i think putting a split page will be good idea, but its showing resource #173 error mesage

<tr>

<tr>

</tr>

</table></td>

</tr>

<tr>

</tr>

<?php if (tep_session_is_registered('customer_id')) {

// retreive the last x products purchased

$orders_query = tep_db_query("SELECT

`products_description`.`products_name`,

`products_description`.`products_description`,

`products`.`products_image`,

`products`.`products_price`,

`orders_products`.`products_id`,

`orders`.`orders_id`,

`orders`.`date_purchased`,

`products`.`products_id`

FROM

`orders`

Inner Join `orders_products` ON `orders`.`orders_id` = `orders_products`.`orders_id`

Inner Join `products` ON `products`.`products_id` = `orders_products`.`products_id`

Inner Join `products_description` ON `products`.`products_id` = `products_description`.`products_id`

WHERE

`orders`.`customers_id` = '" . (int)$customer_id . "' AND

`orders`.`orders_id` = `orders_products`.`orders_id` AND

`orders_products`.`products_id` = `products`.`products_id` AND

`products`.`products_id` = `products_description`.`products_id`

GROUP BY

`products`.`products_id`

ORDER BY

`orders`.`date_purchased` DESC,

`products_description`.`products_name` ASC");

if (tep_db_num_rows($orders_query)) {

?>

<?php

while ($products = tep_db_fetch_array($orders_query)) {

if ($new_price = tep_get_products_special_price($products['products_id'])) {

$products_price = '<s>' . $currencies->display_price_nodiscount($products['products_price'], tep_get_tax_rate($products['products_tax_class_id'])) . '</s> <span class="productSpecialPrice">' . $currencies->display_price_nodiscount($new_price, tep_get_tax_rate($products['products_tax_class_id'])) . '</span>';

} else {

$products_price = $currencies->display_price_nodiscount($products['products_price'], tep_get_tax_rate($products['products_tax_class_id']));

}

?>

<tr>

<td width="<?php echo SMALL_IMAGE_WIDTH; ?>" valign="top" class="smallText"><?php echo '<script></script><noscript><a href="' . tep_href_link(DIR_WS_IMAGES . $products['products_image']) . '" target="_blank">' . tep_image(DIR_WS_IMAGES . 'icon_zoom.gif', TEXT_CLICK_TO_ENLARGE) . TEXT_CLICK_TO_ENLARGE . '</a></noscript>'; ?></td>

<td valign="top" class="smallText"><?php echo '<b>' . $products['products_name'] .'</b></a> <br>';?>

<?php echo tep_flatten_product_description($products['products_description'], ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $products['products_id']) . '"></a>').'</u></b></a> <br>' . TEXT_PRICE . ' ' . $products_price . '<br>';?>

<td align="center" valign="middle" class="smallText"><?php echo '<form name="cart_quantity" method="post" action="' . tep_href_link(FILENAME_ORDERHISTORY, tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $products['products_id']) . '">Qty: <input type="text" name="quantity" value="1" maxlength="2" size="2"><br>' . tep_image_submit('button_in_cart.gif', IMAGE_BUTTON_IN_CART) . '</form>'; ?></td></td>

</tr>

<?php

}

else { ?>

<?php

} } ?>

</table>

juniorprg · February 7, 2007

guys any help here pls

Velveeta · February 7, 2007

// retreive the last x products purchased
$orders_query = tep_db_query("SELECT

`products_description`.`products_name`,

`products_description`.`products_description`,

`products`.`products_image`,

`products`.`products_price`,

`orders_products`.`products_id`,

`orders`.`orders_id`,

`orders`.`date_purchased`,

`products`.`products_id`

FROM

`orders`

Inner Join `orders_products` ON `orders`.`orders_id` = `orders_products`.`orders_id`

Inner Join `products` ON `products`.`products_id` = `orders_products`.`products_id`

Inner Join `products_description` ON `products`.`products_id` = `products_description`.`products_id`

WHERE

`orders`.`customers_id` = '" . (int)$customer_id . "' AND

`orders`.`orders_id` = `orders_products`.`orders_id` AND

`orders_products`.`products_id` = `products`.`products_id` AND

`products`.`products_id` = `products_description`.`products_id`

GROUP BY

`products`.`products_id`

ORDER BY

`orders`.`date_purchased` DESC,

`products_description`.`products_name` ASC");

Ok, the first thing I notice in this query is an extraneous products_id selection, which can be dropped:

$orders_query = tep_db_query("SELECT

`products_description`.`products_name`,

`products_description`.`products_description`,

`products`.`products_image`,

`products`.`products_price`,

`orders_products`.`products_id`,

`orders`.`orders_id`,

`orders`.`date_purchased`,

`products`.`products_id`

Because you're already pulling the products_id from the orders_products table... Secondly, you're doing your joins twice:

FROM

`orders`

Inner Join `orders_products` ON `orders`.`orders_id` = `orders_products`.`orders_id`

Inner Join `products` ON `products`.`products_id` = `orders_products`.`products_id`

Inner Join `products_description` ON `products`.`products_id` = `products_description`.`products_id`

WHERE

`orders`.`customers_id` = '" . (int)$customer_id . "' AND

`orders`.`orders_id` = `orders_products`.`orders_id` AND

`orders_products`.`products_id` = `products`.`products_id` AND

`products`.`products_id` = `products_description`.`products_id`

These state the same thing... An inner join is the same in mysql as performing the checks in the where clause, so choose 1 or the other... I would go with the where clause and drop the inner joins, making it like this:

FROM

`orders`, `orders_products`, `products`, `products_description`

WHERE

`orders`.`customers_id` = '" . (int)$customer_id . "' AND

`orders`.`orders_id` = `orders_products`.`orders_id` AND

`orders_products`.`products_id` = `products`.`products_id` AND

`products`.`products_id` = `products_description`.`products_id`

So the final query should look like this:

$orders_query = tep_db_query("SELECT

`products_description`.`products_name`,

`products_description`.`products_description`,

`products`.`products_image`,

`products`.`products_price`,

`orders_products`.`products_id`,

`orders`.`orders_id`,

`orders`.`date_purchased`

FROM

`orders`, `orders_products`, `products`, `products_description`

WHERE

`orders`.`customers_id` = '" . (int)$customer_id . "' AND

`orders`.`orders_id` = `orders_products`.`orders_id` AND

`orders_products`.`products_id` = `products`.`products_id` AND

`products`.`products_id` = `products_description`.`products_id`

GROUP BY

`products`.`products_id`

ORDER BY

`orders`.`date_purchased` DESC,

`products_description`.`products_name` ASC");

Also, unless you only have 1 language in your database, you're gonna want to put a check in that where clause for and `products_description`.`language_id` = '" . (int)$languages_id . "'" to make sure you pull back the right language...

Richard.

juniorprg · February 8, 2007

thanks richard, that was helpful , i did further optimize the query its now

i think the only solution now is to put split pages as well..curently query takes around 6.8 seconds, not a big change....

$orders_query = tep_db_query("SELECT

`products_description`.`products_name`,

`products_description`.`products_description`,

`products`.`products_image`,

`products`.`products_price`

FROM

`orders`, `orders_products`, `products`, `products_description`

WHERE

`orders`.`customers_id` = '" . (int)$customer_id . "' AND

`orders`.`orders_id` = `orders_products`.`orders_id` AND

`orders_products`.`products_id` = `products`.`products_id` AND

`products`.`products_id` = `products_description`.`products_id` AND

`products`.`products_status` = '1'

GROUP BY

`products`.`products_id`

ORDER BY

`orders`.`date_purchased` DESC,

`products_description`.`products_name` ASC");

juniorprg · February 8, 2007

i fixed it finally it was all about indexing the tables in the database...i had to create an index in orders_products table for order_id and it works like a charm...so now on wards pple always look at the indexes if query is slow

♥bruyndoncx · February 8, 2007

congrats !

sorry, I'm short on time, but looks like Velveeta did a great job coaching you along.

I agree, indexing will help, but can also hide the problem. First you need to trim the query to the bare essentials, and then re-index and add any additional indexes when needed (if the improvement in retrieval times justify the (small) delay in insert/update of the order data).

Another 2 cents ...to keep you guys busy overnight :D

1) I don't think you need the group by statement, if it is to enforce uniqueness, "select distinct" will do, but I really doubt if it is needed.

I suppose your final query contains products_id and data ordered as columns retrieved, so that you can show the chronological history.

(if you only want to show repeat orders once, then a group by products_id having order_date = max(order_date) I think needs to be added).

2) you can probably remove products_status as a condition, but still retrieve the status so you just not hot link the product when it is no longer active. Saves you on querying time.

3) if you run the query in phpmyadmin or other sql tool, try EXPLAIN ( <sql query> ) and see waht the mysql engine says about what indexing being used (GOOD) or table scans performed (BAD). This will give you the insight if you would benefit from an additional index (possibly combined on 2 fields) when your business and order table grows.

juniorprg · February 9, 2007

thanks for your feedback craine, i have to group by product_id because mutlple orders can be placed on same product so it shouldnt be repeated, and about adding max(order_date) i havnt really understood that implication but i will run the query and see if thats needed. I did the EXPLAIN in SQL it doesnt show anything about index being GOOD or BAD.this is my current sql

SELECT

`products_description`.`products_name`,

`products_description`.`products_description`,

`products`.`products_image`,

`products`.`products_price`,

`orders_products`.`products_id`,

`orders`.`orders_id`,

`products`.`products_status`,

`orders`.`date_purchased`

FROM

`orders`, `orders_products`, `products`, `products_description`

WHERE

`orders`.`customers_id` = '" . (int)$customer_id . "' AND

`orders`.`orders_id` = `orders_products`.`orders_id` AND

`orders_products`.`products_id` = `products`.`products_id` AND

`products`.`products_id` = `products_description`.`products_id` AND

`products`.`products_status` = '1'

GROUP BY

`products`.`products_id`

ORDER BY

`orders`.`date_purchased` DESC,

`products_description`.`products_name` ASC

juniorprg · February 9, 2007

guys, i want to give this as a contribution, i think it might be helpful for people, but i work on a template system so its hard for me to complie into basic oscommerce php page, if i give the codes will anyone be able to complie it

♥bruyndoncx · February 9, 2007

If you post the page you currently have here, then someone might.

I personally don't have this need, but lots of others might.

It never hurts to share code that you didn't pay for (except for your own sweat and tears of course :P )

Velveeta · February 9, 2007

If you post the page you currently have here, then someone might.
I personally don't have this need, but lots of others might.

It never hurts to share code that you didn't pay for (except for your own sweat and tears of course :P )

And blood! Let's not forget the blood!

juniorprg · February 12, 2007

THIS IS MY PAGE

<tr>

<tr>

</tr>

</table></td>

</tr>

<tr>

</tr>

<?php if (tep_session_is_registered('customer_id')) {

// retreive the last x products purchased

$orders_query = tep_db_query("SELECT

`products_description`.`products_name`,

`products_description`.`products_description`,

`products`.`products_image`,

`products`.`products_price`,

`orders_products`.`products_id`,

`orders`.`orders_id`,

`products`.`products_status`,

`orders`.`date_purchased`

FROM

`orders`, `orders_products`, `products`, `products_description`

WHERE

`orders`.`customers_id` = '" . (int)$customer_id . "' AND

`orders`.`orders_id` = `orders_products`.`orders_id` AND

`orders_products`.`products_id` = `products`.`products_id` AND

`products`.`products_id` = `products_description`.`products_id` AND

`products`.`products_status` = '1'

GROUP BY

`products`.`products_id`

ORDER BY

`orders`.`date_purchased` DESC,

`products_description`.`products_name` ASC");

if (tep_db_num_rows($orders_query)){

?>

<?php

while ($products = tep_db_fetch_array($orders_query)) {

if ($new_price = tep_get_products_special_price($products['products_id'])) {

$products_price = '<s>' . $currencies->display_price_nodiscount($products['products_price'], tep_get_tax_rate($products['products_tax_class_id'])) . '</s> <span class="productSpecialPrice">' . $currencies->display_price_nodiscount($new_price, tep_get_tax_rate($products['products_tax_class_id'])) . '</span>';

} else {

$products_price = $currencies->display_price_nodiscount($products['products_price'], tep_get_tax_rate($products['products_tax_class_id']));

}

?>

<tr>

<td width="<?php echo SMALL_IMAGE_WIDTH; ?>" valign="top" class="smallText"><?php echo '<script></script><noscript><a href="' . tep_href_link(DIR_WS_IMAGES . $products['products_image']) . '" target="_blank">' . tep_image(DIR_WS_IMAGES . 'icon_zoom.gif', TEXT_CLICK_TO_ENLARGE) . TEXT_CLICK_TO_ENLARGE . '</a></noscript>'; ?></td>

<td valign="top" class="smalltext"><?php echo '<b>' . $products['products_name'] .'</b><br>';?>

<?php echo tep_flatten_product_description($products['products_description'], ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $products['products_id']) . '"></a>').'</u></b></a> <br>' . TEXT_PRICE . ' ' . $products_price . '<br>';?>

<td align="center" valign="middle" class="smallText"><?php echo '<form name="cart_quantity" method="post" action="' . tep_href_link(FILENAME_ORDERHISTORY, tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $products['products_id']) . '">Qty: <input type="text" name="quantity" value="1" maxlength="2" size="2"><br>' . tep_image_submit('button_in_cart.gif', IMAGE_BUTTON_IN_CART) . '</form>'; ?></td></td>

</tr>

<?php

}

else { ?>

<?php

}

} ?>

</table>

need help with optimizing order history query

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived