Improve an inefficient mysql database query
- or -
Post a project like this2987
£35(approx. $44)
- Posted:
- Proposals: 4
- Remote
- #1029293
- Awarded
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
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
Rob W.
100% (34)Projects Completed
45
Freelancers worked with
15
Projects awarded
50%
Last project
10 Jul 2017
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies