Bug or Installation/update issue VM3.0.12 ??

[Otto0815]

Hi,

i just updateted my site to Joomla 3.4.5 & Virtuemart 3.0.12 (clean Install & copied old Database & executed "Install/repair databasefields" in vm configuration tab)
Then everything runs fine, exept the product_details pages wich took about 30+ seconds to load.
Since i could't find the culprit i contacted my serverprovider and he found the following *.sql query as the problem:

Code Select Expand

SELECT p.`virtuemart_product_id`, `l`.`product_name`,`pc`.ordering
FROM `k3rbu_virtuemart_products` as p
INNER JOIN `k3rbu_virtuemart_products_de_de` as l using (`virtuemart_product_id`)
LEFT JOIN `k3rbu_virtuemart_product_shoppergroups` as ps ON p.`virtuemart_product_id` = `ps`.`virtuemart_product_id` 
LEFT JOIN `k3rbu_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` 
LEFT JOIN `k3rbu_virtuemart_categories` as c ON c.`virtuemart_category_id` = `pc`.`virtuemart_category_id` 
LEFT OUTER JOIN `k3rbu_virtuemart_products` children ON p.`virtuemart_product_id` = children.`product_parent_id` 
WHERE ( ( (p.`product_in_stock` - p.`product_ordered`) >"0"
OR (children.`product_in_stock` - children.`product_ordered`) > "0") 
AND  `pc`.`virtuemart_category_id` = 1187
AND  `c`.`published` = 1 
AND  ( `ps`.`virtuemart_shoppergroup_id`= "1"  OR `ps`.`virtuemart_shoppergroup_id` IS NULL  ) 
AND  p.`published`="1" )
AND p.`virtuemart_product_id`!="11865" 
AND `pc`.ordering <= "0" 
ORDER BY `pc`.`ordering` DESC, `virtuemart_product_id` DESC LIMIT 1

His solution was to set some new indexes:

ALTER TABLE  `k3rbu_virtuemart_products` ADD INDEX (  `product_in_stock` )
ALTER TABLE  `k3rbu_virtuemart_products` ADD INDEX (  `product_ordered` )
ALTER TABLE  `k3rbu_virtuemart_product_categories` ADD INDEX (  `virtuemart_category_id` )
ALTER TABLE  `k3rbu_virtuemart_product_shoppergroups` ADD INDEX (  `virtuemart_shoppergroup_id` )


So is this a bug, or did i run in some installtion/override issues?
I've had this Problem on my 2 testinstalltions also....

Greets,,

Otto

[GJC Web Design]

I'm sure Max will comment on this when he gets time..

I was just going to say you should give your very helpful and proactive service provider the publicity he/they deserves..
I work with so many appallingly crap hosting services who idea of service is an automated email...

[Otto0815]

I'm sure Max will comment on this when he gets time..

I was just going to say you should give your very helpful and proactive service provider the publicity he/they deserves..
I work with so many appallingly crap hosting services who idea of service is an automated email...

You are right....it's All-Inkl.com from Germany and they're always doing a perfect job regarding support 24/7.....i'm there now since 7 years

[GJC Web Design]

Ah .. know them well.. have clients there...  good to hear

the only reason I don't use them is a lack of cPanel...  it just makes everything so much easier

[Milbo]

Interesting, all-inkl.com is also my favorite for small german pages :-) if bigger, I prefer hetzner.

We can add this indexes
ALTER TABLE  `k3rbu_virtuemart_products` ADD INDEX (  `product_in_stock` )
ALTER TABLE  `k3rbu_virtuemart_products` ADD INDEX (  `product_ordered` )

Looks like I work to often without stock checks, so I did not notice the performance problem here. The other two keys should be without effect, because we have already unique keys there.
UNIQUE KEY `virtuemart_product_id` (`virtuemart_product_id`,`virtuemart_category_id`),