Performance on showing products in category

-- HikaShop version -- : 2.6.0

I have a performance issue with displaying products in specific category.
And I notice a major difference in showing them in backend (fast) and frontend (timeout).

Now the server I'm running this on right now, will not be the one that will be running production, so I still have the choice of CPU / Memory power to choose from, but as I see this major difference, I'm wondering if there is something I can tune to have the items show faster in the front end as well.

In Backend, I use product page, select the proper category and shortly after that I get the list of products.
When I do the same through the frontend I get a timeout and notice mysqld at 100% CPU for still some time after receiving the timeout.

What could be the major factor for the difference, and can I tune that ?

NOTE: I only added apprx 3% of the category-product relations already resulting in 27000 rows in jos_hikashop_product_category table.
EDIT: NOTE2: I added the relationships directly into the database. So ordering field is '0' for all those products

Addition to the above.......

Increased php max_execution_time to 300. Page loads in around 65 seconds.....

I also noticed issues in the backend.
See attachements
In CategoryView you can see there is are categories for Aprilia - 1000 - 2009
In ProductView you can see the result after clicking the '+' for the 2009 category... no sub-categories displayed.

This issue does not arise for all categories thow
Rebuilding categories does not help (used that button numerous times already)

Another addition to the performance issue......
If a category is empty, the page shows almost instantly. I assume that it still queries the hikashop_category_product table. So the delay is most likely caused on another level.....
However I added some more info into the table and load times increase to 160 sec. which is way too slow......

I moved my shop to another (faster) server, as we were interfering with some other website... (whoops).
Moved it to the new and desired (dedicated) server for the shop and new url : www.1stopmotoronderdelen.nl
You logons are the same on there.
As performance already increased dramatically on the new server I added some more data to the category-product relations. Still not all data is added, but page load already at 18 sec

(The backend issue still exists)

(I'm going to figure out why my pictures on products and subcategories are not showing in the new situation )

Quickly checked the query times myself.
The query taking the longest is also executed multiple times. (Without knowing the code, it is executed for each product that is being shown).

SELECT hk_parent.*
FROM jos_hikashop_category AS hk_cat
LEFT JOIN jos_hikashop_category AS hk_parent
ON (hk_parent.category_left <= hk_cat.category_left
AND hk_parent.category_right >= hk_cat.category_right)
WHERE hk_cat.category_id IN ( ... list of categories ...)
GROUP BY hk_parent.category_id
ORDER BY hk_parent.category_left

And, with the amount of categories I'm using, this takes a very long time. Wondering why it is querying this, so perhaps I can turn off some.

Also not sure if my other topic also had its influence on these page load times. The current page load times appear to be much quicker since I changed the 'Best Rated' module (see topic TOPIC: Donot show 'best rated' products on product page)

Thanks for looking into it with me.....
Performance did increase a lot on the new server, and after my other issue worked around.
But note that currently there is only 1 person looking at the site. (Perhaps 2 at once).
Look e.g. at www.1stopmotoronderdelen.nl/index.php/mo...v-transalp-pd06-pd10 22s. database queries.
(Not sure what is expected as acceptable from your end)

I think I found out what is causing load times to be high. Now figuring out how I could decrease that.
SITUATION:
I have a lot of categories, and products are part of 1 or more categories. I found out that one of the products was part of almost all the categories. (Generic product). I removed that product from all specific categories, and load time of the category/product page increased.
So the biggest delay is caused by querying the hikashop_product_category. Now it appears, when looking at the query, it does query for stuff that is related to the other categories a product is part of. Why ? I can't see why it should do that, so can't figure out what to turn off to increase load times even more....

Just another update.
Some database tuning also helped with my queries.... still not exactly to my likings. (Plans are to grow the number of products by approximately 10fold... or more... in the next year or 2, so better worry about the performance now ).

Obviously the more data, the longer it will take.
Current server is 8 core & 16 GB Memory.

However, I'm still figuring out why the mentioned query is run, as I can't see any use for the results of the query... (yet)....
Obviously, i only have "all direct sub elements" shown in the final category. So the final category will show all products. But the query that is the slowest, does not show me any info required for that final page (or at least I don't see the requirement yet, so can't see why it is run, or what I could turn of to avoid it running)

I have provided login data ealier this week, so you could logon and take a look. (Debugging is turned off again at this point)

Correction... 12.000 products !!! Not 200..
And the number of products will grow soon with large numbers when adding more suppliers. (Estimate to have about 50.000 products by the end of the year)
If you look at the site, you can see the requirement for the huge number of categories (about 2600 categories that have products, and a lot more in order to navigate to those individual categories, as there is no way I could filter on category custom fields.). And each category holds some number of products ranging from 1 to 60 at this point.

I know at the moment that perfmance is fairly good and definately acceptable, but I'm already looking forward.

To bring it down to the basic question I have:
When opening a category page that has products assigned to it, why does it run the "SELECT hk_parent.*" query for each product trying to get ALL other categories a product is part of. That information is (for what I can see and understand now) not required to display the page. Because when looking at the debug information, that is the query that is run multiple times and takes the most of time, depending on the number of categories a product is part of.

Hi,

This request is used for the Breadcrumb.
It allow to get all the parent categories and fill the breadcrumb correctly.

If you don't use breadcrumb or you use the simplified one, you can potentially edit the file "components/com_hikashop/views/product/view.html.php"

And in the "'listing()" function replace the code:

				$categoryClass = hikashop_get('class.category');
				$pathway = $app->getPathway();
				$category_pathway = '&'.$pathway_sef_name.'='.JRequest::getVar('menu_main_category');
				$categories = $categoryClass->getParents(reset($pageInfo->filter->cid));
				$one = true;
				if(!empty($categories)){
					foreach($categories as $category){
						if($one){
							$one = false;
						}else{
							$categoryClass->addAlias($category);
							$pathway->addItem($category->category_name,hikashop_completeLink('category&task=listing&cid='.(int)$category->category_id.'&name='.$category->alias));
						}
					}
				}

$category_pathway = '';

			$one = true;
			if(is_array($categories)){
				foreach($categories as $category){
					if($one){
						$one = false;
					}
					else{
						$class->addAlias($category);
						$alias = $category->alias;
						$pathway->addItem($category->category_name,hikashop_completeLink('category&task=listing&cid='.(int)$category->category_id.'&name='.$alias.$menu_id));
					}
				}
			}

Thnx, I will look into this once I find the time......