Bonjour à tous,
 
Voilà mon problème :
J'ai une très lourde requête qui fait planter Apache toutes les nuits car elle est loin d'être optimisée.
Voilà la requête :

 
Le but de la requête est de supprimer tous les enregistrements d'action_historique qui sont liés à un membre qui n'existe pas ou à un chval qui n'existe pas.
Auriez-vous une idée pour optimiser cette requête ?
 
Merci d'avance

mettre des index sur les id dans tes tables

+1

Index, et remplacer les NOT IN par des NOT EXISTS peut aider également.
 
DELETE FROM action_historique ah
WHERE NOT EXISTS (SELECT 1 FROM membre WHERE idm = ah.idm)
OR NOT EXISTS (SELECT 1 FROM chval WHERE idc = ah.idc);

En sachant que la table contient plusieurs centaines de millions d'enregistrements, tu penses que c'est raisonnable une telle requête ?
Quand vous parlez d'index, vous voulez dire quoi ? (je ne vois pas ce que viennent faire les index là)

ben si t'indexes les champs contenus dans tes clauses ca ira forcement plus vite
mais des centaines de milions d'enregistrements avec MySQL

pour les index c'est une des bases de l'approche des bases de données.
 
les index permettent de diminué enormément les temps de requette sur des champs comme des id
 
 
pour ce qui est de centaines de million d'enregistrement sur mysql, comme le fait remarquer betsamee faut pas avoir peur    ce sgbd ne tiens pas la route pour de tels quantités d'enregistrements

effectivement un index devrait fortement améliorer les choses, car l'on peut considérer que l'on ne devra deleter qu'un nombre d'enregistrement minime par rapport au nombre total de la total,
donc, un index sur idm et un sur idc, suivant le ratio (distinct idm/nbr total enregistrement) peut-etre même un index bitmap (si ca existe en mysql)
 
et vu la taille de la table, peut-etre ne pas lancer la création d'index en journée ^^

J'ai dit une bêtise, pour cette table, on en est à 40 000 000 d'enregistrements.
 
Sinon, je vais regarder la doc à propos des index

40 millions ça semble plus raisonable, même si ça reste un peu gros
 
sinon ++ à tout ce qui a été dit : index + not exists devrait considérablement améliorer les perfs.

Etant donné que idc et idm sont des clés primaires entières en auto increment, pensez-vous que des index puissent vraiment améliorer les perfs ? Il faudra quand même passer tous les enregistrements, non ?

C'est pas dans les tables membre et chval qu'il faut mettre les index, mais dans la table action_historique.
 
Crée un index portant sur idm et idc dans cette table.

Au fait, pourquoi n'as-tu pas fait des FK avec comme conditions sur le delete "cascade" ?
 
Ca aurait permis d'éviter cette requête, et ainsi ne traîter qu'une ligne à la fois, ce qui serait bien plus rapide ! (couple imd,idc dans un index, peut-être même unique !)

 
non ca va pas etre terrible alors
 
essaies de lancer un optimize sur tes tables de temps en temps

ou bien des triggers, peut importe. c'est quand même plus rapide de ne traîter qu'un nombre réduit de lignes en n'utilisant qu'une valeur unique dans un index plutôt que d'aller chercher une liste de valeurs qui n'existent pas dans deux autres tables qui peuvent être volumineuses !

 
si ses tables sont en MyISAM alors c'est mort pour le FK

tu parles de MySQL la  

je pense surtout qu'il ne cherche pas à mettre l'index au bon endroit

ah ouais c'est vrai, j'oubliait qu'il y avait des maso sur ce forum

pour en revenir au probleme meme indexe a mort ca me parait pas viable une requete avec deux not in sur deux sous requetes , avec MySQL et des dizaines de milions d'enregistrements

Bah c'est sur que y'a comme qui dirait un léger problème.
 
Faut dire qu'il n'y a pas que MySQL qui aura du mal, n'importe quel SGBD n'aime pas ce genre de requêtes.
D'où l'intérêt absolu d'utiliser des FK avec "ON DELETE CASCADE". (ou des triggers quand on travaille avec des vrais outils )

Après vérification, il y a déjà un index sur idm et sur idc dans action_historique.
 
Je vais regarder du côté des FK (j'avoue que je ne sais pas ce que c'est ) : ça me parait pas mal. Si je comprends bien, ça permet de supprimer tous les champs correspondant au même idm, c'est ça ?
C'est-à-dire, on trouve un enregistrement dans a_h pour un idm=1 dont l'idm n'existe pas dans membre, ça va tout de suite supprimer tous les enregistrements dont l'idm est 1 dans a_h en évitant de faire la recherche dans membre à chaque ligne : j'ai juste ?

