[Résolu] Optimisation mysql

Bonjour,

Je m'initie à un mysql un peu plus avancé ces derniers temps, et je tatônne encore pas mal, malgré mes nombreuses sources.

Dans le cadre d'un projet, j'ai créé une table de messagerie privée contenant 11 champs, de types et de taille variable. J'enregistrai les destinataires d'un message dans le même champ, ce qui pose de nombreux problèmes. J'ai donc essayé de les résoudre et me suis penché sur les jointures, les clefs composites, et la 'forme normale' (modèle pour DB).

Je me retrouve donc maintenant avec 3 tables:

CREATE TABLE `smokingf_aMMdTest`.`notebox_notes` (
`ref` CHAR( 10 ) NOT NULL ,
`titre` TINYTEXT NOT NULL ,
`texte` TEXT NOT NULL ,
`date` CHAR( 16 ) NOT NULL ,
PRIMARY KEY ( `ref` ) 
) ENGINE = MYISAM

CREATE TABLE `smokingf_aMMdTest`.`own` (
`ref` CHAR( 10 ) NOT NULL ,
`tag` CHAR( 10 ) NOT NULL ,
`stat` ENUM( '0','1','2' ) NOT NULL ,
PRIMARY KEY ( `ref` ) 
) ENGINE = MYISAM

CREATE TABLE `smokingf_aMMdTest`.`notebox_chat` (
`chatTag` CHAR( 10 ) NOT NULL ,
`ref` CHAR( 10 ) NOT NULL ,
PRIMARY KEY ( `chatTag` ) 
) ENGINE = MYISAM

(grosso modo, j'ai fait les clefs composites après).

J'utilise donc les jointures sql pour retrouver mes enregistrements.
Le soucis est le suivant: je cherche à optimiser mes requêtes, et donc à minimiser le nombres de champs fouillés par mysql (mysql->explain), mais systématiquement mes requêtes balayent au moins tous les enregistrements de la notebox.

Ma question est donc: est-ce que mon modèle est convenablement conçu? Comment pourrais je l'améliorer?

En vous remerciant,

Smoke
Modifié par Smoke (15 Sep 2009 - 20:18)

Salut,

en l'état ce n'est pas très clair. D'abord parce que le nom de tes tables n'est pas très parlant et ensuite parce que c'est justement en voyant les clefs que tu as crées qu'on pourrait dire quelque chose...

Quoi qu'il en soit je dirais que si une requête balaye tous les enregistrement c'est qu'il te manque un index...

Salut, et merci de l'intérêt que tu portes à ma situation ^^

Pour les tables, j'ai décomposé les infos nécessaires à l'existence d'un message : la table chat récupère une référence de conversation associée à la référence d'un message précis de la conversation (elles constituent une clef composite), la table notes a comme clef la référence de chaque message, les champs récupère le titre, le texte et la date du message, et la table own détermine qui a accès aux messages, avec référence du message et identifiant de l'utilisateur concerné, avec un champ statut pour l'utilisateur désigné (un message pourra être nouveau chez le destinataire mais considéré comme déjà lu chez l'auteur). Own contient une clef composite sur ref et tag. Le schéma a été pensé en prenant en compte notamment le fait qu'il y aura possibilité d'y avoir plusieurs destinataires pour un messages, et qu'il faudrait que la recherche dessus soit la plus propre possible (mon ancienne alternative consistait en l'utilisation de requête avec like, plutôt moche :s...). Le projet est assez important, donc on devrait se retrouver avec une table plutôt conséquente.

As tu besoin de davantage d'informations?

Smoke a écrit :
Own contient une clef composite sur ref et tag.

Alors dans ce cas tu devrais avoir :

CREATE TABLE `own` (
  `ref` char(10) NOT NULL,
  `tag` char(10) NOT NULL,
  `stat` enum('0','1','2') NOT NULL,
  PRIMARY KEY  (`ref`,`tag`)
) ENGINE=MyISAM

Comme je te disais : si tous les enregistrements sont balayés c'est qu'il manque un (ou des) index(s) sur un (des) champ(s). Il faudrait donc :
1) voir les indexs que tu as créés.
2) voir la requête que tu lances.

Je n'ai pas créé d'index, j'ai pensé que ça ne ferait que répéter inutilement les clefs primaires :s...

Je confirme pour le code que tu as mis^^

J'ai pas la requête sous la main, et je ne pourrais pas la recomposer avant lundi, mais quand ça sera fait je la posterais^^

Bon week end^^

Smoke a écrit :
Je n'ai pas créé d'index, j'ai pensé que ça ne ferait que répéter inutilement les clefs primaires

C'est effectivement inutile de créer un index qui reprend le ou les champ(s) de la clé primaire. C'est en revanche utile si la clé primaire ne correspond pas à tous les champs qui se trouvent dans une clause WHERE.
Modifié par Heyoan (12 Sep 2009 - 13:44)

intéressant^^ je me pencherais sur la question merci pour l'info^^

Salut^^

Bon du coup j'ai fais mes petits essais et je suis tombé sur un résultat qui me semble satisfaisant. J'ai utilisé ton conseil sur les index et la clause where, ce qui a considérablement amélioré ma requête, la voici:

select n.titre,n.texte
from notebox_chat1 c
inner join notebox_notes1 n on c.ref=n.ref
inner join notebox_own1 o on c.ref=o.ref
where c.chatTag='0000000001' and o.stat=1

Explain me retourne:

1	SIMPLE	o	ref	PRIMARY,stat	stat	1	const	1	Using where
1	SIMPLE	c	eq_ref	PRIMARY	PRIMARY	20	const,smokingf_aMMdTest.o.ref	1	Using where; Using index
1	SIMPLE	n	eq_ref	PRIMARY	PRIMARY	10	smokingf_aMMdTest.c.ref	1	Using where

j'ai donc deux types eq_ref (la doc dit que c'est bien, j'ai pas encore super bien compris pourquoi^^) et le nombre de champs balayés me semble minimal .
Je retiens, donc^^.

Qu'en pense tu?

Smoke a écrit :
Qu'en pense tu?

Ben c'est nickel vu que la requête trouve directement les bons indexs (en l'occurrence les clefs primaires).

