Shop is very slow everywhere i click

Hi,

How many products and categories do you have ?
Displaying a 100 products a already quite a lot. When you do that it needs to load the product data, the images, the prices, the badges, the variants, the options, the custom fields etc for each product. So it can amount to a lot of queries, but also a lot of data to process in PHP. It's not the same as displaying the product entries from only one table via PHPMyAdmin.
But there are ways to improve it I suppose. First, correctly configuring the MySQL server to have the tables in RAM is a good idea if not already done. Also, adding indexes to the database might be necessary based on how the listings and filters are configured in order to speed up the queries.
Analyzing the queries done on your pages would be necessary to understand what's slowing down the process. It can be done by turning on the "debug mode" setting of the Joomla configuration.

Also, caching can be activated in HikaShop to further speed up the display of the pages.

Hi,

I've checked and here is what I said for reference:

You have a really simple filter so it should be able to handle a few 100 000 products with maybe a 1 second processing time on a normal server (it's basically the limit of the MySQL query processing by your MySQL server here and since the query isn't complex, it should be quite fast).

Hi,

Great, we're moving forward !
So, here is for example a page:
take.ms/cHC3x
You can see that the MySQL queries takes 13 seconds, while the total processing of the page is 14 seconds.
So almost everything is eaten up by queries to the database.
Then, if you browse through the queries there, you can see this query:
take.ms/nNhkR
Only this query eats by itself 10 seconds of the processing.
Would it be possible to get an access to your phpmyadmin in order to run some tests with that query on the database in order to understand what is the exact drag on that query?
Maybe adding an index somewhere will solve the issue but without your data in the tables, it's hard to give a definitive answer.

If that's ok, you can provide it with a link to this thread via our contact form:
www.hikashop.com/support/contact-us.html

You can also turn off the debug mode for now.

Sorry for the delay. I've had a lot on my plate in the last few weeks and such kind of debugging takes a lot of time.
I'm going to look at the issue this afternoon and get back to you.

So I've run some tests on your SQL server.

The query

SELECT DISTINCT b.*
FROM jos_hikashop_product_category AS a
LEFT JOIN jos_hikashop_product AS b 
ON a.product_id=b.product_id 
WHERE b.product_published=1
AND b.product_type='main'
AND a.category_id IN (42)
AND (b.product_access = 'all' OR b.product_access LIKE '%,1,%')
ORDER BY ordering ASC
LIMIT 0,100

Hi,

If you try the query on your phpmyadmin with the ordering, you can see that it takes more than 3secs. And if you remove the ordering it takes less than 1ms. So the ordering is the problem for that specific query.

Removing the categories might help, but that's not the main issue.

The filters performances will depend on how the filters are configured and what the data is.
For maximum performances, instead of a brand filter, I would recommend a custom product field filter.
That way, no JOIN is required as the fitlering will be done directly with the product column on the hikashop_product table.
For the search on the name, the issue is the same as what I explained previously with the ordering. Removing the ordering should help.
If that's not enough, removing the categories JOIN will help. It can be done by modifying the code:

if(empty($select)){
			$parentCategories = implode(',',$pageInfo->filter->cid);
			$catName = 'a.category_id';
			$type = 'product';

			if(!empty($element->category_type) && $element->category_type=='manufacturer'){
				if($pageInfo->filter->order->value=='a.ordering' || $pageInfo->filter->order->value=='b.ordering'){
					$pageInfo->filter->order->value='b.product_name';
				}
				$type = 'manufacturer';
				$catName = 'b.product_manufacturer_id';
				$b = '';
				$a = hikashop_table('product').' AS b';
				$on = '';
				$select='SELECT DISTINCT b.*';
			}else{
				if($pageInfo->filter->order->value=='b.ordering'){
					$pageInfo->filter->order->value='a.ordering';
				}
				$b = hikashop_table('product_category').' AS a LEFT JOIN ';
				$a = hikashop_table('product').' AS b';
				$on = ' ON a.product_id=b.product_id';
				$select='SELECT DISTINCT b.*';
			}
			if($this->params->get('filter_type',2)==2){
				$defaultParams = $config->get('default_params');
				$this->params->set('filter_type',$defaultParams['filter_type']);
			}
			if(!$this->params->get('filter_type')){
				if(!empty($parentCategories)&& $parentCategories!='0') $filters[]=$catName.' IN ('.$parentCategories.')';
			}else{
				$categoryClass->parentObject =& $this;
				$categoryClass->type = $type;

				$children = $categoryClass->getChildren($pageInfo->filter->cid,true,array(),'',0,0);
				//
				// Depending the number of $children, we can determine the number of categories (without filter), check if we have the same number and not put the filter in this case.
				// We can also use an "NOT IN" if there are a lot of $children and the diff between $children and $allcategories gave a few number of categories.
				//
				$filter = $catName.' IN (';
				foreach($children as $child){
					$filter .= $child->category_id.',';
				}
				$filters[]=$filter.$parentCategories.')';
			}
		}

Hi,

When you search by name, the problem of the query is the left join to the product_category table. That's why I proposed removing the that code which handles the join.
So yes, we're talking about the same thing.

As I said in a previous message, I'm talking about the file components/com_hikashop/views/product/view.html.php in the listing function. That's where the query loading the products is done and that's the one causing you the slow page processing. So that's what you want to modify.

You already provided a backend access to the website and I was able to check your settings already, so it is not necessary to provide it again.