la FK permet, en temps normal, de s'assurer que lorsqu'on insère une ligne dans action_historique, que la valeur de IDM existe dans la table membre, et IDC dans la table chval.
 
Mais elle permet aussi de faire dans l'autre sens : si tu supprimes une ligne dans membre, alors ça vérifie qu'il n'y a aucune ligne avec cet IDM dans ta table d'historique.
Par défaut, si au moins une ligne en question existe, alors ça plante.
Avec la propriété "ON DELETE CASCADE", alors à ce moment, lorsque tu supprimes un membre, ça supprime toutes les lignes de action_historique faisant référence à son IDM.

Peut être une piste pour gagner en perf :
- créer une table temporaire résultat de select action_historique.* from action_historique inner join  membre inner join cheval  
- remplacer action_historique par ta table tempon (drop table + rename)
 
Donc au lieu de faire des delete, tu fait le select des enregistements qui vont bien, en général c'est plus performant.
Attention : ne pas oublier de recréer tes index après la manip

 
   
c'est assez incroyable mais j'etais en train  de poster le meme conseil
 

spa bête en effet. (mais qu'est-ce que vous êtes gores les gars )

 
en revanche cette etape risque d'etre la plus longue

  d'où le "en général c'est plus performant", parfois la recréation des index est trop longue  

ben ça dépend comment MySQL gère ça. S'il fait comme Oracle ou SQL Server, la création de l'index est instantannée, et il le reconstruit réellement en tâche de fond, donc ça n'influe pas sur les temps de réponse (par contre tant que les index ne sont pas fini d'être régénérés, bah... il peut pas les utiliser )

Ce serait intéressant de savoir combien il y a de lignes dans chaque table (40 millions pour action_historique, mais combien pour les autres tables : membre/chval ?)
 
edit: et une approximation du nombre de lignes qu'on pense effacer.

 
40 000 000 enregistrements la dessus ? bin ca en fait un paquet de bourricot

Ah bon ?
A l'époque ou je faisait bcp de ce genre de traitement, la création des indexs ne se faisait pas comme ça, ça prenait réellement un certain temps (pour ne pas dire un temps certain). Ca marche comme ça depuis Oracle 9 ? Ou alors c'est un paramétrage de l'instance et/ou de la commande create index ? Ou alors ça depend du type d'index ?

remplace tes sous requetes par des tables temporaires. je suppose que ton sgbd est mysql, les sous requetes relevent du masochisme avec mysql
 
edit:

 
pas forcement ca depend de l'utilisation qu'on en fait
et comme le disait Arjuna meme un SGBD plus "professionnel" aurait certainement du mal a realiser une requete pareille

sous mysql 5 je pense que ca doit etre mieux en tout cas

le 4.1.9 fait ca tres bien aussi

le principe est de chercher a mettre la sous requete dans le From si possible et non pas le where

Ben chais pas, quand j'étais chez GE (avec un Serveur HP sous SunOS / Oracle 8i) la création d'un index était rigoureusement instantannée (ou ça prenait une petite dizaine de secondes).
Puis ensuite si on faisait une requête tapant normalement dans l'index, il ne l'utilisait pas. Avec un table hint, on obtenait une erreur comme quoi l'index n'était pas prêt.
Puis au bout de quelques minutes ça marchait.
 
Par contre, maintenant que tu m'y fait penser c'est vrai que... il fallait faire une oppération spéciale pour que ça marche... me souvient plus du tout quoi ! Parceque dans TOAD ça mettait un peu plus de temps. Et vu que la machine était quelque peut grosse (pintaprocesseur - me demandez pas pourquoi 5 et pas un multiple de deux avec deux SAN dédiés aux bases et quelque 4 Go de mémoire par CPU) donc forcément, même un index sur 25 champ sur une table de 50 000 000 lignes ça mettait pas très longtemps à se créer...
M'enfin sur et certain, dans notre batch de réplication de la base depuis le serveur de l'ERP vers la base dédiée au Web, via SQL Loader, (suppression des index, truncate, allimentation puis indexation), l'indexation qui suivait était rigoureusement instantannée, et pourtant le volume des données était pire (dénormalisation et duplication des valeurs de référence pour de meilleures performances en lecture) alors que le serveur était... le même

PS: c'était de bêtes "create index", sans spécifier le type

Environ 150 000 pour membre et 190 000 pour chval.
Il y a, je pense quelques centaines d'enregistrements à effacer.
 
Pour info, la version de MySQL est la 4.1.13
Le serveur est un bi-Xeon (2.8 ou 3GHz, je ne sais plus) avec 2Go de RAM, SCSI+Raid1.