Discussion :

[vinzzzz]

Hello,

Je dispose d'une DB dans laquelle j'ai une table Obj qui contient les objets de ma base, une table Sel représentant une selection d'objets, et une ObjSel faisant l'association entre Sel et Obj

En gros:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Obj - Obj.obj_id
Sel - Sel.sel_id, Sel.sel_name
ObjSel - obj_id, sel_id

Dans mon appli cliente, je donne la possibilité de supprimer une selection, et en opton, de supprimer les objets associés a cette selection.

Mon problème est simple: l'execution d'une telle requête prends un temps vraiment très important dans ma base qui actuellement contient plus de 4 millions d'objets.

La requête que j'utilise est la suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
DELETE FROM Obj WHERE obj_id IN (SELECT obj_id from ObjSel where sel_id=13)

Y'a t-il un moyen d'optimiser un DELETE de ce type? J'avais le même genre de problème de perf avec les selects que j'ai réglé par des jointures, mais pour les delete je ne trouve pas encore de solution...

Merci d'avance...

PS: je précise: mes tables sont en myisam

[CinePhil]

Essaie avec une jointure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
DELETE FROM Obj 
INNER JOIN ObjSel ON Obj.obj_id = ObjSel = obj_id
WHERE ObjSel.sel_id=13

[vinzzzz]

J'avais déjà essayé ce type de requête mais impossible de l'executer, il me sortait systématiquement une erreur de syntaxe donc j'ai supposé que ca n'était pas applicable à un delete...

J'ai refait qqes essais, toujours pareil: (avec les vrais noms de table...)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
DELETE FROM Structures 
    INNER JOIN StructuresSelectionsAssoc 
        ON Structures.structures_id=StructuresSelectionsAssoc.ssa_struct_id 
    WHERE StructuresSelectionAssoc.ssa_selection_id='5';

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
DELETE FROM Structures 
    INNER JOIN StructuresSelectionsAssoc 
        ON Structures.structures_id=StructuresSelectionsAssoc.ssa_struct_id;

Marche pas.. Je n'ai jms vu de tels exemples dans la doc ou autre c'est pr ca que j'avais abandonné...

[vinzzzz]

Ah... Magie, en ajoutant USING juste après le delete, ca fonctionne...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
DELETE FROM Structures USING Structures
    INNER JOIN StructuresSelectionsAssoc 
        ON Structures.structures_id=StructuresSelectionsAssoc.ssa_struct_id 
    WHERE StructuresSelectionAssoc.ssa_selection_id='5';

[vinzzzz]

Enfin pas tant que ca... le temps d'execution est également énorme :S:

pour une selection de 50000 objets, 3min 54 sec sur un core i7 dernier cris avec pour support de stockage deux vellociraptor 10000 tours /min monté en raid0

[CinePhil]

J'ose espérer que les tables sont indexées ?
Notamment 'Structures.structures_id' et 'StructuresSelectionsAssoc.ssa_struct_id' ?

[vinzzzz]

Oui oui ces deux colonnes sont des clés primaires:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
CREATE TABLE IF NOT EXISTS `Structures` (
 
  `structures_id` int(10) unsigned NOT NULL auto_increment,
(...)
 
  PRIMARY KEY  (`structures_id`)
(...)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=100000000 ;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
 
CREATE TABLE IF NOT EXISTS `StructuresSelectionsAssoc` (
  `ssa_struct_id`           INT NOT NULL ,
  `ssa_selection_id`        INT NOT NULL ,
  PRIMARY KEY (`ssa_struct_id`, `ssa_selection_id`) ,
  CONSTRAINT `ssa_struct_fk`
    FOREIGN KEY (`ssa_struct_id`)
    REFERENCES `Structures` (`structures_id` )
    ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `ssa_selection_fk`
    FOREIGN KEY (`ssa_selection_id` )
    REFERENCES `StructuresSelections` (`ss_id` )
)
ENGINE = MyISAM;

J'ai du mal a croire qu'il n'y a aucune solution... Ou bien c'est ma base qui est mal concue à ce niveau... L'équivalent du DELETE en SELECT est pourtant suffisament rapide.

[CinePhil]

Objet de la recherche : "MySQL DELETE"

Lien trouvé vers la doc MySQL.

On peut y lire :

La commande DELETE supporte les clauses suivantes :

• Si vous spécifiez le mot clé LOW_PRIORITY, l'exécution de la commande DELETE est repoussée jusqu'à ce qu'aucun client ne soit en train de lire la table.
• Pour les tables MyISAM, si vous spécifiez l'option QUICK, le moteur de stockage ne compacte pas les index durant l'effacement, ce qui peut accélérer certains effacements.
• L'option IGNORE fait que MySQL ignore les erreurs durant le traitement des lignes. Les erreurs rencontrées durant la phase d'analyse sont traitées comme d'habitude. Les erreurs qui sont ignorées grâce à cette options sont listées comme des alertes. Cette option a été ajoutée en MySQL 4.1.1.

La vitesse d'exécution des opérations de suppressions peut être affectées par les facteurs présentés dans la section Section 7.2.16, « Rapidité des requêtes DELETE ».

Une piste intéressante ? Je te laisse le soin de l'explorer.

Voir aussi ce qui suit juste après l'extrait précédent :

Dans les tables de type MyISAM, les enregistrements effacés sont maintenus dans une liste liée et les requêtes INSERT suivantes réutilisent les vieux emplacements. Pour recouvrir l'espace inutilisé ou réduire la taille des fichiers, utilisez la commande OPTIMIZE TABLE ou l'utilitaire myisamchk pour réorganiser les tables. OPTIMIZE TABLE est plus simple, mais myisamchk est plus rapide. Voyez Section 13.5.2.5, « Syntaxe de OPTIMIZE TABLE » et Section 5.7.3.10, « Optimisation de table ».

Attention avec USING :

L'idée est que seul les lignes concordante dans les tables énumérées avant le FROM ou avant la clause USING sont effacés. Le but est de pouvoir effacer des lignes de plusieurs tables en même temps tout en ayant d'autres tables pour les recherches.

Préférer peut-être l'alias AS pour résoudre le problème du message d'erreur.

Bonne chance !