plugin overloads the database
« on: April 16, 2019, 10:29:11 PM »
Hello, hosting complains that the plugin overloads the database and blocks my domain.
Can you do something about it? The problem appeared after the last update.
Is possible back to ver 2.0.2?
Sample query log:

Query   25232   statistics      SELECT osc_t_item.*, osc_t_item.s_contact_name as s_user_name\nFROM (osc_t_item)\nLEFT OUTER  JOIN osc_t_item_alpha ON osc_t_item.pk_i_id = osc_t_item_alpha.fk_i_item_id\nINNER  JOIN osc_t_item_attribute as tbl_at_17_208 ON osc_t_item.pk_i_id = tbl_at_17_208.fk_i_item_id\nINNER  JOIN osc_t_item_attribute as tbl_at_17_209 ON osc_t_item.pk_i_id = tbl_at_17_209.fk_i_item_id\nINNER  JOIN osc_t_item_attribute as tbl_at_17_210 ON osc_t_item.pk_i_id = tbl_at_17_210.fk_i_item_id\nINNER  JOIN osc_t_item_attribute as tbl_at_17_211 ON osc_t_item.pk_i_id = tbl_at_17_211.fk_i_item_id\nINNER  JOIN osc_t_item_attribute as tbl_at_17_212 ON osc_t_item.pk_i_id = tbl_at_17_212.fk_i_item_id\nINNER  JOIN osc_t_item_attribute as tbl_at_17_213 ON osc_t_item.pk_i_id = tbl_at_17_213.fk_i_item_id\nINNER  JOIN osc_t_item_attribute as tbl_at_17_214 ON osc_t_item.pk_i_id = tbl_at_17_214.fk_i_item_id\nINNER  JOIN osc_t_item_attribute as tbl_at_17_215 ON osc_t_item.pk_i_id = tbl_at_17_215.fk_i_item_id\nINNER  JOIN osc_t_item_attribute as tbl_at_17_216 ON osc_t_item.pk_i_id = tbl_at_17_216.fk_i_item_id\nINNER  JOIN osc_t_item_attribute as tbl_at_17_217 ON osc_t_item.pk_i_id = tbl_at_17_217.fk_i_item_id\nINNER  JOIN osc_t_item_attribute as tbl_at_17_218 ON osc_t_item.pk_i_id = tbl_at_17_218.fk_i_item_id\nINNER  JOIN osc_t_item_attribute as tbl_at_17_219 ON osc_t_item.pk_i_id = tbl_at_17_219.fk_i_item_id\nINNER  JOIN osc_t_item_attribute as tbl_at_17_220 ON osc_t_item.pk_i_id = tbl_at_17_220.fk_i_item_id\nINNER  JOIN osc_t_item_attribute as tbl_at_17_221 ON osc_t_item.pk_i_id = tbl_at_17_221.fk_i_item_id\nINNER  JOIN osc_t_item_attribute as tbl_at_17_222 ON osc_t_item.pk_i_id = tbl_at_17_222.fk_i_item_id\nWHERE osc_t_item.b_enabled = 1 AND osc_t_item.b_active = 1 AND osc_t_item.b_spam = 0 AND (osc_t_item.b_premium = 1 || osc_t_item.dt_expiration >= '2019-04-16 13:57:10')\nAND osc_t_item.fk_i_category_id IN (17)\nAND  (tbl_at_17_208.fk_i_attribute_id = 17 AND tbl_at_17_208.fk_i_attribute_value_id in (208)) AND (tbl_at_17_209.fk_i_attribute_id = 17 AND tbl_at_17_209.fk_i_attribute_value_id in (209)) AND (tbl_at_17_210.fk_i_attribute_id = 17 AND tbl_at_17_210.fk_i_attribute_value_id in (210)) AND (tbl_at_17_211.fk_i_attribute_id = 17 AND tbl_at_17_211.fk_i_attribute_value_id in (211)) AND (tbl_at_17_212.fk_i_attribute_id = 17 AND tbl_at_17_212.fk_i_attribute_value_id in (212)) AND (tbl_at_17_213.fk_i_attribute_id = 17 AND tbl_at_17_213.fk_i_attribute_value_id in (213)) AND (tbl_at_17_214.fk_i_attribute_id = 17 AND tbl_at_17_214.fk_i_attribute_value_id in (214)) AND (tbl_at_17_215.fk_i_attribute_id = 17 AND tbl_at_17_215.fk_i_attribute_value_id in (215)) AND (tbl_at_17_216.fk_i_attribute_id = 17 AND tbl_at_17_216.fk_i_attribute_value_id in (216)) AND (tbl_at_17_217.fk_i_attribute_id = 17 AND tbl_at_17_217.fk_i_attribute_value_id in (217)) AND (tbl_at_17_218.fk_i_attribute_id = 17 AND tbl_at_17_218.fk_i_attribute_value_id in (218)) AND (tbl_at_17_219.fk_i_attribute_id = 17 AND tbl_at_17_219.fk_i_attribute_value_id in (219)) AND (tbl_at_17_220.fk_i_attribute_id = 17 AND tbl_at_17_220.fk_i_attribute_value_id in (220)) AND (tbl_at_17_221.fk_i_attribute_id = 17 AND tbl_at_17_221.fk_i_attribute_value_id in (221)) AND (tbl_at_17_222.fk_i_attribute_id = 17 AND tbl_at_17_222.fk_i_attribute_value_id in (222))\nORDER BY dt_pub_date desc\nLIMIT 0, 10

