Slow Queries debug

[Mat_D1245]

Hi,

we have lots problems with slow queries in our Prestashop.

We have a dedicated server, really fast. 

In our slow query log, I found queries like:

DELETE FROM ps_search_index WHERE id_product IN (13522,13522,13522,13522, .... ,13522,13522);

where the product ID is repeated 88.000 times (!), making the query run for over 3s.

We also have that one:

SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity,
				pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
			 MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` manufacturer_name ,(
				SELECT SUM(weight)
				FROM ps_search_word sw
				LEFT JOIN ps_search_index si ON sw.id_word = si.id_word
				WHERE sw.id_lang = 2
					AND sw.id_shop = 1
					AND si.id_product = p.id_product
					AND (sw.word LIKE '%neopren%')
			) position, MAX(product_attribute_shop.`id_product_attribute`) id_product_attribute,
				DATEDIFF(
					p.`date_add`,
					DATE_SUB(
						NOW(),
						INTERVAL 21 DAY
					)
				) > 0 new, MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity
				FROM ps_product p
				 INNER JOIN ps_product_shop product_shop
		ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
				INNER JOIN `ps_product_lang` pl ON (
					p.`id_product` = pl.`id_product`
					AND pl.`id_lang` = 2 AND pl.id_shop = 1 
				)
				LEFT JOIN `ps_product_attribute` pa	ON (p.`id_product` = pa.`id_product`)
				 LEFT JOIN ps_product_attribute_shop product_attribute_shop
		ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND product_attribute_shop.`default_on` = 1)
				 LEFT 
			JOIN ps_stock_available stock
			ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1  AND stock.id_shop_group = 0  )
				LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
				LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product`) LEFT JOIN ps_image_shop image_shop
		ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1)
				LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 2)
				WHERE p.`id_product`  IN (9118,9158,9169,4478,4479,4480,7618,7619,254,256,260,393,527,654,1322,1324,1325,1326,1471,1538,2609,2614,2615,2911,2924,3033,3036,3039,3040,3170,3171,3185,3318,3320,3321,3656,4299,4425,4902,4903,4936,4944,4990,4991,5006,5007,5010,5015,6180,6193,6194,6210,6214,6215,6223,6225,6246,6249,6260,6263,6264,6337,6400,6401,6404,6742,6743,6746,7109,7124,7704,7715,7798,7799,7806,7811,7815,7816,7819,7825,7826,7829,7834,7846,8104,8570,8954,9121,9129,9130,9612,9761,9787,9788,9815,9816,9929,9933,9935,9937,9941,9944,9954,9955,9959,11746,11886,11887,11888,11904,11905,11907,12500,12501,12503,12504,12599,12602,12603,12735,13206,13211,13212,13239,13344,13345,13354,1660,2328,2329,2372,2373,2376,2408,2409,2663,2904,2926,2969,2970,3487,3492,4317,4318,4421,4456,4458,4554,4854,4855,4856,4907,4911,4912,4913,4921,5196,5239,5240,5478,5479,5481,6111,6138,6139,6161,6162,6450,6737,7114,7115,7116,7117,7118,7122,7123,7207,7208,7209,7210,7682,7707,7720,7866,7867,7872,7873,8074,9045,9046,9715,9716,9838,11676,11678,11912,11914,11915,12876,12981,13054,4891,4898,6253,6254,6414,8739,8740,8741,10879,10881,10886,10887,11121,12001,12063,12064,13113,5354,5355,5356,6103,6681,6682,7129,7589,7852,7854,7856,7859,8320,8541,9078,9079,9081,9082,9083,9537,9568,9811,9984,10868,11631,11632,11634,11635,11636,11922,11923,13240,3870,3871,6845,12163,9610,13314,2656,3032,10869,5400,8420,961,998,2908,2943,3173,3617,3623,4287,4291,4424,5619,5632,6597,7345,7346,8355,8359,8360,8469,8472,9117,9131,9171,9351,9353,9357,9358,9950,9951,9952,9981,10034,10035,10036,10037,10924,10925,11792,11793,11948,11962,12016,12109,12528,12529,12530,12532,12533,12534,12535,13111,13339,13340,13341,13342,13343,1496,2187,3315,5574,5623,5629,7126,9133,9156,9359,9965,9966,9967,9968,12505,12506,12531,12536,12537,12538,12539,12540,12541,12542,12543,12544,8354,12427,78,79,220,9013,9014,9017,4069,4076,4080,4446,13226,8817,11823,4516,1671,5381,5534,6422,6425,6426,6427,6428,9150,10891,10967,10972,12549,12550,12571,12572,13289,1673,4939,4940,4941,6751,6752,6754,8156,8157,8158,8704,9084,9085,10043,10044,10045,10046,10939,10940,10941,12585,12587,12588,12589,12590,12591,12592,12593,12594,12595,12596,12597,12598,6429,11806,11807,12546,12548,12573,12574,12575,12576,12701,12702,12703,12704,6420,1489,2000,2001,4606,4716,4717,4718,4720,4992,5472,5493,5494,5571,7968,7971,7972,7973,7975,7976,7977,7978,8284,8285,8961,531,534,631,736,741,1313,1314,1315,1497,1498,3283,3314,5636,5830,6592,6593,6594,6595,7106,7250,7343,7344,7351,7571,7717,7718,7719,8361,8362,8403,8404,8405,8406,8407,8408,8413,8414,8415,8416,8418,9115,9116,9123,9157,9354,9355,9356,9874,9877,9887,9906,9907,9915,9916,10007,10008,10009,10010,10014,10015,10022,10024,10025,10027,10028,10029,10919,10920,10921,10922,10923,11790,11791,11889,11890,11891,11892,11893,11894,11895,11896,12108,12111,12498,12507,12508,12511,12517,12519,12525,12526,12527,13052,13053,13335,13336,13337,533,737,1495,2185,3028,3158,3589,5630,5631,5835,6248,7251,7350,7572,7573,7575,7577,7578,7629,9344,9345,11899,11961,12110,12521,9892,9897,12509,12513,12515,12523,11382,5271,8307,12727,13309,1909,1910,9505,9536,12069,12070,12071,12074,12075,12076,12079,12424,12432,13114,13203,13205,4693,4694,4695,7130,8147,11092,11093,11094,11786,11787,11788,11795,11796,11797,7058,9538,11031,11748,11749,12382,12434,12839,12917,13105,13106,9474,9475,3228,3229,3230,1807,1808,1809,1825,3244,6612,6616,6590,6702,8400,8401,9870,12455,13307,1490,4467,4994,4995,5292,5474,7313,7319,7321,7322,7581,7582,7583,7930,7959,7960,7962,7964,7965,7966,7967,7974,8086,8087,9346,9347,12607,12608,12609,12610,12611,12612,12613,12614,12615,12616,12617,13210,1491,3610,4742,4998,4999,8160,9608,9609,9613,10048,12068,12605,12606,13353,2225,5834,7110,7956,7957,9151,11204,11205,11906,11909,11910,12545,7579,8422,8956,11799,11963,11964,2605,4288,4303,4668,5018,5254,7335,7584,7703,7705,7706,8319,9349,10926,13312,4607,4608,4611,3872,11287,11288,11289,11291,11292,11293,11294,11295,11296,11297,11298,11299,11300,12162,763,3503,7620,7622,8538,8539,10049,10050,10051,11681,11810,11811,12657,12658,12659,12660,12661,12662,12663,13235,13346,13347,13348,13349,13350,7623,8540,12664,4315,4316,4488,7120,7585,7586,9660,9661,11680,11808,11809,11826,11827,11911,11916,11917,11949,12634,12635,12636,12637,12638,12639,12640,12641,12642,12643,12644,12645,12646,12647,12648,12649,12650,12651,12652,13055,13232,6402,7213,7625,7683,8004,11829,12655,12656,12691,12653,9997,12624,12631,12632,13471,13472,13473,84,762,4662,5714,5715,5717,6343,6347,6348,7203,7342,8088,8089,9164,9165,9985,9987,9988,9992,12618,12620,12622,12625,12626,12627,12628,12629,13469,13470,337,3916,3917,9080,693) 
				GROUP BY product_shop.id_product
				ORDER BY  position desc
				LIMIT 0,24;

that needed 11 seconds!

If anyone has an idea about what is going wrong here, please let me know!

[ietax]

sorry, have you solved? thanks