Improve an inefficient mysql database query

  • Posted:
  • Proposals: 4
  • Remote
  • #1029293
  • Awarded
Derek P.
Darshan S.Faizan Q.Tariq A. have already sent a proposal.
  • 0

Description

Experience Level: Intermediate
We have a module for Opencart that has a query that is causing excess load on the server.

The module allows a customer to filter a category of products by manufacturer and in/out of stock, it is very useful and we want to retain it but it causes a database load on every page load and we would like to make it as efficient as possible so that it does not cause this excess load.

The specific query is:

public function getManufacturersForFilteringInSearch($data = array()) {
$sql = "SELECT DISTINCT p.manufacturer_id AS manufacturer_id, m.name AS manufacturer_name FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "product_tag pt ON (p.product_id = pt.product_id) LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) WHERE p.status = '1' AND p.date_available get('config_store_id') . "'";

And...

public function getManufacturersForFiltering($category_id) {
$sql = "SELECT DISTINCT p.manufacturer_id AS manufacturer_id, m.name AS manufacturer_name FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p2c.category_id = '" . (int)$category_id . "' ORDER BY `m`.`name` ASC";

I also attach the module.

I am open to suggestion based on :

1) Proposal to improve the query/queries
2) Proposal to review the module as a whole to make it more efficient, or work in a slightly different way, to reduce load whilst delivering functionality
3) An alternative module that could do the same job

I'll specify a fixed price of £35 but understand that the amount will differ based on the proposal.

I look forward to hearing from you.

Rob

New Proposal

Create an account now and send a proposal now to get this job.

Sign up

Clarification Board Ask a Question

    There are no clarification messages.