Lange Such Abfrage (SQL) - 25 Sekunden

[Jörg Saxtec]

Hallo,

 

wir haben einen (neuen) Shop mit ca. 5000 Produkten im Test. 

 

Dieser ist auf einem VServer mit 4 Cores (3500 MHz) und 16GB RAM sowie einer 400 GB SSD untergebracht.

Bislang hoffte ich, dass diese Konfiguration für den neuen Shop ausreichen würde.

 

Jetzt haben wir jedoch Ladezeiten, bzw. SQL - Select Zeiten von 23 - 25 Sekunden. Das ist für die Besucher sicherlich nicht ganz so lustig.

 

So entsteht z.B. bei einer Suche nach "Bosch" folgende lange SQL Zeile. wenn ich diese in der Konsole (SSH / mysql Client) eingebe, sagt mir der Server: 50 rows in set (23.73 sec)

 

Meine Frage ist nun, kann man die Suche optimieren? Welche mySQL Server Einstellungen sind bei 16 GB RAM sinnvoll, um PrestaShop im produktiv einzusetzen? 

SELECT SQL_NO_CACHE 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`,
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 = 1
AND sw.id_shop = 1
AND si.id_product = p.id_product
AND (sw.word LIKE 'bosch%')
) position,
DATEDIFF(
p.`date_add`,
DATE_SUB(
"2016-08-02 00:00:00",
INTERVAL 1 DAY
)
) > 0 new, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity, IFNULL(product_attribute_shop.`id_product_attribute`,0) id_product_attribute
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` = 1 AND pl.id_shop = 1 
)
LEFT JOIN `ps_product_attribute_shop` product_attribute_shop
ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=1)
LEFT JOIN ps_stock_available stock
ON (stock.id_product = p.id_product AND stock.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_shop` image_shop
ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=1)
LEFT JOIN `ps_image_lang` il ON (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 1)
WHERE p.`id_product`  IN (4348,4354,567,573,574,589,590,591,592,593,614,617,622,624,627,628,632,642,643,644,646,647,4322,4327,4328,4329,4330,4331,4332,4333,4340,4341,4344,651,652,653,4315,4093,4101,4115,4117,4126,4131,4133,4154,4161,4162,4163,4164,4165,4166,4167,4168,4169,4182,4187,4188,4197,4198,4211,4212,4213,4214,4216,4224,4225,4226,4229,4234,4236,4237,4238,4239,4240,4241,4242,4243,4244,4245,4246,4248,4249,4250,4251,4252,4273,4278,4279,4285,4286,4293,4294,4296,4305,4308,458,461,462,1795,1796,1797,1798,1799,11,12,17,18,19,20,21,23,44,45,46,47,48,49,50,51,52,53,54,55,135,136,137,138,139,140,141,481,482,491,492,493,494,495,496,497,525,529,530,531,532,533,534,535,1818,1855,1856,1857,1865,1866,1867,1871,1875,1907,1908,1950,1951,1973,1974,1978,1979,1980,1981,1982,1983,1984,2006,2007,2008,2023,2024,2025,2035,2038,2043,2044,2053,2061,2063,2064,2065,2066,2067,2069,2073,2082,2097,2116,2126,2127,2128,2129,2130,2131,2132,2133,2135,2136,2158,2159,2160,2177,2178,2183,2213,2230,2231,2232,2233,2234,2253,2257,2258,2259,2260,2261,2262,2263,2264,2265,2266,2267,2268,2269,2270,2271,2273,2306,2308,2322,2323,2324,2341,2348,2349,2350,2351,2352,2353,2354,2355,2356,2367,2368,2369,2376,2388,2395,2396,2397,2398,2437,2438,2439,2440,2441,2442,2443,2444,2445,2446,2447,2448,2449,2450,2451,2452,2453,2456,2457,2458,2459,2474,2475,2476,2477,2478,2479,2480,2481,2482,2483,2484,2485,2486,2487,2488,2489,2490,2491,2492,2498,2499,2500,2501,2519,2520,2521,2522,2523,2525,2526,2528,2529,2530,2531,2532,2533,2534,2541,2542,2543,2544,2545,2546,2547,2548,2549,2550,2555,2562,2590,2591,2592,2594,2595,2600,2604,2605,2606,2607,2608,2609,2610,2611,2612,2613,2614,2636,2637,2638,2639,2642,2646,2652,2655,2656,2657,2658,2696,2697,2698,2728,2741,2742,2743,214,215,216,217,218,267,268,2792,2793,2807,2821,2823,2824,2825,2826,2827,2828,2829,2830,2831,2832,2833,2834,2835,2836,2837,2838,2843,2846,2847,2848,2849,2850,2851,2852,2853,2854,2855,2856,2857,2858,2859,2860,2871,2872,2882,2885,2886,2894,2895,2897,2898,2899,2900,2901,2902,2903,2904,2905,2906,2907,2908,2909,2910,2933,2943,2948,2951,2955,2964,2965,2966,2967,2968,2969,2976,2977,2978,2979,2980,2982,3000,3014,3015,287,288,289,296,297,298,299,300,3031,3054,3055,3056,3057,3058,3059,3060,3061,3062,3063,3065,3072,3074,3075,3076,3077,3078,3079,3089,3090,3095,3096,3112,319,3158,3161,3175,3176,3178,3180,3187,3188,3216,3217,3219,3220,3223,3224,3242,3258,3263,3264,3265,3271,3272,3286,3287,3288,3290,3296,3297,3301,3302,3303,3333,3334,3335,3336,3337,3338,3339,3340,3341,3342,3343,3344,3345,3346,3347,3348,3349,3350,3351,3388,3389,3416,3418,3445,3446,3447,3448,3453,3454,3455,3456,3457,3458,3459,3460,3461,3462,3464,3466,3467,3468,3481,3482,3483,3484,3485,3486,3487,3488,3489,3491,3492,3497,3498,3501,3502,3504,3511,3512,361,362,380,391,392,396,397,402,403,404,405,407,410,411,419,420,421,423,453,454,455,456,457,3520,3521,3524,3533,3534,3535,3544,3547,3548,3555,3556,3581,3582,3583,3584,3590,3591,3596,3600,3601,3602,3612,3613,3614,3623,3624,3625,3626,3627,3643,3645,3646,3651,3653,3658,3659,3664,3665,3666,3668,3680,3681,3684,3685,3692,3752,3753,3754,3764,3773,3774,3775,3776,3777,3778,3779,3780,3781,3786,3787,3801,3812,3816,3818,3823,3824,3828,3846,3852,3872,3885,3886,3887,3888,3900,3901,3902,3903,3904,3905,3906,3907,3908,3909,3910,3911,3912,3918,3943,3944,3945,3956,3957,3962,3963,3964,3965,3975,3976,3989,3990,3991,4014,4016,4020,4026,4027,4028,4031,4046,4047,4048,4049,4050,4051,4059,4067,4068,4069,4073,4074,679,680,682,683,684,685,686,687,688,692,695,696,761,762,763,764,769,773,788,792,793,794,798,799,800,801,802,803,820,821,822,838,868,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,922,961,962,963,964,965,966,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,991,992,993,994,995,996,997,998,1001,1010,1011,1012,1015,1018,1019,1020,1021,1022,1023,1024,1025,1026,1027,1028,1029,1030,1031,1041,1042,1043,1044,1045,1046,1050,1051,1052,1061,1063,1065,1066,1070,1071,1072,1073,1083,1084,1085,1102,1113,1119,1127,1128,1129,1130,1131,1132,1133,1145,1146,1147,1148,1149,1150,1151,1152,1153,1154,1155,1156,1157,1163,1164,1165,1166,1167,1171,1173,1198,1200,1201,1202,1203,1204,1205,1206,1207,1212,1216,1226,1227,1228,1229,1230,1231,1232,1233,1234,1235,1236,1237,1238,1244,1245,1256,1257,1258,1259,1260,1261,1662,1663) 
GROUP BY product_shop.id_product
ORDER BY  product_shop.price desc
LIMIT 0,50