News:

Support the VirtueMart project and become a member

Main Menu

Virtuemart datebase very slow

Started by geisti, January 16, 2019, 14:56:24 PM

Previous topic - Next topic

geisti

hey guys,

since yesterday our shop is working very slow. we are using joomla 3.9.2 , virtuemart 3.4.2 and php 7.1

we talked to our hoster and he send us the following logfile of a query. we hope someone can help us to fix this issue

Quotedatabase1234        localhost       datebase1234 Query   7       Sending data    SELECT SQL_CALC_FOUND_ROWS p.`virtuemart_product_id` , IF(pp.override, pp.product_override_price, pp.product_price) as product_price FROM `jos34_virtuemart_products` as p LEFT JOIN `jos34_virtuemart_product_shoppergroups` as ps ON p.`virtuemart_product_id` = `ps`.`virtuemart_product_id` LEFT JOIN `jos34_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` LEFT JOIN `jos34_virtuemart_product_prices` as pp ON p.`virtuemart_product_id` = pp.`virtuemart_product_id` WHERE ( ((p.`product_parent_id` = "0" AND `pc`.`virtuemart_category_id` > "0") OR p.`product_parent_id` > "0") AND ((p.`product_parent_id` > "0" AND `pc`.`virtuemart_category_id` > "0") OR p.`product_parent_id` = "0") AND ( `ps`.`virtuemart_shoppergroup_id`= "1" OR `ps`.`virtuemart_shoppergroup_id` IS NULL ) AND p.`published`="1" AND p.`virtuemart_product_id`!=599 AND p.`virtuemart_product_id`!=3378 AND p.`virtuemart_product_id`!=3392
AND p.`virtuemart_product_id`!=3394 AND p.`virtuemart_product_id`!=5776 AND p.`virtuemart_product_id`!=8547 AND p.`virtuemart_product_id`!=8573 AND p.`virtuemart_product_id`!=8574 AND p.`virtuemart_product_id`!=13588 AND p.`virtuemart_product_id`!=13589 AND p.`virtuemart_product_id`!=13601 AND p.`virtuemart_product_id`!=13602 AND p.`virtuemart_product_id`!=9299 AND p.`virtuemart_product_id`!=9300 AND p.`virtuemart_product_id`!=1393 AND p.`virtuemart_product_id`!=3073 AND p.`virtuemart_product_id`!=8578 AND p.`virtuemart_product_id`!=8580 AND p.`virtuemart_product_id`!=8583 AND p.`virtuemart_product_id`!=8585 AND p.`virtuemart_product_id`!=8588 AND p.`virtuemart_product_id`!=8649 AND p.`virtuemart_product_id`!=8651 AND p.`virtuemart_product_id`!=8653 AND p.`virtuemart_product_id`!=8655 AND p.`virtuemart_product_id`!=8657 AND p.`virtuemart_product_id`!=10958 AND p.`virtuemart_product_id`!=12420 AND p.`virtuemart_product_id`!=13343 AND p.`virtuemart_product_id`!=1572 AND p.`virtuemart_
product_id`!=13344 AND p.`virtuemart_product_id`!=13402 AND p.`virtuemart_product_id`!=13345 AND p.`virtuemart_product_id`!=1498 AND p.`virtuemart_product_id`!=13404 AND p.`virtuemart_product_id`!=1499 AND p.`virtuemart_product_id`!=8106 AND p.`virtuemart_product_id`!=1423 AND p.`virtuemart_product_id`!=1424 AND p.`virtuemart_product_id`!=9294 AND p.`virtuemart_product_id`!=9295 AND p.`virtuemart_product_id`!=1500 AND p.`virtuemart_product_id`!=764 AND p.`virtuemart_product_id`!=2652 AND p.`virtuemart_product_id`!=2658 AND p.`virtuemart_product_id`!=2671 AND p.`virtuemart_product_id`!=2753 AND p.`virtuemart_product_id`!=1420 AND p.`virtuemart_product_id`!=1421 AND p.`virtuemart_product_id`!=882 AND p.`virtuemart_product_id`!=2301 AND p.`virtuemart_product_id`!=2364 AND p.`virtuemart_product_id`!=2369 AND p.`virtuemart_product_id`!=2552 AND p.`virtuemart_product_id`!=2564 AND p.`virtuemart_product_id`!=2570 AND p.`virtuemart_product_id`!=2665 AND p.`virtuemart_product_id`!=10038 AND
p.`virtuemart_product_id`!=2312 AND p.`virtuemart_product_id`!=8062 AND p.`virtuemart_product_id`!=8061 AND p.`virtuemart_product_id`!=737 AND p.`virtuemart_product_id`!=11050 AND p.`virtuemart_product_id`!=883 AND p.`virtuemart_product_id`!=13098 AND p.`virtuemart_product_id`!=13112 AND p.`virtuemart_product_id`!=13113 AND p.`virtuemart_product_id`!=13449 AND p.`virtuemart_product_id`!=6575 AND p.`virtuemart_product_id`!=2531 AND p.`virtuemart_product_id`!=6574 AND p.`virtuemart_product_id`!=6573 AND p.`virtuemart_product_id`!=6572 AND p.`virtuemart_product_id`!=1064 AND p.`virtuemart_product_id`!=1065 AND p.`virtuemart_product_id`!=1066 AND p.`virtuemart_product_id`!=12320 AND p.`virtuemart_product_id`!=2371 AND p.`virtuemart_product_id`!=2543 AND p.`virtuemart_product_id`!=10207 AND p.`virtuemart_product_id`!=2749 AND p.`virtuemart_product_id`!=8064 AND p.`virtuemart_product_id`!=8066 AND p.`virtuemart_product_id`!=8077 AND p.`virtuemart_product_id`!=8094 AND p.`virtuemart_produc
t_id`!=884 AND p.`virtuemart_product_id`!=6558 AND p.`virtuemart_product_id`!=6571 AND p.`virtuemart_product_id`!=6557 AND p.`virtuemart_product_id`!=1598 AND p.`virtuemart_product_id`!=12602 AND p.`virtuemart_product_id`!=12918 AND p.`virtuemart_product_id`!=12948 AND p.`virtuemart_product_id`!=13114 AND p.`virtuemart_product_id`!=6556 AND p.`virtuemart_product_id`!=2324 AND p.`virtuemart_product_id`!=2485 AND p.`virtuemart_product_id`!=2534 AND p.`virtuemart_product_id`!=2571 AND p.`virtuemart_product_id`!=2615 AND p.`virtuemart_product_id`!=2667 AND p.`virtuemart_product_id`!=2670 AND p.`virtuemart_product_id`!=9061 AND p.`virtuemart_product_id`!=10208 AND p.`virtuemart_product_id`!=10217 AND p.`virtuemart_product_id`!=3611 AND p.`virtuemart_product_id`!=2679 AND p.`virtuemart_product_id`!=2720 AND p.`virtuemart_product_id`!=2777 AND p.`virtuemart_product_id`!=2783 AND p.`virtuemart_product_id`!=8055 AND p.`virtuemart_product_id`!=8074 AND p.`virtuemart_product_id`!=8076 AND p.`v
irtuemart_product_id`!=6567 AND p.`virtuemart_product_id`!=1067 AND p.`virtuemart_product_id`!=1071 AND p.`virtuemart_product_id`!=1072 AND p.`virtuemart_product_id`!=1073 AND p.`virtuemart_product_id`!=1074 AND p.`virtuemart_product_id`!=6570 AND p.`virtuemart_product_id`!=2319 AND p.`virtuemart_product_id`!=2392 AND p.`virtuemart_product_id`!=2435 AND p.`virtuemart_product_id`!=2547 AND p.`virtuemart_product_id`!=2612 AND p.`virtuemart_product_id`!=2693 AND p.`virtuemart_product_id`!=2695 AND p.`virtuemart_product_id`!=1061 AND p.`virtuemart_product_id`!=9063 AND p.`virtuemart_product_id`!=8109 AND p.`virtuemart_product_id`!=2728 AND p.`virtuemart_product_id`!=2791 AND p.`virtuemart_product_id`!=2833 AND p.`virtuemart_product_id`!=8050 AND p.`virtuemart_product_id`!=8075 AND p.`virtuemart_product_id`!=8095 AND p.`virtuemart_product_id`!=8802 AND p.`virtuemart_product_id`!=8808 AND p.`virtuemart_product_id`!=8814 AND p.`virtuemart_product_id`!=9918 AND p.`virtuemart_product_id`!=11
051 AND p.`virtuemart_product_id`!=271 AND p.`virtuemart_product_id`!=765 AND p.`virtuemart_product_id`!=10902 AND p.`virtuemart_product_id`!=12276 AND p.`virtuemart_product_id`!=12299 AND p.`virtuemart_product_id`!=12300 AND p.`virtuemart_product_id`!=12414 AND p.`virtuemart_product_id`!=12415 AND p.`virtuemart_product_id`!=12638 AND p.`virtuemart_product_id`!=12639 AND p.`virtuemart_product_id`!=8056 AND p.`virtuemart_product_id`!=6568 AND p.`virtuemart_product_id`!=6569 AND p.`virtuemart_product_id`!=7967 AND p.`virtuemart_product_id`!=2809 AND p.`virtuemart_product_id`!=6712 AND p.`virtuemart_product_id`!=2358 AND p.`virtuemart_product_id`!=2439 AND p.`virtuemart_product_id`!=2441 AND p.`virtuemart_product_id`!=2508 AND p.`virtuemart_product_id`!=2516 AND p.`virtuemart_product_id`!=2666 AND p.`virtuemart_product_id`!=2675 AND p.`virtuemart_product_id`!=5613 AND p.`virtuemart_product_id`!=10181 AND p.`virtuemart_product_id`!=372 AND p.`virtuemart_product_id`!=4236 AND p.`virtuema
rt_product_id`!=2386 AND p.`virtuemart_product_id`!=2584 AND p.`virtuemart_product_id`!=2678 AND p.`virtuemart_product_id`!=2756 AND p.`virtuemart_product_id`!=2800 AND p.`virtuemart_product_id`!=2806 AND p.`virtuemart_product_id`!=2817 AND p.`virtuemart_product_id`!=2846 AND p.`virtuemart_product_id`!=8063 AND p.`virtuemart_product_id`!=8096 AND p.`virtuemart_product_id`!=8385 AND p.`virtuemart_product_id`!=1409 AND p.`virtuemart_product_id`!=1501 AND p.`virtuemart_product_id`!=11872 AND p.`virtuemart_product_id`!=12522 AND p.`virtuemart_product_id`!=12713 AND p.`virtuemart_product_id`!=12919 AND p.`virtuemart_product_id`!=13178 AND p.`virtuemart_product_id`!=13179 AND p.`virtuemart_product_id`!=13381 AND p.`virtuemart_product_id`!=8057 AND p.`virtuemart_product_id`!=2737 AND p.`virtuemart_product_id`!=6579 AND p.`virtuemart_product_id`!=1417 AND p.`virtuemart_product_id`!=6713 AND p.`virtuemart_product_id`!=2502 AND p.`virtuemart_product_id`!=2505 AND p.`virtuemart_product_id`!=56
14 AND p.`virtuemart_product_id`!=4240 AND p.`virtuemart_product_id`!=8093 AND p.`virtuemart_product_id`!=8386 AND p.`virtuemart_product_id`!=8809 AND p.`virtuemart_product_id`!=531 AND p.`virtuemart_product_id`!=1075 AND p.`virtuemart_product_id`!=1076 AND p.`virtuemart_product_id`!=6555 AND p.`virtuemart_product_id`!=12281 AND p.`virtuemart_product_id`!=12310 AND p.`virtuemart_product_id`!=12499 AND p.`virtuemart_product_id`!=12521 AND p.`virtuemart_product_id`!=12545 AND p.`virtuemart_product_id`!=13073 AND p.`virtuemart_product_id`!=6578 AND p.`virtuemart_product_id`!=6835 AND p.`virtuemart_product_id`!=6836 AND p.`virtuemart_product_id`!=6837 AND p.`virtuemart_product_id`!=6838 AND p.`virtuemart_product_id`!=6839 AND p.`virtuemart_product_id`!=6840 AND p.`virtuemart_product_id`!=6841 AND p.`virtuemart_product_id`!=6842 AND p.`virtuemart_product_id`!=6843 AND p.`virtuemart_product_id`!=6862 AND p.`virtuemart_product_id`!=6554 AND p.`virtuemart_product_id`!=2304 AND p.`virtuemart
_product_id`!=2618 AND p.`virtuemart_product_id`!=5615 AND p.`virtuemart_product_id`!=10182 AND p.`virtuemart_product_id`!=2799 AND p.`virtuemart_product_id`!=8101 AND p.`virtuemart_product_id`!=532 AND p.`virtuemart_product_id`!=6792 AND p.`virtuemart_product_id`!=885 AND p.`virtuemart_product_id`!=841 AND p.`virtuemart_product_id`!=6715 AND p.`virtuemart_product_id`!=9289 AND p.`virtuemart_product_id`!=12520 AND p.`virtuemart_product_id`!=12823 AND p.`virtuemart_product_id`!=13040 AND p.`virtuemart_product_id`!=13110 AND p.`virtuemart_product_id`!=2550 AND p.`virtuemart_product_id`!=2636 AND p.`virtuemart_product_id`!=5616 AND p.`virtuemart_product_id`!=10209 AND p.`virtuemart_product_id`!=10256 AND p.`virtuemart_product_id`!=4241 AND p.`virtuemart_product_id`!=8102 AND p.`virtuemart_product_id`!=733 AND p.`virtuemart_product_id`!=1069 AND p.`virtuemart_product_id`!=6527 AND p.`virtuemart_product_id`!=6480 AND p.`virtuemart_product_id`!=6481 AND p.`virtuemart_product_id`!=6482 AND
  p.`virtuemart_product_id`!=8561 AND p.`virtuemart_product_id`!=12283 AND p.`virtuemart_product_id`!=12284 AND p.`virtuemart_product_id`!=12285 AND p.`virtuemart_product_id`!=12286 AND p.`virtuemart_product_id`!=12287 AND p.`virtuemart_product_id`!=12288 AND p.`virtuemart_product_id`!=12289 AND p.`virtuemart_product_id`!=12290 AND p.`virtuemart_product_id`!=12411 AND p.`virtuemart_product_id`!=12412 AND p.`virtuemart_product_id`!=12413 AND p.`virtuemart_product_id`!=12531 AND p.`virtuemart_product_id`!=12645 AND p.`virtuemart_product_id`!=12648 AND p.`virtuemart_product_id`!=12649 AND p.`virtuemart_product_id`!=12650 AND p.`virtuemart_product_id`!=12651 AND p.`virtuemart_product_id`!=12652 AND p.`virtuemart_product_id`!=6714 AND p.`virtuemart_product_id`!=2388 AND p.`virtuemart_product_id`!=2572 AND p.`virtuemart_product_id`!=2581 AND p.`virtuemart_product_id`!=2694 AND p.`virtuemart_product_id`!=2719 AND p.`virtuemart_product_id`!=10235 AND p.`virtuemart_product_id`!=6577 AND p.`vi
rtuemart_product_id`!=4128 AND p.`virtuemart_product_id`!=9199 AND p.`virtuemart_product_id`!=9648 AND p.`virtuemart_product_id`!=7961 AND p.`virtuemart_product_id`!=272 AND p.`virtuemart_product_id`!=533 AND p.`virtuemart_product_id`!=543 AND p.`virtuemart_product_id`!=6576 AND p.`virtuemart_product_id`!=6483 AND p.`virtuemart_product_id`!=6484 AND p.`virtuemart_product_id`!=6485 AND p.`virtuemart_product_id`!=6716 AND p.`virtuemart_product_id`!=6717 AND p.`virtuemart_product_id`!=1068 AND p.`virtuemart_product_id`!=2556 AND p.`virtuemart_product_id`!=2575 AND p.`virtuemart_product_id`!=2586 AND p.`virtuemart_product_id`!=2587 AND


GJC Web Design

GJC Web Design
VirtueMart and Joomla Developers - php developers https://www.gjcwebdesign.com
VM4 AusPost Shipping Plugin - e-go Shipping Plugin - VM4 Postcode Shipping Plugin - Radius Shipping Plugin - VM4 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
https://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

geisti

ok. i switched every omit to no. i will watch it some hours

geisti

i would say it solved my problem.


btw whats that omit for?

GJC Web Design

As I understand it if u had say only 5 products in a shop and enabled featured, latest, top ten etc etc then effectively u would just show the same products repeatedly.
So the omit attempts to NOT show the same products in each display
I guess it isn't necessary as soon as you have a reasonable number of products in your shop
GJC Web Design
VirtueMart and Joomla Developers - php developers https://www.gjcwebdesign.com
VM4 AusPost Shipping Plugin - e-go Shipping Plugin - VM4 Postcode Shipping Plugin - Radius Shipping Plugin - VM4 NZ Post Shipping Plugin - AusPost Estimator
Samport Payment Plugin - EcomMerchant Payment Plugin - ccBill payment Plugin
VM2 Product Lock Extension - VM2 Preconfig Adresses Extension - TaxCloud USA Taxes Plugin - Virtuemart  Product Review Component
https://extensions.joomla.org/profile/profile/details/67210
Contact for any VirtueMart or Joomla development & customisation

geisti

okay thanks. we have nearly 15 k products in our shop