Hello,
I have got this problem.
I use the latest opcart instalation, have the problems at to separate hosting providers.
When i import all my products and categories (11.000 products and 100 categories)
the server gets overloaded and my hosting provider shut my site down.
Is there anything i can change in the config that the site wont crashes the server?
mabey something with the cache or something??
regards Michiel
I have got this problem.
I use the latest opcart instalation, have the problems at to separate hosting providers.
When i import all my products and categories (11.000 products and 100 categories)
the server gets overloaded and my hosting provider shut my site down.
Is there anything i can change in the config that the site wont crashes the server?
mabey something with the cache or something??
regards Michiel
See this thread. I have it linked to my second vqmod post. Try that and it should help. http://forum.opencart.com/viewtopic.php ... 20#p171445mixup wrote:hello daniel,
tnx for your reply. but the import goes well, but after i imported the products the pages load very slow (10 - 20 seconds to load)
And than it overload the server.
OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter
oke im gonna try it, tnx
This is what the hosting company sended to me:
This query seems to be the problem:
SELECT COUNT( DISTINCT p.product_id ) AS total
FROM oc_product p
LEFT JOIN oc_product_description pd ON ( p.product_id = pd.product_id )
LEFT JOIN oc_product_to_store p2s ON ( p.product_id = p2s.product_id )
WHERE pd.language_id = '2'
AND p.status = '1'
AND p.date_available <= NOW( )
AND p2s.store_id = '0'
AND p.product_id
IN (
SELECT p2c.product_id
FROM oc_product_to_category p2c
WHERE p2c.category_id = '485'
)
This is what the hosting company sended to me:
This query seems to be the problem:
SELECT COUNT( DISTINCT p.product_id ) AS total
FROM oc_product p
LEFT JOIN oc_product_description pd ON ( p.product_id = pd.product_id )
LEFT JOIN oc_product_to_store p2s ON ( p.product_id = p2s.product_id )
WHERE pd.language_id = '2'
AND p.status = '1'
AND p.date_available <= NOW( )
AND p2s.store_id = '0'
AND p.product_id
IN (
SELECT p2c.product_id
FROM oc_product_to_category p2c
WHERE p2c.category_id = '485'
)
yes that is the exact query that the vqmod addresses. It doesn't eliminate the count query, but it rolls it in to the model and caches the results.mixup wrote:oke im gonna try it, tnx
This is what the hosting company sended to me:
This query seems to be the problem:
SELECT COUNT( DISTINCT p.product_id ) AS total
FROM oc_product p
LEFT JOIN oc_product_description pd ON ( p.product_id = pd.product_id )
LEFT JOIN oc_product_to_store p2s ON ( p.product_id = p2s.product_id )
WHERE pd.language_id = '2'
AND p.status = '1'
AND p.date_available <= NOW( )
AND p2s.store_id = '0'
AND p.product_id
IN (
SELECT p2c.product_id
FROM oc_product_to_category p2c
WHERE p2c.category_id = '485'
)
OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter
the solution is here:
http://code.google.com/p/opencart/source/detail?r=520
replace these files fromt he ones in the svn.
http://code.google.com/p/opencart/source/detail?r=520
replace these files fromt he ones in the svn.
OpenCart®
Project Owner & Developer.
I wouldn't jump on that just yet. You simply cannot do it with group by. I tried it every which way to do that and it's simply not possible. Daniel you need to take a close look at the data returned to make sure it's correct, because you cannot get a recursive lookup of all products in a category and it's subcategories using group by.Daniel wrote:the solution is here:
http://code.google.com/p/opencart/source/detail?r=520
replace these files fromt he ones in the svn.
I have not looked over the whole solution yet, but the fact that it's using group by makes me very suspicious.
OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter
Code: Select all
public function getProducts($data = array()) {
if ($this->customer->isLogged()) {
$customer_group_id = $this->customer->getCustomerGroupId();
} else {
$customer_group_id = $this->config->get('config_customer_group_id');
}
// $cache = md5(http_build_query($data));
//$product_data = $this->cache->get('product.' . $cache . '.' . $customer_group_id);
// if (!$product_data) {
$sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)";
if (isset($data['filter_category_id']) && $data['filter_category_id']) {
$sql .= " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id)";
}
if (isset($data['filter_tag']) && $data['filter_tag']) {
$sql .= " LEFT JOIN " . DB_PREFIX . "product_tag pt ON (p.product_id = pt.product_id)";
}
$sql .= " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
if (isset($data['filter_name']) && $data['filter_name']) {
if (isset($data['filter_description']) && $data['filter_description']) {
$sql .= " AND (LCASE(pd.name) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%') OR p.product_id IN (SELECT pt.product_id FROM " . DB_PREFIX . "product_tag pt WHERE pt.language_id = '" . (int)$this->config->get('config_language_id') . "' AND LCASE(pt.tag) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%') OR LCASE(pd.description) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%')))";
} else {
$sql .= " AND (LCASE(pd.name) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%') OR p.product_id IN (SELECT pt.product_id FROM " . DB_PREFIX . "product_tag pt WHERE pt.language_id = '" . (int)$this->config->get('config_language_id') . "' AND LCASE(pt.tag) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%')))";
}
}
if (isset($data['filter_category_id']) && $data['filter_category_id']) {
if (isset($data['filter_sub_category']) && $data['filter_sub_category']) {
$implode_data = array();
$implode_data[] = $data['filter_category_id'];
$this->load->model('catalog/category');
$categories = $this->model_catalog_category->getCategoriesByParentId($data['filter_category_id']);
foreach ($categories as $category_id) {
$implode_data[] = "p2c.category_id = '" . (int)$category_id . "'";
}
$sql .= " AND (" . implode(' OR ', $implode_data) . ")";
} else {
$sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
}
}
if (isset($data['filter_tag']) && $data['filter_tag']) {
$sql .= " AND pt.language_id = '" . (int)$this->config->get('config_language_id') . "' AND LCASE(pt.tag) LIKE LCASE('%" . $this->db->escape($data['filter_tag']) . "%')";
}
if (isset($data['filter_manufacturer_id'])) {
$sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
}
$sql .= " GROUP BY p.product_id";
$sort_data = array(
'pd.name',
'p.model',
'p.quantity',
'p.price',
'rating',
'p.sort_order',
'p.date_added'
);
if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
$sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
} else {
$sql .= " ORDER BY " . $data['sort'];
}
} else {
$sql .= " ORDER BY p.sort_order";
}
if (isset($data['order']) && ($data['order'] == 'DESC')) {
$sql .= " DESC";
} else {
$sql .= " ASC";
}
if (isset($data['start']) || isset($data['limit'])) {
if ($data['start'] < 0) {
$data['start'] = 0;
}
if ($data['limit'] < 1) {
$data['limit'] = 20;
}
$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
}
$product_data = array();
$query = $this->db->query($sql);
foreach ($query->rows as $result) {
$product_data[$result['product_id']] = $this->getProduct($result['product_id']);
}
// $this->cache->set('product.' . $cache . '.' . $customer_group_id, $product_data);
//}
return $product_data;
}
Code: Select all
public function getTotalProducts($data = array()) {
$sql = "SELECT COUNT(DISTINCT p.product_id) AS total FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)";
if (isset($data['filter_category_id']) && $data['filter_category_id']) {
$sql .= " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id)";
}
if (isset($data['filter_tag']) && $data['filter_tag']) {
$sql .= " LEFT JOIN " . DB_PREFIX . "product_tag pt ON (p.product_id = pt.product_id)";
}
$sql .= " WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";
if (isset($data['filter_name'])) {
if (isset($data['filter_description']) && $data['filter_description']) {
$sql .= " AND (LCASE(pd.name) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%') OR p.product_id IN (SELECT pt.product_id FROM " . DB_PREFIX . "product_tag pt WHERE pt.language_id = '" . (int)$this->config->get('config_language_id') . "' AND LCASE(pt.tag) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%')) OR LCASE(pd.description) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%'))";
} else {
$sql .= " AND (LCASE(pd.name) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%') OR p.product_id IN (SELECT pt.product_id FROM " . DB_PREFIX . "product_tag pt WHERE pt.language_id = '" . (int)$this->config->get('config_language_id') . "' AND LCASE(pt.tag) LIKE LCASE('%" . $this->db->escape($data['filter_name']) . "%')))";
}
}
if (isset($data['filter_category_id']) && $data['filter_category_id']) {
if (isset($data['filter_sub_category']) && $data['filter_sub_category']) {
$implode_data = array();
$implode_data[] = "p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
$this->load->model('catalog/category');
$categories = $this->model_catalog_category->getCategoriesByParentId($data['filter_category_id']);
foreach ($categories as $category_id) {
$implode_data[] = "p2c.category_id = '" . (int)$category_id . "'";
}
$sql .= " AND (" . implode(' OR ', $implode_data) . ")";
} else {
$sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
}
}
if (isset($data['filter_tag']) && $data['filter_tag']) {
$sql .= " AND pt.language_id = '" . (int)$this->config->get('config_language_id') . "' AND LCASE(pt.tag) LIKE LCASE('%" . $this->db->escape($data['filter_tag']) . "%')";
}
if (isset($data['filter_manufacturer_id'])) {
$sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
}
$query = $this->db->query($sql);
return $query->row['total'];
}
OpenCart®
Project Owner & Developer.
made the changes and the site works great!!!Xsecrets wrote:yes that is the exact query that the vqmod addresses. It doesn't eliminate the count query, but it rolls it in to the model and caches the results.mixup wrote:oke im gonna try it, tnx
This is what the hosting company sended to me:
This query seems to be the problem:
SELECT COUNT( DISTINCT p.product_id ) AS total
FROM oc_product p
LEFT JOIN oc_product_description pd ON ( p.product_id = pd.product_id )
LEFT JOIN oc_product_to_store p2s ON ( p.product_id = p2s.product_id )
WHERE pd.language_id = '2'
AND p.status = '1'
AND p.date_available <= NOW( )
AND p2s.store_id = '0'
AND p.product_id
IN (
SELECT p2c.product_id
FROM oc_product_to_category p2c
WHERE p2c.category_id = '485'
)
tnx!
I got a godaddy VPS with over 3GB of ram, and have 2 opencart stores v.1.5.1.3 and v.1.5.2.1 runing.
the server has been very slow lately. the sever load is getting to 17.5... after contacting the hosting they said the MySql server is causing issue related to the 2 sites...
http://safirabeauty.com/ and
http://glamoroustore.com/
does anybody have any idea what could be causing this?
thanks
the server has been very slow lately. the sever load is getting to 17.5... after contacting the hosting they said the MySql server is causing issue related to the 2 sites...
http://safirabeauty.com/ and
http://glamoroustore.com/
does anybody have any idea what could be causing this?
thanks
The page is no longer working and i would like to have the solution to since i have the same problem...Daniel wrote:the solution is here:
http://code.google.com/p/opencart/source/detail?r=520
replace these files fromt he ones in the svn.
Hi Danniel , I am getting a similar error & response from my shared hosting provider.SELECT COUNT( DISTINCT p.product_id ) AS total
FROM product p
LEFT JOIN product_description pd ON ( p.p
Im using opencart 1.5.5.1 , SQL Version 5.5.31-cll , have disabled product count from admin > settings .
My hosting server has to say that the sql query is overloading the server & needs to be looked into immediately to avoid shut down of the site.
Please assist asap....
Thanks in advance.Saurav
Did this ever get resolved, seems like a heavy bug in opencart.
I have the exact same issue, but the issue it self seems to stem back years. Was it ever really fixed?
I am using 1.5.5.1 and have a website that is now not functional due to error.
This query SELECT COUNT(DISTINCT p.product_id) AS total FROM res_category_path cp LEFT JOIN res_product_to_cate is the problem.
Any guidance would be appreciated.
I have the exact same issue, but the issue it self seems to stem back years. Was it ever really fixed?
I am using 1.5.5.1 and have a website that is now not functional due to error.
This query SELECT COUNT(DISTINCT p.product_id) AS total FROM res_category_path cp LEFT JOIN res_product_to_cate is the problem.
Any guidance would be appreciated.
Who is online
Users browsing this forum: No registered users and 43 guests