Optimisation de code SQL et php

Bonjour à tous

J’ai un bout de code qui fonctionne très bien, mais qui est très très lent (25 sec).

J’ai indexé la table et j’ai gagné 1 sec, youpi : )

J’essai d’optimiser le code mais je ne trouve pas de solution, je pause cette question dans la parti mysql car je pense qu’on peut simplifier le tout avec DELETE WHERE NOT EXISTS, mais je n’y arrive pas.

Si vous avez des idées je suis preneur

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

<?php $sql = "SELECT id_data FROM `table1`"; $req = mysql_query($sql); while ($arr = mysql_fetch_array($req, MYSQL_ASSOC)) { $ide = " SELECT `name` FROM `table2` WHERE `value`='".$arr['id_data']."'"; $result = mysql_query($ide); $arr2 = mysql_fetch_array($result, MYSQL_ASSOC); if (!$arr2){ $reqdel = "DELETE FROM `table1` WHERE `id_data` = '".$arr['id_data']."'"; mysql_query($reqdel); } } ?>

---

A voir ce qui est le plus optimisé selon la taille de tes tables, mais tu as plusieurs moyens d'optimiser :
- si tes tables sont petites, DELETE FROM `table1` WHERE `id_data` NOT IN (SELECT value FROM `table2`) ?

- pour tes tables plus grosses et, surtout, si tu souhaites garder un traitement côté PHP - pour ajouter des logs, un contrôle, etc -, faire du traitement par lots avec des requêtes préparées ( introduction à PDO et requêtes préparées).
Cette méthode te permettra de traiter des lots de 50 (par exemple, à adapter selon ton serveur et ta bdd) éléments avec des requêtes pour lesquelles le moteur aura déjà calculé le plan d'exécution => gain de temps :D

Tu peux aussi récupérer tous tes identifiants à supprimer dans un tableau et lancer ta requête de suppression à la sortie de ta boucle (plutôt que de faire un DELETE à chaque tour de boucle).
Par exemple :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

<?php $tabToDelete = array(); $sql = "SELECT id_data FROM `table1`"; $req = mysql_query($sql); while ($arr = mysql_fetch_array($req, MYSQL_ASSOC)) { $ide = " SELECT `name` FROM `table2` WHERE `value`='".$arr['id_data']."'"; $result = mysql_query($ide); $arr2 = mysql_fetch_array($result, MYSQL_ASSOC); if (!$arr2){ $tabToDelete[] = $arr['id_data']; } } if (!empty($tabToDelete)) { $reqdel = "DELETE FROM `table1` WHERE `id_data` IN '".implode(',', $tabToDelete)."'"; mysql_query($reqdel); } ?>

---

Salut,

il faut fuir les requêtes imbriquées comme la peste.
La solution réside dans le design de ta base :
- quel est le type des tables utilisées : MyISAM, InnoDB...

Ensuite les solution à base de IN sont très lentes car cette clause induit une lecture séquentielles des données.

Citation:

---

Envoyé par k'amm

A voir ce qui est le plus optimisé selon la taille de tes tables, mais tu as plusieurs moyens d'optimiser :
- si tes tables sont petites, DELETE FROM `table1` WHERE `id_data` NOT IN (SELECT value FROM `table2`) ?

- pour tes tables plus grosses et, surtout, si tu souhaites garder un traitement côté PHP - pour ajouter des logs, un contrôle, etc -, faire du traitement par lots avec des requêtes préparées ( introduction à PDO et requêtes préparées).
Cette méthode te permettra de traiter des lots de 50 (par exemple, à adapter selon ton serveur et ta bdd) éléments avec des requêtes pour lesquelles le moteur aura déjà calculé le plan d'exécution => gain de temps :D

Tu peux aussi récupérer tous tes identifiants à supprimer dans un tableau et lancer ta requête de suppression à la sortie de ta boucle (plutôt que de faire un DELETE à chaque tour de boucle).
Par exemple :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

<?php $tabToDelete = array(); $sql = "SELECT id_data FROM `table1`"; $req = mysql_query($sql); while ($arr = mysql_fetch_array($req, MYSQL_ASSOC)) { $ide = " SELECT `name` FROM `table2` WHERE `value`='".$arr['id_data']."'"; $result = mysql_query($ide); $arr2 = mysql_fetch_array($result, MYSQL_ASSOC); if (!$arr2){ $tabToDelete[] = $arr['id_data']; } } if (!empty($tabToDelete)) { $reqdel = "DELETE FROM `table1` WHERE `id_data` IN '".implode(',', $tabToDelete)."'"; mysql_query($reqdel); } ?>

---

---

Merci la version php, elle ma fait gagné 7 sec, ce qui est un vrais changement.

Je vais regarder pour les autres points que tu as dit

Pour information c'est du MyISAM il y a 86 602 ligne et la table fait 3Mo, je ne suis pas obliger de garder un traitement php (j'aimerai même éviter)

Et avec ça :

Code:

---

1 2 3 4 5

DELETE FROM table1 LEFT JOIN table2 ON table1.id_data = table2.name WHERE table2.name IS NULL

---

N'oublie pas de poser des index sur les colonnes de jonction.

Bonsoir,

il me semble qu'il n'est pas possible d'utiliser des jointures lors de l'utilisation de la commande DELETE.

Il va falloir passer par la syntaxe que k'amm a donné plus haute :

Code:

---

DELETE FROM maTable1 WHERE idData NOT IN (SELECT idDate FROM matable2 ...)

---

Je vous confirme que le lef join est possible dans un delete, je continue mes investigations.

Après lecture de la doc au sujet des jointures avec l'utilisation du DELETE,

il est précisé qu'il faut préciser les tables qui seront concerné par la suppression d'enregistrement avant la clause FROM :

Code:

---

1 2	DELETE table1 FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

---

si on veut supprimer dans table1, table2 mais que l'on a besoin d'information dans table3 :

Code:

---

1 2 3 4

DELETE t1, t2 FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id LEFT JOIN table3 t3 ON t2.nom = t3.nom WHERE t3.col IS NULL;

---

lien vers la doc.