I recently received an order for backend optimization. The issue described was the slowness of the product editing and catalog pages. After using XHProf to check, it became clear that the delay was specifically in the model and in the functions for obtaining options and attributes. It was decided to optimize the options and attributes as well as optimize the PHP and SQL code.
A similar issue was also present on the category, product, search, and manufacturer pages of the storefront and the CSV Product Export extension because the code is identical throughout.
Solution
I have removed additional SQL subqueries from the functions used to obtain options and attributes, as all the necessary data can now be obtained in a single SQL query. We have also utilized PHP pointers, enabling us to process all the data in a single cycle. This optimization option is suitable for all versions of OpenCart.
<?php
public function getProductOptions($product_id) {
$product_option_data = array();
$product_option_query = $this->db->query("SELECT pov.product_option_value_id, pov.option_value_id, ovd.name as product_option_value_name, od.name as option_name, ov.image, pov.quantity, pov.subtract, pov.price, pov.price_prefix, pov.points, pov.points_prefix, pov.weight, pov.weight_prefix, pov.product_option_id, pov.option_id, o.type, po.required
FROM " . DB_PREFIX . "product_option_value pov
LEFT JOIN " . DB_PREFIX . "option_value ov ON (pov.option_value_id = ov.option_value_id)
LEFT JOIN " . DB_PREFIX . "option_value_description ovd ON (ov.option_value_id = ovd.option_value_id)
LEFT JOIN " . DB_PREFIX . "product_option po ON (pov.product_option_id = po.product_option_id)
LEFT JOIN `" . DB_PREFIX . "option` o ON (po.option_id = o.option_id)
LEFT JOIN " . DB_PREFIX . "option_description od ON (o.option_id = od.option_id)
WHERE po.product_id = " . (int)$product_id . "
AND od.language_id = " . (int)$this->config->get('config_language_id') . "
ORDER BY o.sort_order, ov.sort_order");
$product_option_value_data = array();
foreach ($product_option_query->rows as $product_option) {
if (in_array($product_option['type'], array('select', 'radio', 'checkbox', 'image'))) {
$product_option_value_data[$product_option['product_option_id']][] = array(
'product_option_value_id' => $product_option['product_option_value_id'],
'option_value_id' => $product_option['option_value_id'],
'name' => $product_option['product_option_value_name'],
'image' => $product_option['image'],
'quantity' => $product_option['quantity'],
'subtract' => $product_option['subtract'],
'price' => $product_option['price'],
'price_prefix' => $product_option['price_prefix'],
'points' => $product_option['points'],
'points_prefix' => $product_option['points_prefix'],
'weight' => $product_option['weight'],
'weight_prefix' => $product_option['weight_prefix']
);
$product_option_data[$product_option['option_id']] = array(
'product_option_id' => $product_option['product_option_id'],
'option_id' => $product_option['option_id'],
'name' => $product_option['option_name'],
'type' => $product_option['type'],
'required' => $product_option['required']
);
$product_option_data[$product_option['option_id']]['option_value'] = & $product_option_value_data[$product_option['product_option_id']];
} else {
$product_option_data[$product_option['option_id']] = array(
'product_option_id' => $product_option['product_option_id'],
'option_id' => $product_option['option_id'],
'name' => $product_option['option_name'],
'type' => $product_option['type'],
'option_value' => $product_option['option_value'], 'required' => $product_option['required']
);
}
}
return $product_option_data;
}
public function getProductAttributes($product_id) {
$product_attribute_group_data = array();
$product_attribute_group_query = $this->db->query("SELECT a.attribute_id, ad.name as attribute_name, pa.text, ag.attribute_group_id, agd.name as attribute_group_name FROM " . DB_PREFIX . "product_attribute pa
LEFT JOIN " . DB_PREFIX . "attribute a ON (pa.attribute_id = a.attribute_id)
LEFT JOIN " . DB_PREFIX . "attribute_description ad ON (a.attribute_id = ad.attribute_id)
LEFT JOIN " . DB_PREFIX . "attribute_group ag ON (a.attribute_group_id = ag.attribute_group_id)
LEFT JOIN " . DB_PREFIX . "attribute_group_description agd ON (ag.attribute_group_id = agd.attribute_group_id)
WHERE pa.product_id = " . (int)$product_id . "
AND ad.language_id = agd.language_id
AND pa.language_id = agd.language_id
AND agd.language_id = " . (int)$this->config->get('config_language_id') . "
ORDER BY ag.sort_order, agd.name, a.sort_order, ad.name");
$product_attribute_data = array();
foreach ($product_attribute_group_query->rows as $product_attribute) {
$product_attribute_data[$product_attribute['attribute_id']][] = array(
'attribute_id' => $product_attribute['attribute_id'],
'name' => $product_attribute['attribute_name'],
'text' => $product_attribute['text']
);
$product_attribute_group_data[$product_attribute['attribute_group_id']] = array(
'attribute_group_id' => $product_attribute['attribute_group_id'],
'name' => $product_attribute['attribute_group_name']
);
$product_attribute_group_data[$product_attribute['attribute_group_id']]['attribute'] = & $product_attribute_data[$product_attribute['attribute_id']];
}
return $product_attribute_group_data;
}
I did the same with the admin panel (only options) and with the CSV Product Export extension.
Be cautious when using this code; it has been modified. This means that vqmod/ocmod adjustments that apply to this section of code may not function as expected.
Result
- The administrative panel page now loads approximately 25 seconds faster, taking no more than 1.5 seconds.
- The page load time for catalog pages (categories/products/search/manufacturers) has decreased by a couple of seconds.
- The CSV Product Export extension now generates an XML file with products in just a couple of seconds, instead of taking 6 minutes.
Services
If you encounter any difficulties or your project experiences performance issues, feel free to ask for assistance. Everything is optimizable. I prefer optimizing the code rather than increasing server resources.
