Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.11.6
-
Debian 11 with docker container
Description
Hi,
I'm experiencing an issue with my database queries.
When I initialize the database and use it for a few weeks, the query responses are fast, around ~0.10 seconds. However, after a while, the queries suddenly become very slow, taking more than 30 seconds to execute.
By enabling the slow query log, I noticed that when the queries are fast, they scan approximately 50,000 rows. But when the queries become slow, the number of rows analyzed suddenly increases to 8 million.
To resolve this issue, I perform the following steps:
Create a dump of the database.
Move the /var/lib/mysql directory.
Create new instance.
Restore the database from the dump.
After doing this, the queries return to their original fast performance.
Thank you in advance for your help.
This the one of queries that have this problem :
SELECT m0_.id AS id_0 |
FROM materiel_tarification m1_ |
LEFT JOIN materiel m0_ ON m1_.materiel_id = m0_.id |
AND m0_.discr IN ('materiel') |
INNER JOIN materiel_base_categorie_base m3_ ON m0_.id = m3_.materiel_base_id |
INNER JOIN eav_categorie e2_ ON e2_.id = m3_.categorie_base_id |
AND e2_.discr IN ('categorie') |
LEFT JOIN materiel m4_ ON m0_.id = m4_.materiel_parent_variant_id |
AND m4_.discr IN ('materiel') |
LEFT JOIN eav_valeur_attribut_materiel e5_ ON m0_.id = e5_.materiel_id |
AND e5_.discr IN ('valeurAttribut') |
LEFT JOIN eav_attribut e6_ ON e5_.attribut_id = e6_.id |
AND e6_.discr IN ('attribut') |
LEFT JOIN configuration_taux_tva c7_ ON e2_.tva_courante_id = c7_.id |
AND c7_.discr IN ('tva') |
LEFT JOIN materiel_marque m8_ ON m0_.marque = m8_.id |
AND m8_.discr IN ('marque') |
LEFT JOIN canal_de_vente c9_ ON m1_.canal_de_vente_id = c9_.id |
AND c9_.discr IN ('canal_de_vente') |
LEFT JOIN materiel m10_ ON m0_.materiel_parent_variant_id = m10_.id |
AND m10_.discr IN ('materiel') |
LEFT JOIN campagne_materiel c12_ ON m0_.id = c12_.materiel_id |
LEFT JOIN campagne c11_ ON c11_.id = c12_.campagne_id |
AND c11_.discr IN ('campagne') |
LEFT JOIN modele_materiel m14_ ON m0_.id = m14_.materiel_id |
LEFT JOIN modele m13_ ON m13_.id = m14_.modele_id |
LEFT JOIN machine m15_ ON m13_.machine_id = m15_.id |
LEFT JOIN materiel_marque m16_ ON m13_.marque_id = m16_.id |
AND m16_.discr IN ('marque') |
WHERE (e2_.is_active = 1 |
AND m0_.flag_actif = 1 |
AND m0_.materiel_parent_variant_id IS NULL |
AND c9_.libelle LIKE 'Web' |
AND (e2_.id = 1 |
OR e2_.id = 4 |
OR e2_.id = 6 |
OR e2_.id = 7 |
OR e2_.id = 8 |
OR e2_.id = 9 |
OR e2_.id = 10 |
OR e2_.id = 11 |
OR e2_.id = 12 |
OR e2_.id = 117 |
OR e2_.id = 130) |
AND ( |
(SELECT MIN(m17_.prix_vente_ht) AS sclr_1 |
FROM materiel_tarification m17_ |
LEFT JOIN materiel m18_ ON m17_.materiel_id = m18_.id |
AND m18_.discr IN ('materiel') |
WHERE (m18_.materiel_parent_variant_id = m0_.id |
AND m17_.prix_vente_ht <> 0) |
AND m17_.discr IN ('tarification')) > 0 |
AND |
(SELECT MIN(m19_.prix_vente_ht) AS sclr_2 |
FROM materiel_tarification m19_ |
LEFT JOIN materiel m20_ ON m19_.materiel_id = m20_.id |
AND m20_.discr IN ('materiel') |
LEFT JOIN canal_de_vente c21_ ON m19_.canal_de_vente_id = c21_.id |
AND c21_.discr IN ('canal_de_vente') |
WHERE (m20_.materiel_parent_variant_id = m0_.id |
AND m19_.prix_vente_ht <> 0) |
AND m19_.discr IN ('tarification')) <= (1500 / (1 + c7_.tva)) |
OR (m1_.prix_vente_ht <= (1500 / (1 + c7_.tva)) |
AND m1_.prix_vente_ht <> 0 |
AND m1_.prix_vente_ht IS NOT NULL))) |
AND m1_.discr IN ('tarification') |
GROUP BY m0_.id |
ORDER BY m0_.flag_indisponible ASC, |
m0_.qteStock DESC |
LIMIT 12;
|
Best regards,
WareTeamFlo