Edit: par contre tu pourrais essayer de créer un index sur ref, stat pour voir.
Et également jeter un oeil à EXPLAIN EXTENTED suivi de SHOW WARNINGS.
Modifié par Heyoan (15 Sep 2009 - 20:18)

super^^ bon, je mets tout ça sous le coude alors^^ en espérant que ça fonctionne correctement en situation réelle ^^ (là mes tables n'ont que 3 messages mdr)

Merci pour ton aide

Ah on s'est croisés : je venais d'éditer mon post précédent... et pour un vrai test il faut te faire un vrai jeu d'essai... (il suffit de faire un script php qui fait des INSERT dans une boucle).
Modifié par Heyoan (15 Sep 2009 - 20:20)

no soucy^^

j'ai un index sur stat, fait avant le test mais tu en veux un sur le ref de quelle table?

Voici le résultat du explain extended:

1	SIMPLE	c	ref	PRIMARY	PRIMARY	10	const	2	Using where; Using index
1	SIMPLE	o	ref	PRIMARY,stat	PRIMARY	10	smokingf_aMMdTest.c.ref	1	Using where
1	SIMPLE	n	eq_ref	PRIMARY	PRIMARY	10	smokingf_aMMdTest.c.ref	1	 

comment dois je mettre le show warnings? :s j'ai jamais réussi à le placer celui là -_-'

Smoke a écrit :
mais tu en veux un sur le ref de quelle table?

Je parle de créer un index sur notebox_own1 : ref + stat

Smoke a écrit :
comment dois je mettre le show warnings? :s j'ai jamais réussi à le placer celui là -_-'

il faut séparer les requêtes par un point virgule. Une recherche daans Google devrait te montrer des exemples.
Modifié par Heyoan (15 Sep 2009 - 20:44)

Voilà les warnings:

Level 	Code 	Message 
Note	1003	select `smokingf_aMMdTest`.`n`.`titre` AS `titre`,...

et voilà l'explain avec l'index sur stat et ref:

1	SIMPLE	o	ref	PRIMARY,stat	stat	1	const	2	Using where; Using index
1	SIMPLE	n	eq_ref	PRIMARY	PRIMARY	10	smokingf_aMMdTest.o.ref	1	 
1	SIMPLE	c	eq_ref	PRIMARY	PRIMARY	20	const,smokingf_aMMdTest.o.ref	1	Using where; Using index

Modifié par Smoke (15 Sep 2009 - 20:54)

Smoke a écrit :
Voilà les warnings

Euh... oui mais non.

Je ne pense pas qu'on puisse le faire depuis phpMyAdmin puisqu'il tronque le message. Le "plus simple" est de le faire en ligne de commande mysql (cf. Google).

ah damned! m'en serais douté!

bon, sérieusement, j'ai eu la flemme hier soir^^.
L'ami google m'a parlé d'un client nommé mysql pour faire ce genre de choses en ligne de commande, mais je l'ai pas trouvé sur le site (bille), donc je pense que j'vais mettre la requête quelque part en ligne, et je poste dès que j'ai le résultat^^

je n'arrive pas à montrer les warnings -_-', donc je pense que je te les soumettrais plus tard, quand j'aurais réussi :$...

Salut^^
Je travaille sur une autre requête actuellement, voici le explain:

1	SIMPLE	a	ALL	PRIMARY	NULL	NULL	NULL	14	 
1	SIMPLE	n	ref	PRIMARY,author	author	10	smokingf_aManMachineDream.a.tag	1	Using where
1	SIMPLE	c	ref	ref	ref	10	smokingf_aManMachineDream.n.ref	1	Using index
1	SIMPLE	o	eq_ref	PRIMARY,stat	PRIMARY	20	smokingf_aManMachineDream.c.ref,const	1	Using where

et la requête:

 SELECT a.artName, n.date, n.titre, count( n.ref ) 
FROM notebox_notes n
INNER JOIN accounts a ON n.author = a.tag
INNER JOIN notebox_chat c ON n.ref = c.ref
INNER JOIN notebox_own o ON o.ref = n.ref
WHERE o.stat = '1'
AND o.tag = '0000000001'
AND n.author = a.tag

je pense qu'il est possible de faire en sorte que la clef primaire soit choisie et que le type ne soit pas all pour la table accounts, mais je ne trouve pas comment, et j'ai peur que ça ralentisse considérablement mes requêtes :s... Aurais tu quelques suggestions?

Salut,

à priori il manque une clé primaire ou un index sur le champ tag de la table accounts. D'autre part c'est inutile de rajouter AND n.author = a.tag dans la clause where puisque c'est déjà spécifié dans le premier INNER JOIN.

Salut

La clef primaire est déjà sur le champ tag^^ quand au and n.author.. je n'étais pas sûr, donc je l'ai retiré.
Ca n'y change malheureusement rien. Peut être avons nous déjà poussé à son max l'optimisation de cette requête?