Bonjour,
J'aurai besoin d'un coup de main svp. Je rencontre un problème avec mon nouveau site lors du chargement des catégories de la liste produit.
Je vous résume le problème , nous avons un filtre sur notre site , celui ci met un temps différent pour charger à chaque fois , cela peut aller de 400 ms à 8 secondes pour le chargement.
Le module en question fonctionne très bien , nous en avons donc déduit avec mon webmaster qu'il y avait un problème dans la base données.
Voici toutes les infos :
(Je vous ai mis également en pièce jointe l'éxécution de la requete)
IP du hosting reseller : 51.68.113.151
Version PHP 7.2.31
Serveur de base de données :
Serveur : Localhost via UNIX socket
Type de serveur : MariaDB
Connexion au serveur : SSL n'est pas utilisé Documentation
Version du serveur : 10.0.38-MariaDB-0+deb8u1 - (Debian)
Version du protocole : 10
Utilisateur : modadiandrea@localhost
Jeu de caractères du serveur : cp1252 West European (latin1)
PhpMyAdmin version 4.9.0.1
Informations du site :
· Version de PrestaShop : 1.7.6.4
· URL de la boutique : http://modadiandrea.com/
· Chemin de la boutique : /var/www/vhosts/modadiandrea.com/httpdocs
· Thème actif : at_movic
Requête SQL qui parfois est lente :
SELECT o.id_option, count(distinct p.id_product) AS total_products
FROM `ps_product` p
INNER JOIN `ps_product_shop` `p_shop` ON p_shop.id_product = p.id_product AND p_shop.id_shop = 1
LEFT JOIN `ps_manufacturer` `m` ON m.id_manufacturer = p.id_manufacturer
INNER JOIN `ps_category_product` `cp` ON cp.id_product = p.id_product
INNER JOIN `ps_category_group` `cg` ON cg.id_category = cp.id_category AND cg.id_group = 1
INNER JOIN `ps_category_product` `cpro_p` ON cpro_p.id_product = p_shop.id_product
INNER JOIN `ps_category_shop` `cshop_p` ON cshop_p.id_category = cpro_p.id_category AND cshop_p.id_shop = 1
LEFT JOIN `ps_product_attribute_shop` `pas` ON pas.id_product = p_shop.id_product AND p_shop.id_shop = 1
INNER JOIN `ps_fpp_index_prices` `ip` ON ip.id_product = p.id_product AND ip.id_shop = p_shop.id_shop
INNER JOIN `ps_stock_available` `stock_0` ON stock_0.id_product = p.id_product AND stock_0.id_product_attribute = IFNULL(pas.id_product_attribute, 0)
INNER JOIN `ps_fpp_option` `o` ON o.id_table = cshop_p.id_category OR cshop_p.id_category IN (SELECT ch.id_category
FROM `ps_category` ch
INNER JOIN `ps_category_shop` `csh` ON csh.id_category = ch.id_category AND csh.id_shop = 1
WHERE (ch.nleft >= (SELECT c.nleft
FROM `ps_category` c
INNER JOIN `ps_category_shop` `cs` ON cs.id_category = c.id_category AND cs.id_shop = 1
WHERE (c.id_category = o.id_table)
) and ch.nright <= (SELECT c.nright
FROM `ps_category` c
INNER JOIN `ps_category_shop` `cs` ON cs.id_category = c.id_category AND cs.id_shop = 1
WHERE (c.id_category = o.id_table)
))
)
INNER JOIN `ps_category_group` `cg_o` ON cg_o.id_category = o.id_table AND cg_o.id_group = 1
WHERE (p_shop.active = 1) AND (p_shop.visibility IN ("both", "catalog")) AND (p_shop.id_product IN (202,207,209,293,297,322,338,395,397,400,406,413,414,417,419,420,423,424,425,426,427,428,434,435,437,444,445,453,454,455,457,466,478,483,484,495,499,502,505,515,516,517,521,522,523,524,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,548,549,550,551,552,553,554,555,556,557,558,559,560,561,564,565,569,572,573,577,578,579,586,587,588,589,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,614,615,616,619,620,623,644,667,680,698,1133,1145,1146,1261,1263,1268,1269,1270,1275,1277,1278,1279,1280,1281,1282,1285,1286,1287,1288,1291,1292,1300,1302,1304,1308,1315,1317,1318,1329,1331,1333,1334,1336,1337,1345,1346,1347,1348,1350,1351,1429,1458,1459,1460,1461,1462,1463,1464,1465,1466,1467,1468,1469,1470,1471,1472,1473,1474,1475,1476,1477,1478,1479,1480,1481,1482,1483,1484,1486,1487,1488,1489,1490,1491,1492,1493,1494,1495,1496,1497,1498,1499,1500,1501,1502,1503,1504,1505,1506,1507,1508,1509,1510,1511,1512,1513,1514,1515,1516,1517,1518,1519,1520,1521,1522,1523,1524,1525,1526,1527,1537,1538,1539,1540,1541,1542,1543,1549,1553,1554,1559,1561,1562,1563,1564,1589,1597,1599,1609,1617,1623,1635,1685,1705,1718,1744,1747,1758,1785,1805,1809,1810,1817,1826,1832,1857,1872,1876,1896,1917,1924,1930,1965,1969,1979,1984,1994,2002,2073,2076,2078,2082,2084,2090,2094,2098,2099,2109,2116,2119,2125,2126,2130,2154,2159,2169,2170,2173,2177,2190,2204,2212,2215,2216,2226,2231,2235,2237,2239,2269,2274,2283,2286,2292,2296,2300,2309,2312,2315,2318,2321,2344,2347,2350,2356,2359,2361,2373,2391,2400,2408,2411,2414,2424,2427,2428,2429,2430,2431,2432,2435,2436,2437,2438,2514,2515,2516,2517,2518,2519,2520,2521,2522,2523,2524,2525,2526,2527,2528,2529,2530,2531,2540,2541,2542,2543,2544,2545,2546,2547,2548,2549,2550,2551,2552,2553,2871,2872,2873,2874,2876,3010,3104,3105,3106,3107,3108,3109,3110,3111,3112,3113,3114,3115,3116,3117,3118,3137,3143,3144,3152,3153,3157,3158,3161,3168,3169,3170,3171,3173,3174,3175,3176,3177,3178,3179,3180,3181,3182,3183,3184,3185,3186,3187,3188,3189,3190,3191,3192,3193,3194,3195,3196,3197,3198,3200,3201,3202,3203,3204,3206,3207,3208,3209,3211,3212,3214,3215,3217,3218,3219,3220,3221,3222,3223,3224,3225,3226,3227,3231,3237,3238,3239,3240,3241,3242,3243,3244,3245,3246,3247,3248,3249,3279,3322,3328,3329,3330,3331,3332,3333,3334,3335,3342,3343,3344,3345,3346,3347,3348,3349,3350,3351,3352,3353,3354,3355,3356,3357,3358,3359,3360,3361,3362,3363,3364,3365,3366,3367,3371,3372,3376,3384,3385,3386,3392,3393,3400,3401,3402,3403,3404,3405,3406,3407,3408,3409,3410,3411,3412,3413,3414,3416,3417,3419,3420,3421,3422,3423,3424,3425,3426,3427,3429,3430,3431,3432,3433,3434,3435,3436,3437,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3462,3463,3464,3465,3466,3467,3468,3472,3474,3475,3482,3493,3494,3495,3496,3497,3498,3499,3500,3501,3502,3503,3504,3505,3506,3507,3514,3516,3517,3518,3519,3520,3521,3522,3523,3524,3526,3527,3530,3532,3533,3534,3535,3536,3537,3538,3539,3559,3561,3569,3570,3586,3587,3588,3594,3595,3598,3603,3609,3610,3611,3612,3614,3618,3619,3620,3621,3623,3624,3625,3626,3627,3628,3629,3630,3631,3632,3633,3634,3636,3643,3644,3645,3646,3647,3648,3649,3650,3651,3652,3653,3654,3655,3670,3671,3672,3673,3674,3675,3676,3677,3678,3679,3680,3681,3682,3683,3684,3685,3686,3687,3688,3689,3690,3691,3692,3693,3707,3708,3709,3710,3711,3712,3713,3714,3716,3722,3723,3725,3726,3727,3728,3729,3730,3731,3732,3733,3734,3735,3736,3737,3738,3739,3746,3747,3748,3749,3750,3751,3753,3754,3755,3756,3757,3758,3759,3760,3761,3762,3763,3764,3765,3766,3767,3768,3769,3770,3771,3772,3774,3775,3776,3777,3778,3780,3781,202,207,209,293,297,322,338,395,397,400,406,413,414,417,419,420,423,424,425,426,427,428,434,435,437,444,445,453,454,455,457,466,478,483,484,495,499,502,505,515,516,517,521,522,523,524,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,548,549,550,551,552,553,554,555,556,557,558,559,560,561,564,565,569,572,573,577,578,579,586,587,588,589,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,614,615,616,619,620,623,644,1133,1145,1146,1261,1263,1268,1269,1270,1275,1277,1278,1279,1280,1281,1282,1285,1286,1287,1288,1291,1292,1300,1302,1304,1308,1315,1317,1318,1329,1331,1333,1334,1336,1337,1345,1346,1347,1348,1350,1351,1429,1458,1459,1460,1461,1462,1463,1464,1465,1466,1467,1468,1469,1470,1471,1472,1473,1474,1475,1476,1477,1478,1479,1480,1481,1482,1483,1484,1486,1487,1488,1489,1490,1491,1492,1493,1494,1495,1496,1497,1498,1499,1500,1501,1502,1503,1504,1505,1506,1507,1508,1509,1510,1511,1512,1513,1514,1515,1516,1517,1518,1519,1520,1521,1522,1523,1524,1525,1526,1527,1537,1538,1539,1540,1541,1542,1543,1549,1553,1554,1559,1561,1562,1563,1564,1589,1597,1599,1609,1617,1623,1635,1685,1705,1718,1744,1747,1758,1785,1805,1809,1810,1817,1826,1832,1857,1872,1876,1896,1917,1924,1930,1965,1969,1979,1984,1994,2002,2073,2076,2078,2082,2084,2090,2094,2098,2099,2109,2116,2119,2125,2126,2130,2154,2159,2169,2170,2173,2177,2190,2204,2212,2215,2216,2226,2231,2235,2237,2239,2269,2274,2283,2286,2292,2296,2300,2309,2312,2315,2318,2321,2344,2347,2350,2356,2359,2361,2373,2391,2400,2408,2411,2414,2424,2427,2428,2429,2430,2431,2432,2435,2436,2437,2438,2514,2515,2516,2517,2518,2519,2520,2521,2522,2523,2524,2525,2526,2527,2528,2529,2530,2531,2540,2541,2542,2543,2544,2545,2546,2547,2548,2549,2550,2551,2552,2553,2871,2872,2873,2874,2876,3010,3104,3105,3106,3107,3108,3109,3110,3111,3112,3113,3114,3115,3116,3117,3118,3137,3143,3144,3152,3153,3157,3158,3161,3168,3169,3170,3171,3173,3174,3175,3176,3177,3178,3179,3180,3181,3182,3183,3184,3185,3186,3187,3188,3189,3190,3191,3192,3193,3194,3195,3196,3197,3198,3200,3201,3202,3203,3204,3206,3207,3208,3209,3211,3212,3214,3215,3217,3218,3219,3220,3221,3222,3223,3224,3225,3226,3227,3231,3237,3238,3239,3240,3241,3242,3243,3244,3245,3246,3247,3248,3249,3279,3322,3328,3329,3330,3331,3332,3333,3334,3335,3342,3343,3344,3345,3346,3347,3348,3349,3350,3351,3352,3353,3354,3355,3356,3357,3358,3359,3360,3371,3372,3376,3384,3385,3386,3392,3393,3400,3401,3402,3403,3404,3405,3406,3407,3408,3409,3410,3411,3412,3413,3414,3416,3417,3419,3420,3421,3422,3423,3424,3425,3426,3427,3429,3430,3431,3432,3433,3434,3435,3436,3437,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3462,3463,3464,3465,3466,3467,3468,3472,3474,3475,3482,3493,3494,3495,3496,3497,3498,3499,3500,3501,3502,3503,3504,3505,3506,3507,3514,3516,3517,3518,3519,3520,3521,3522,3523,3524,3526,3527,3536,3537,3538,3539,3569,3570,3586,3587,3588,3594,3595,3598,3618,3619,3620,3621,3623,3624,3625,3626,3627,3628,3629,3630,3631,3632,3633,3634,3636,3643,3644,3645,3646,3647,3648,3649,3650,3651,3652,3653,3654,3655,3670,3671,3672,3673,3674,3675,3676,3677,3678,3679,3680,3681,3682,3683,3684,3685,3686,3687,3688,3689,3690,3691,3692,3693,3707,3708,3709,3710,3711,3712,3713,3714,3716,3722,3723,3725,3726,3727,3728,3729,3730,3731,3732,3733,3734,3735,3736,3737,3738,3739,3746,3747,3748,3749,3750,3751,3753,3754,3755,3756,3757,3758,3759,3760,3761,3762,3763,3764,3765,3766,3767,3768,3769,3770,3771,3772,3774,3775,3776,3777,3778,3780,3781,444,445,209,425,435,521,522,523,524,569,592,597,598,608,609,611,612,1317,1521,1522,1527,3392,3734,3750,297,406,453,454,457,1133,1145,1261,1285,1286,1287,1288,1291,1292,1300,1302,1304,1308,1315,1318,1329,1331,1333,1334,1429,1458,1459,1460,1461,1462,1463,1464,1465,1466,1467,1468,1469,1470,1471,1472,1473,1474,1475,1476,1477,1478,1479,1480,1481,1482,1483,1484,1486,1487,1488,1489,1490,1491,1492,1493,1494,1495,1496,1497,1498,1499,1500,1501,1502,1503,1504,1505,1506,1507,1549,1561,1562,1563,1589,1597,1599,1609,1617,1623,1635,1685,1705,1718,1744,1758,1785,1817,1826,1832,1857,1872,1876,1896,1917,2002,2076,2082,2094,2098,2099,2109,2119,2130,2154,2159,2169,2170,2177,2190,2212,2215,2216,2231,2235,2239,2269,2274,2296,2411,2431,2432,2540,3104,3105,3106,3107,3108,3109,3110,3111,3112,3137,3231,3328,3329,3330,3331,3376,3384,3462,3463,3465,3466,3467,3468,3472,3474,3482,3569,3570,3588,3636,3655,3670,3671,3672,3673,3674,3675,3676,3677,3678,3679,3680,3775,395,437,577,578,1346,1347,2424,455,548,550,553,586,591,1146,2876,3161,338,426,427,428,434,483,484,515,516,517,531,556,557,558,559,560,572,573,587,588,593,594,595,596,600,601,602,603,1269,1277,1278,1282,1348,1523,1524,1525,1537,1810,2300,2315,2318,2321,2438,2525,3347,3348,3408,3409,3410,3411,3412,3464,3594,3595,3633,3646,3647,3732,3733,3735,3736,3748,3749,3751,3756,3757,3759,3760,3766,3768,3769,3770,3777,599,1279,1345,1351,1509,2437,2553,3113,3372,3524,419,420,423,466,505,545,546,549,564,607,1510,1511,1512,1519,1520,1526,3393,3475,202,207,293,322,413,551,552,554,565,604,610,644,1263,1268,1275,1280,1281,1336,1337,1350,1508,1965,2116,2126,2237,2344,2347,2391,2400,2428,2435,2436,2514,2515,2516,2517,2518,2519,2520,2521,2522,2523,2524,2526,2527,2871,2872,2873,2874,3114,3143,3144,3152,3153,3322,3332,3333,3334,3335,3342,3343,3344,3345,3346,3349,3350,3351,3352,3353,3354,3355,3356,3357,3358,3359,3360,3371,3400,3401,3402,3403,3404,3405,3406,3407,3413,3414,3416,3417,3419,3420,3421,3422,3423,3424,3425,3522,3523,3618,3619,3620,3621,3623,3624,3625,3626,3627,3628,3629,3630,3631,3632,3634,3648,3649,3650,3651,3652,3681,3682,3683,3684,3685,3686,3687,3688,3689,3690,3691,3708,3709,3710,3711,3712,3727,3728,3729,3730,3731,3737,3738,3739,3747,3753,3754,3758,3761,3762,3763,3764,3765,3767,3771,3772,3774,3780,3781,3010,3539,3586,3587,589,397,400,414,417,424,478,495,499,502,532,533,534,535,536,537,538,539,540,541,542,543,544,555,561,579,605,606,614,615,616,619,620,623,1270,1513,1514,1515,1516,1517,1518,1538,1539,1540,1541,1542,1543,1553,1554,1559,1564,1747,1805,1809,1924,1930,1969,1979,1984,1994,2073,2078,2084,2090,2125,2173,2204,2226,2283,2286,2292,2309,2312,2350,2356,2359,2361,2373,2408,2414,2427,2429,2430,2528,2529,2530,2531,2541,2542,2543,2544,2545,2546,2547,2548,2549,2550,2551,2552,3115,3116,3117,3118,3157,3158,3168,3169,3170,3171,3173,3174,3175,3176,3177,3178,3179,3180,3181,3182,3183,3184,3185,3186,3187,3188,3189,3190,3191,3192,3193,3194,3195,3196,3197,3198,3200,3201,3202,3203,3204,3206,3207,3208,3209,3211,3212,3214,3215,3217,3218,3219,3220,3221,3222,3223,3224,3225,3226,3227,3237,3238,3239,3240,3241,3242,3243,3244,3245,3246,3247,3248,3249,3279,3385,3386,3426,3427,3429,3430,3431,3432,3433,3434,3435,3436,3437,3438,3439,3440,3441,3442,3443,3444,3445,3446,3447,3493,3494,3495,3496,3497,3498,3499,3500,3501,3502,3503,3504,3505,3506,3507,3514,3516,3517,3518,3519,3520,3521,3526,3527,3536,3537,3538,3598,3643,3644,3645,3653,3654,3692,3693,3707,3713,3714,3716,3722,3723,3725,3726,3746,3755,3776,3778,3559,3561,3603,3559,3561,3603,667,680,698,3361,3362,3363,3364,3365,3366,3367,3530,3532,3534,3535,3609,3610,3611,3612,3614,667,3534,698,680,3530,3532,3609,3610,3611,3612,3614,3533,3535,3533,3535)) AND (ip.id_group = 1) AND (ip.id_currency = 1) AND (ip.id_country = 8) AND (stock_0.quantity > 0) AND (o.id_filter = 2) AND (o.active = 1)
GROUP BY o.id_option
ORDER BY o.column, o.sort
Fichier dans lequel la requête est appelé :
/httpdocs/modules/filterproductspro/classes/SearcherManagerClass.php
Fonction qui appelle la requête :
getCategoryFilterOptionsId
Cordialement
Alexandre
Serveurs Privés Virtuels (VPS) - Requête SQL trop lente, 8 secondes
Willkommen in der OVHcloud Community
Stellen Sie Fragen, suchen Sie nach Informationen, veröffentlichen Sie Inhalte und interagieren Sie mit anderen Mitgliedern der OVHcloud Community.
Frage
Requête SQL trop lente, 8 secondes
Von
Erstellungsdatum 2020-07-06 07:06:40 (edited on 2024-09-04 13:46:02) in Serveurs Privés Virtuels (VPS)
Positive Bewertungen (0)
1596 Ansichten
Related questions
- Perte de mot de passe
32773 
26.05.2023 13:36
- Comment configurer/utiliser un SMTP depuis un VPS ?
32550 07.10.2019 10:49
- Je connais que le mutu et besoin de node js
30716 14.04.2017 13:34
- Envoi Email via SMTP (port 587) sur VPS Pro1
28288 27.01.2017 10:02
- [résolu] Serveur Mysql - hôte inconnu
27978 19.01.2017 16:21
- Qu'est-ce vcore?
27774 06.01.2017 19:20
- Mon VPS à Timed Out
26580 15.03.2017 22:50
- Délai de livraison VPS
26239 11.07.2018 15:15
- Accès externe base de données
25605 18.07.2018 10:29
- Problème avec statisitique Plesk
24923 29.03.2017 07:44
tu es sur un vps plesk, n'est ce pas?
ça tourne sur quoi comme VPS ?
J'ai un plesk pro 1.
Bonjour,
Est-ce que la base de données est en innodb ?
Si oui, des jointures ont-elles étaient mis en place ?
Est-ce qu'un système de cache, côté php, a été mis en place (memcache ou autre ?)
Est-ce que le serveur de base de données à été paramétré ou est-il avec sa config par défaut ?
La version de mariadb présente semble dater donc je pense que le système d'exploitation aussi malheureusement (dangereux à mon sens).
Effectivement, c'est une deb8, donc ce n'est plus maintenu.
Première étape passer sur autre chose...
Et plesk pro... y a quoi là derrière ?
Les perfs disques ça dit quoi ?
Et je rejoins @popallo, il faut du cache applicatif (memcache, redis, filecache au pire).
Merci beaucoup Messieurs pour vos infos et désolé pour mon retard tardif..
J'ai contacté à ce jour OVH pour faire évoluer ma base de données mais ils refusent de le faire.
J'ai fait un clone de mon site sur un hebergement mutualisé à 10 euros par mois et je viens de m'apercevoir que le temps de chargement des pages étaient plus rapides pour les catégories que sur le plesk à 80 euros.
Le problème vient bien de la base de données et les requetes SQL mettent plus de temps à s'exécuter.
Voici la réponse de OVH :
"Après vérifications auprès de nos administrateurs, je suis navré de vous indiquer qu'il n'est pas possible de mettre à jour MariaDB.
Par ailleurs, Il est effectivement possible qu'il y ai une différence entre votre hébergement mutualisés avec un SQL privé car la mémoire vive est dédiée à traité uniquement les requêtes SQL. Tandis que sur votre plesk tout est géré par la même mémoire vive (os, web, php, mysql,..)."
Du coup je ne sais pas quoi faire du tout.
Avez-vous une idée de comment je peux faire?
Cordialement
Alexandre