Very slow category loading time

[dithegrey]

Hello.
I have a very big problem - loading time to display 10(!) products from a selected category is about a minute!
I've digged into slow querys on server and found loads of this(for example):

Code Select Expand

SET timestamp=1382698980;
SELECT SQL_CALC_FOUND_ROWS  l.`virtuemart_product_id` FROM `zs1x6_virtuemart_products_ru_ru` as l JOIN `zs1x6_virtuemart_products` AS p using (`virtuemart_product_id`) LEFT JOIN `zs1x6_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id`
LEFT JOIN `zs1x6_virtuemart_categories_ru_ru` as c ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id` LEFT JOIN `zs1x6_virtuemart_product_shoppergroups` ON p.`virtuemart_product_id` = `zs1x6_virtuemart_product_shoppergroups`.`virtuemart_product_id`
LEFT  OUTER JOIN `zs1x6_virtuemart_shoppergroups` as s ON s.`virtuemart_shoppergroup_id` = `zs1x6_virtuemart_product_shoppergroups`.`virtuemart_shoppergroup_id` LEFT OUTER JOIN `zs1x6_virtuemart_products` children ON p.`virtuemart_product_id` = children.`product_parent_id`  WHERE ( p.`published`="1"  AND  (p.`product_in_stock` - p.`product_ordered` >"0" OR children.`product_in_stock` - children.`product_ordered` > "0")  AND  `pc`.`virtuemart_category_id` = 188 AND  ( s.`virtuemart_shoppergroup_id`= "2"  OR s.`virtuemart_shoppergroup_id` IS NULL  ) )  group by p.`virtuemart_product_id`  ORDER BY product_name ASC LIMIT 0, 10;

As I understand - it is problem with SQL_CALC_FOUND_ROWS.. But how to sovle that?

P.S. Also before that - i had a problem that products wont even be displayed, it was showing me this:

Code Select Expand

vmError: exeSortSearchListQuery The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay SQL=SELECT SQL_CALC_FOUND_ROWS l.`virtuemart_product_id` FROM `zs1x6_virtuemart_products_ru_ru` as l JOIN `zs1x6_virtuemart_products` AS p using (`virtuemart_product_id`) LEFT JOIN `zs1x6_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` LEFT JOIN `zs1x6_virtuemart_categories_ru_ru` as c ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id` LEFT JOIN `zs1x6_virtuemart_product_shoppergroups` ON p.`virtuemart_product_id` = `zs1x6_virtuemart_product_shoppergroups`.`virtuemart_product_id` LEFT OUTER JOIN `zs1x6_virtuemart_shoppergroups` as s ON s.`virtuemart_shoppergroup_id` = `zs1x6_virtuemart_product_shoppergroups`.`virtuemart_shoppergroup_id` LEFT OUTER JOIN `zs1x6_virtuemart_products` children ON p.`virtuemart_product_id` = children.`product_parent_id` WHERE ( p.`published`="1" AND (p.`product_in_stock` - p.`product_ordered` >"0" OR children.`product_in_stock` - children.`product_ordered` > "0") AND `pc`.`virtuemart_category_id` = 194 AND ( s.`virtuemart_shoppergroup_id`= "2" OR s.`virtuemart_shoppergroup_id` IS NULL ) ) group by p.`virtuemart_product_id` ORDER BY product_name ASC LIMIT 0, 10
I solved it by putting this code

Code Select Expand

$db=& JFactory::getDBO();
$db->setQuery("SET OPTION SQL_BIG_SELECTS=1");
$db->query(); in virtuemart.php right after:

Code Select Expand

<?php
if( !defined( '_JEXEC' ) ) die( 'Direct Access to '.basename(__FILE__).' is not allowed.' );

Please help me, because site must be startet up soon. Thank you.
Also - we are using shared hosting, even though the database are on ssd.

[jjk]

Can you supply a link to your shop, so somebody can have a look?

[dithegrey]

Can you supply a link to your shop, so somebody can have a look?

Here is the site http://ponigifts-ru.1gb.ru/
Cart is disabled at the moment for security reasons.
You can try going to any category, or do the search.
Also - i wish to know - where product listing query for category is created? i mean which file. I cant find it.

[jjk]

There are many things which can slow down a website. Looking at your case, one of the first things I would check are the settings on your Microsoft-IIS/6.0 server. Maybe it is something simple like in this post: http://forums.iis.net/t/1163225.aspx

There are a few other settings which might save you a few seconds of loading time. I would suggest to disable loading the external Google fonts (probably loaded by your template) and the either the Google jQuery or the VirtueMart jQuery (personally I prefer to switch off the jQuery from googleapis.com. At present you are loading two different jquery versions.

Also you should fix errors like this one: "NetworkError: 404 Not Found - http://ponigifts-ru.1gb.ru/templates/ot_kitchen/images/main-bg.png"
Perhaps you can see some more info about what causes the slow loading time if you enable 'Debug System' in Joomla and have a look at the 'Profile information'.

Do you use by chance sh404sef?

[dithegrey]

There are many things which can slow down a website. Looking at you case, one of the first things I would check are the settings on your Microsoft-IIS/6.0 server. Maybe it is something simple like in this post: http://forums.iis.net/t/1163225.aspx

There are a few other settings which might save you a few seconds of loading time. I would suggest to disable loading the external Google fonts (probably loaded by your template) and the either the Google jQuery or the VirtueMart jQuery (personally I prefer to switch off the jQuery from googleapis.com. At present you are loading two different jquery versions.

Also you should fix errors like this one: "NetworkError: 404 Not Found - http://ponigifts-ru.1gb.ru/templates/ot_kitchen/images/main-bg.png"
Perhaps you can see some more info about what causes the slow loading time if you enable 'Debug System' in Joomla and have a look at the 'Profile information'.

Do you use by chance sh404sef?

As I said - we are using shared hosting, Apache based.
I disabled sef404.
Still loading time is slow.
I used debug - the problem is with slow virtuemart query.
Can you please tell me in wich file query is created (for front end product listing aka categories)

[Milbo]

exeSortSearchListQuery The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

Says clearly that your my.ini/cnf is configured too restricted. You need a bit more ressources

[dithegrey]

Well...
I solved the problem.
It was the problem with the calculation.
You see - I disabled not showing unavailable products - and know query is super fast, beacuse calculation was disabled this way.
I just want to know - why the calculation is done using query. It is very slow.

Code Select Expand

(p.`product_in_stock` - p.`product_ordered` >"0" OR children.`product_in_stock` - children.`product_ordered` > "0")
Is there a way to calculate without the query?