Majeur La requête de suppression des ressources effectuée par le script de maintenance des newsletters n'est pas optimisée et peux effectuer un lock de plusieurs minutes sur la table RESSOURCE.
La requête actuelle est la suivante :
DELETE FROM RESSOURCE WHERE CODE_PARENT IN (SELECT CONCAT(ID_MAILING,',TYPE=MAILING') FROM MAILING WHERE DATE_PURGE <= 'YYYY-MM-DD');
Voici le temps que prend la requête avec 71415 enregistrements dans RESSOURCE et 249 enregistrements dans MAILING:
sudo mysqlslap -uroot -proot --create-schema=database --query="DELETE FROM RESSOURCE WHERE CODE_PARENT IN (SELECT CONCAT(ID_MAILING,',TYPE=MAILING') FROM MAILING WHERE DATE_PURGE <= NOW());" --verbose Benchmark Average number of seconds to run all queries: 181.276 seconds Minimum number of seconds to run all queries: 181.276 seconds Maximum number of seconds to run all queries: 181.276 seconds Number of clients running queries: 1 Average number of queries per client: 1
Si on optimise la requête en passant par un join :
DELETE R FROM RESSOURCE R JOIN MAILING M ON R.CODE_PARENT = CONCAT(M.ID_MAILING,',TYPE=MAILING') AND DATE_PURGE <= 'YYYY-MM-DD';
La requête prend moins d'une seconde:
sudo mysqlslap -uroot -proot --create-schema=database --query="DELETE R FROM RESSOURCE R JOIN MAILING M ON R.CODE_PARENT = CONCAT(M.ID_MAILING,',TYPE=MAILING') AND DATE_PURGE <= NOW()" --verbose Benchmark Average number of seconds to run all queries: 0.008 seconds Minimum number of seconds to run all queries: 0.008 seconds Maximum number of seconds to run all queries: 0.008 seconds Number of clients running queries: 1 Average number of queries per client: 1
1.
|
Report 6.7 - NEWSLETTER-154 |
|
Fini | Automate |