AND ( oc_t_item_location.fk_i_region_id = 781813  )
AND  (tbl_at_84_892.fk_i_attribute_id = 84 AND tbl_at_84_892.fk_i_attribute_value_id in (892)) AND (tbl_at_84_893.fk_i_attribute_id = 84 AND tbl_at_84_893.fk_i_attribute_value_id in (893)) AND (tbl_at_84_894.fk_i_attribute_id = 84 AND tbl_at_84_894.fk_i_attribute_value_id in (894)) AND (tbl_at_84_895.fk_i_attribute_id = 84 AND tbl_at_84_895.fk_i_attribute_value_id in (895)) AND (tbl_at_84_896.fk_i_attribute_id = 84 AND tbl_at_84_896.fk_i_attribute_value_id in (896)) AND (tbl_at_84_897.fk_i_attribute_id = 84 AND tbl_at_84_897.fk_i_attribute_value_id in (897)) AND (tbl_at_84_898.fk_i_attribute_id = 84 AND tbl_at_84_898.fk_i_attribute_value_id in (898)) AND (tbl_at_84_899.fk_i_attribute_id = 84 AND tbl_at_84_899.fk_i_attribute_value_id in (899)) AND (tbl_at_84_900.fk_i_attribute_id = 84 AND tbl_at_84_900.fk_i_attribute_value_id in (900)) AND (tbl_at_84_901.fk_i_attribute_id = 84 AND tbl_at_84_901.fk_i_attribute_value_id in (901)) AND (tbl_at_84_902.fk_i_attribute_id = 84 AND tbl_at_84_902.fk_i_attribute_value_id in (902)) AND (tbl_at_84_903.fk_i_attribute_id = 84 AND tbl_at_84_903.fk_i_attribute_value_id in (903)) AND (tbl_at_84_904.fk_i_attribute_id = 84 AND tbl_at_84_904.fk_i_attribute_value_id in (904)) AND (tbl_at_84_905.fk_i_attribute_id = 84 AND tbl_at_84_905.fk_i_attribute_value_id in (905)) AND (tbl_at_84_906.fk_i_attribute_id = 84 AND tbl_at_84_906.fk_i_attribute_value_id in (906)) AND (tbl_at_84_907.fk_i_attribute_id = 84 AND tbl_at_84_907.fk_i_attribute_value_id in (907)) AND (tbl_at_84_908.fk_i_attribute_id = 84 AND tbl_at_84_908.fk_i_attribute_value_id in (908))
ORDER BY i_price desc
LIMIT 0, 50 |

« Last Edit: April 16, 2019, 10:38:41 PM by Zbyszek Ochelski »

*

MB Themes

Re: plugin overloads the database
« Reply #1 on: April 16, 2019, 11:12:51 PM »
@Zbyszek Ochelski
Depends on settings, number of attributes etc. More attributes, more workload to database. Try to change settings for "Search engine", it may rewrite query.
  To get fast support, we need following details: Detail description, URL to reproduce problem, Screenshots

Re: plugin overloads the database
« Reply #2 on: April 18, 2019, 10:45:57 AM »
@Zbyszek Ochelski
Depends on settings, number of attributes etc. More attributes, more workload to database. Try to change settings for "Search engine", it may rewrite query.

can you be a little brighter? what settings are best, I do not have too many attributes and ads on one side and and so slows down.

*

jcarrolo01

  • ***
  • 114 posts
Re: plugin overloads the database
« Reply #3 on: April 18, 2019, 11:51:53 AM »
In my casse im filling car attributes... i fill everithing until ferrari... let say between 300 and 400 attributes...

I realize in admin when i save the first atttibutes im spending arround 3 secondd... now the latest atttibutes added to ferrari is spending arround 6.5 seconds..

In front side when i chosse an car model is spending arround 2/ 3 second and i dont feel there is a great increase in time

Im doing this in local host to decide what is the best way to implement the attributes..

The first way is have the car makes as categorry and only fill model as attributes..

The other way is add the car make as attribute and then car model as attribute also..

Lets see wich have best perfomance

*

MB Themes

Re: plugin overloads the database
« Reply #4 on: April 18, 2019, 01:43:17 PM »
That is way too much attributes, try cache plugin.
  To get fast support, we need following details: Detail description, URL to reproduce problem, Screenshots