Post by mixup » Fri Jul 29, 2011 5:25 pm

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

Newbie

Posts

Joined
Fri Jul 29, 2011 5:17 pm

Post by Daniel » Fri Jul 29, 2011 7:36 pm

split putting the products in to around 500 at a time.

its nothing to do with opencart if you importing 11,000 products.


11,000 proudcts will run fine on opencart.

OpenCart®
Project Owner & Developer.


User avatar
Administrator

Posts

Joined
Fri Nov 03, 2006 6:57 pm

Post by mixup » Fri Jul 29, 2011 8:46 pm

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.

Newbie

Posts

Joined
Fri Jul 29, 2011 5:17 pm

Post by Xsecrets » Fri Jul 29, 2011 9:19 pm

mixup 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.
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#p171445

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by mixup » Fri Jul 29, 2011 9:45 pm

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'
)

Newbie

Posts

Joined
Fri Jul 29, 2011 5:17 pm

Post by Xsecrets » Fri Jul 29, 2011 10:14 pm

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'
)
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.

OpenCart commercial mods and development http://spotonsolutions.net
Layered Navigation
Shipment Tracking
Vehicle Year/Make/Model Filter


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by Daniel » Fri Jul 29, 2011 10:16 pm

working on a solution now.

OpenCart®
Project Owner & Developer.


User avatar
Administrator

Posts

Joined
Fri Nov 03, 2006 6:57 pm

Post by Daniel » Fri Jul 29, 2011 10:28 pm

the solution is here:

http://code.google.com/p/opencart/source/detail?r=520

replace these files fromt he ones in the svn.

OpenCart®
Project Owner & Developer.


User avatar
Administrator

Posts

Joined
Fri Nov 03, 2006 6:57 pm

Post by Xsecrets » Fri Jul 29, 2011 11:42 pm

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 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.

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


Guru Member

Posts

Joined
Sun Oct 25, 2009 3:51 am
Location - FL US

Post by Daniel » Sat Jul 30, 2011 12:50 am

its not using group its using COUNT(DISTINCT p.product_id)

OpenCart®
Project Owner & Developer.


User avatar
Administrator

Posts

Joined
Fri Nov 03, 2006 6:57 pm

Post by Daniel » Sat Jul 30, 2011 12:51 am

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;
	}


and

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.


User avatar
Administrator

Posts

Joined
Fri Nov 03, 2006 6:57 pm

Post by mixup » Sat Jul 30, 2011 9:34 pm

Xsecrets wrote:
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'
)
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.
made the changes and the site works great!!!
tnx!

Newbie

Posts

Joined
Fri Jul 29, 2011 5:17 pm

Post by jfn99 » Fri Apr 13, 2012 1:09 am

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

New member

Posts

Joined
Thu Feb 25, 2010 5:14 pm

Post by jfn99 » Fri Apr 13, 2012 2:26 am

I guess this is the SQL query causing the load

SELECT COUNT( DISTINCT p.product_id ) AS total
FROM product p
LEFT JOIN product_description pd ON ( p.p

New member

Posts

Joined
Thu Feb 25, 2010 5:14 pm

Post by Solan » Sat Oct 20, 2012 6:40 am

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.
The page is no longer working and i would like to have the solution to since i have the same problem... :(

Active Member

Posts

Joined
Sat Feb 19, 2011 7:34 pm

Post by Solan » Wed Feb 13, 2013 9:01 pm

No answer since October.. :(

Still have problem solwing this issue when the link is not working

Active Member

Posts

Joined
Sat Feb 19, 2011 7:34 pm

Post by Daniel » Sat Feb 16, 2013 6:48 pm

upgrade to the latest. it handles categories a lot better.

OpenCart®
Project Owner & Developer.


User avatar
Administrator

Posts

Joined
Fri Nov 03, 2006 6:57 pm

Post by tvmc_chennai » Thu Jul 04, 2013 7:43 pm

SELECT COUNT( DISTINCT p.product_id ) AS total
FROM product p
LEFT JOIN product_description pd ON ( p.p
Hi Danniel , I am getting a similar error & response from my shared hosting provider.

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

Newbie

Posts

Joined
Sun Apr 14, 2013 2:22 am

Post by adamylo » Tue Oct 01, 2013 8:05 am

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.

Newbie

Posts

Joined
Mon Apr 16, 2012 7:07 pm

Post by adamylo » Wed Oct 02, 2013 6:12 pm

Any ideas guys? I can not seem to fix this issue?

Newbie

Posts

Joined
Mon Apr 16, 2012 7:07 pm
Who is online

Users browsing this forum: No registered users and 5 guests