bon alors suite à un post sur l'utilité du cryptage de cookie débuté par Gizmo, on m'a conseillé de lancer un tomic sur l'optimisation des requêtes MySQL donc le voilà   .
 
Pour ma part je ne me considère pas comme une super bête en SQL mais je vais tenter d'expliquer les quelques pièges à éviter et quelques manières d'accèlérer le traitement des données via PHP/MySQL
 
Plutot d'en écrire une super tartine je vous laisse poser vos schémas de table et scripts, et on essaiera de les améliorer (surtout pour les forums, avec 10 posts ça passe, et à mille le truc rame à mort !).
 
Euh.. please mettez les structures de table en mode texte, plus facile à copier/coller  
 
GO !!!!!!!!!!

un truc pour un forum en affichage sans arborescence (comme celui là)
 
utiliser 3 tables
1 - pour les titres, avec nom de l'auteur, titre du tomic, dernière réponse etc...
 
   Id int(10) unsigned NOT NULL auto_increment,
   Titre char(100) NOT NULL,
   Auteur char(100) NOT NULL,
   Date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   Rep smallint(5) unsigned DEFAULT '0' NOT NULL,
   Vues smallint(5) unsigned DEFAULT '0' NOT NULL,
   Der_aut char(100) NOT NULL,
   Der_rep datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   PRIMARY KEY (Id)
 
 
2 - une pour les messages, avec un champ Parent qui porte le même nombre que l'Id du Titre parent
 
   Id int(10) unsigned NOT NULL auto_increment,
   Pere int(10) DEFAULT '0' NOT NULL,
   Titre varchar(200) NOT NULL,
   Auteur varchar(100) NOT NULL,
   Date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   Texte mediumblob NOT NULL,
   PRIMARY KEY (Id
 
 
3 - une pour les profils des posteurs
 
   Id int(10) unsigned NOT NULL auto_increment,
   Auteur char(100) NOT NULL,
   Autmail char(100) NOT NULL,
   Ville char(80) NOT NULL,
   Sexe set('M','F'),  
   Url char(50) NOT NULL,
   PRIMARY KEY (Id)
 
 
of course on peut ajouter des infos dans les tables selon ce qu'on a envie de faire.
 
 
Maintenant au niveau des query :
 
Pour afficher la liste des posts, on ressort tout bètement le contenu de la table Titres
là au choix, traitement Php pour la mise en forme, ou directement dans la requête avec CONCAT
 
on fait un lien pour chaque titre où il y aura en paramètre l'ID du post (style message.php?post=XXX ou msg=XYZ)
 
 
Pour afficher ensuite les messages et le profil du posteur à coté, on fait un JOIN des deux tables avec comme point de jointure un clause where sur le pseudo
en gros, MySQL va coller les champs correspondants au nom du posteur à coté de la ligne contenant son post, comme ça pa besoin de faire une requête de plus pendant qu'on parcoure les résultats.
 
ça donne ça : SELECT liste_des_champs from table_des_post LEFT JOIN liste_des_champs table_des_profils on profils.Auteur=post.Auteur where Pere=$post
 
ou pourrait encore accélèrer la query en couplant un n° de posteur à chaque profil et en effectuant la jointure à ce niveau (les opération sur les entiers étant plus rapides que sur les chaines de caractères).
 
on peu aussi ajouter une clause CONCAT histoire de sortir la ligne directement via MySQL, mais là il faudra protéger l'ajout de post plutôt que l'affichage (pour le code HTML, les images non désirées etc)
 
bon déjà un chti bout de fait, n'hésitez pas à en ajouter !

eh eh pour le moment, j'ai tout bon et même plus

up !
Gizmo quand t'as plus tu pourrais détailler non ?  

vi! bon alors, dans ta première table et la deuxieme, tu utilise des varchar pour les auteurs (et dernier auteurs) ce qui fait que si tu veux, comme ici par exemple, mettre des indication supplémentaires comme l'acces au profil au mail ou autre dans les post, tu vas devoir faire une jointure des tables sur base d'une chaine de charactère. Or cette jointure est beaucoup plus rapide si elle se fait sur des entiers, ainsi que la consultation des tables 1 et 2.

oh là tu charries :

 
 
je l'ai pas inclus dans l'exemple pour que ça reste le plus simple possible ;-)
 
j'ai pas eu le temps de faire une new astuce, la prochaine peut être demain, opérations de calculs en direct via la requête, gestion des euros etc..

oups! j'avais sauté cette ligne
 
bon mis a part ca, pour tout ce qui est renseignement sur les posteurs: idem que pour les posts: limiter la table au minimum couramment utilisé. Pour tout ce qui est des renseignements aditionnels, il vaut mieux faire une autre table car la plupart seront de longueur variables et donc plus lent a consulter.

bien sur mais tu peux ne sélectionner que les champs qui t'intêresse là on touche vraiment à l'optimisation : à partir de quelle quantité de données le JOIN est il plus valable que le SELECT de certains champs ?
 
  voilà une bonne question, va falloir sortir les benchs

a mon avis, le join complet n'est jamais plus valable que le select car il doit tout autant parcourir les tables. Par contre si l'on fait un join selectif, la ca peut devenir intéressant, mais il faut voir aucci comment mysql s'arrange pour faire cela.
 
Pour les bench, c'est pas moi qui vait remplir une base avec 100.000 entrées pour faire les test

Il faut tjs essayer d'éviter les jointures !
Qd ya pas bcp de données, ca passe sans trop de prob mais par la suite, ca devient dur dur !
Dans le cadre d'un forum, je préconiserait 3 requetes a qsuivre plutot qu'une jointure.
 
(J'ai testé et ca s'avere bien plus souple et rapide).
 
Autre chose, les index sur les tables, extremement important !
Faut ruser et des fois, faut en mettre des fois pas mais en général qd tu utilise une close WHERE sur un champ, il faut l'indexer.

intérressant! comment as-tu fais tes tests? les différences sont de quel ordre?

Euh elles sont nettement visibles. Essentiellement pour e serveur qui travaille moins.
J'ai fais les tests en creant mon propre forum.
(Basé sur jointures au tout début et simplifié par la suite)
 
Je peux pas te donner des chiffres exacts car la structure de certaines tables, et la gestion des requetes ont radicalement changées et cela influait aussi sur les temps.

t'a fait des test chronometré ou pas? pour les 3 table sitée plus haut t'avais qqch dans le style avec tes jointure ou autre? précise, ca m'intéresse vraiment

euh la, je peux pas te dire. Sinon les test ont été réalisés avec un chrono sur les pages même.
(Comme ya sur ce forum)
Les temps ont été divisés par 2 au moins.
LA seule chose comme je l'ai dit, c'est que non seulement, j'avais suprimé les jointures mais en plus, la stucture même des tables a été bien revue aussi pour éviter les redondances au maximum.

parfait, je jetterai un oeil sur ca alors. ton forum, il est pas open source par hasard?

Euh non malheureusement
Et la il est HS Le dedie a un prob a premiere vue (L'admin  du faire une couille en changeant des params ce matin dans apache).
 
Je te file l'url en privé si tu le souhaite ?

non, envoie moi un mail plutot, mes private déconnent a cause d'un jocebug.
 
merci.

JBs a écrit a écrit : Euh non malheureusement Et la il est HS Le dedie a un prob a premiere vue (L'admin  du faire une couille en changeant des params ce matin dans apache).   Je te file l'url en privé si tu le souhaite ?

 
j'ai rien fais moi en peu rien si les virtual host d'apache on deconner non ?
 
url= http://forum.centralfr.com

[edtdd]--Message édité par Boris l'invincible--[/edtdd]

hehe ;D
Dsl je pouvais pas savoir. Gingko vient de me dire que sous la RED hat 6..2, il a le même genre de prob apres avoir ete dans linuxconf.
Je c pas si ca s'applique aussi a la distro du serv mais bon ...
(Je c c pas le salon et je m'en excuse. D'ailleurs j'arrete la )

pitite questiong...
 
Si j'utilise php, vaut-il mieux employer les fonctions php ou plutot les machins SQL
 
par exemple,  
 
mysql_num_row($machin)
ou
Select count (*) from ...

Euh, je voudrais aussi savoir ce que faisait CONCAT.
 
Les tutorials sql que j'ai trouvés ne l'expliquent pas  
 
Et si vous connaissez un site complet sur sql...
 
 
 
je suis neuneu ! n'est-ce pas ?

 
le SELECT COUNT() c'est mieux...

art_dupond a écrit a écrit : Euh, je voudrais aussi savoir ce que faisait CONCAT.   Les tutorials sql que j'ai trouvés ne l'expliquent pas     Et si vous connaissez un site complet sur sql...       je suis neuneu ! n'est-ce pas ?

 
Je cite la doc :
 
CONCAT(str1,str2,...)
Concatène les arguments et retourne le résultat. Retoune NULL si un des arguments est NULL. Le nombre d'argument minimum est 2.
 
mysql> select CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> select CONCAT('My', NULL, 'QL');
        -> NULL

en fait le CONCAT permet de concaténer la valeur des champs à la sorite de la query, tu peux aussi y ajouter des strings, des opérations etc...
 
exemple simple : avec champs(valeur) => id(1) nom(Sh@rdar) url(http://)
 
$Query = mysql_query("SELECT CONCAT('Le n°',id,' est ',nom,', son site est ',url,'.') as TEST" )
 
$val = mysql_fetch_array($Query);
echo $val["TEST"];
 
la sortie : Le n°1 est Sh@rdar son site est http://.

[edtdd]--Message édité par Sh@rdar--[/edtdd]

oki d'accord...
 
 
au fait, t'as une adresse pour cette doc ?
 
Comme ca je pourrais un peu chercher par moi meme et moins embeter les gens avec mes stupides questions

 
pour une requete unique, je suis d'accord, mais si tu dois aussi sortir les résultat. vaut-il mieux faire un select suivi d'un seclect count ou bien un select suivi de la fonction php?

 
Je pense qu'il faut éviter de faire "travailler" le serveur SQL le plus possible & laisser cette charge au serveur PHP...
 
Maintenant, il faudrait que l'on fasse des tests rigoureux et méthodiques afin de mesurer les temps de réponses en fonction des méthodes utilisées.
 
Par exemple, lorque tu fais un "SELECT DISTINCT(champ) FROM..." cela prend beaucoup de ressources... L'alternative est de faire un "SELECT champ FROM..." et de trier en PHP. Mais est-ce toujours valable, est-ce que cela dépend de la taille de la base (surement) ? Cela dépend aussi de la puissance du serveur SQL et PHP... Difficile de faire des conclusions...

 
si tu doit sortir des résultats, en général t'utilises pas un COUNT...
de toute façon le COUNT(*) ne compte pas vraiment les lignes de la table, il lit juste l'index. donc bien plus rapide que n'importe quel autre comptage.
 
pour ce qui est de délester la charge sur PHP plutôt que MySQL je suis pas d'accord, MySQL est une base super rapide et php reste quand même un peu lourd, pour des opérations complexes je pense que faire un max de travail sur les query peu économiser un max de temps au serveur.  
 
pour se détendre un peu : ce que j'ai trouvé par hasard dans la doc :
The official way to pronounce MySQL is ``My Ess Que Ell'' (not MY-SEQUEL). But we try to avoid correcting people who say MY-SEQUEL    
 
sinon je rappelle à tout ceux qui ont installé MySQL sur leur machine que la doc se trouve dans le répertoire mysqL/docs/

oui mais ca c'est la doc mysql, et y a des trucs qui sont apparemment supposés comme connus.
Moi j'ai pas appris, et les machins sql, c'est pas tout expliqué dedans.
 
 
sinon, quand tu dis que COUNT lis l'index, si jamais y a un "trou", genre 1, 3, 4,... il te donnera le bon nombre de lignes ?

oki, c'était pour etre sur parce que c'était un peu confusant le coup de "lire l'index"
 
 

 
Ouaifff à voir ...

 
Oui, c'est bien ce que j'ai déjà dit

mea culpa je me suis mal expliqué pour le count() en fait au début de la table il y a un index (pas comme sur un champ) qui indique le nombre d'enregistrements et c'est celui là qui est lu par count.
 
 
pour l'histoire des serveurs PhP/MySQL, faut pas oublier quand même que le moteur PhP est couplé à Apache, MySQL lui n'a que sa DB à faire tourner....
 
j'ai pas mal lu la doc sur la manière de fonctionner de la base de données, en fait il s'agit surtout d'optimiser le nombre d' accès disques et certaines opérations peuvent être efefctuées en un seul accès. je vais creuser un peu...

c'est quoi la difference entre CHAR et VARCHAR ?
Y'en a une ?

un bon site en français sur SQL http://sqlpro.multimania.com/indexSQL.html

[edtdd]--Message édité par Taureau--[/edtdd]

 
Oui plutot !
CHAR est utilise lorsqu'il sagit d'une donnée de longueur fixe.
(Etant fixe, il est bien plus rapide que VARCHAR)
VARCHAR lui, il peut etre utilisé sur une longueur variable et est un peu plus lourd que CHAR.
 
Qd on peut, faut pas hésiter a utiliser CHAR au lieu de VARCHAR. Mias par ex dans le cadre d'un enregistrement à un forum, pour le champ du login il faut choisir VARCHAR sinon tous les utilisateurs auront des pseudo de même longueur enfin c pas tres cool..

j'ai trouvé ca... ici
 
CHAR(taille)
Le type CHAR définit une chaine de caractère de taille contante.  
Une chaine de 0 à 255 caractères (taille)  
Si la chaine stockée est plus petite que la taille indiquée les caractères supplémentaire sont remplacés par des espaces vides.  
Si la chaine stockée est plus grande que la taille indiquée, elle sera coupée à l'enregistrement.  
Stockage sur (taille) octets  
Le type CHAR n'est pas sensible à la casse sauf si BINARY  
 
 
VARCHAR(taille)
Le type VARCHAR définit une chaine de caractère de taille variable.  
Une chaine de 0 à 255 caractères (taille)  
Si la chaine stockée est plus petite que la taille indiquée les caractères vides supplémentaire sont supprimés  
Si la chaine stockée est plus grande que la taille indiquée, elle sera coupée à l'enregistrement.  
Stockage sur (taille) octets + 1 octet  
Le type VARCHAR n'est pas sensible à la casse sauf si BINARY  
 
voili, comme ca j'ai bieng compris maintenant

[edtdd]--Message édité par art_dupond--[/edtdd]

JBs > CHAR est plus rapide...
 
mais si on compare un truc dans un CHAR(10) genre "bonjour   "
 
est-ce que "bonjour" == "bonjour   " ?

[edtdd]--Message édité par art_dupond--[/edtdd]

 
Tout a fait d'ou mon allusion au probleme du forum (Pseudo) -> à ne pas utiliser partout mais la ou l'on peut

attention pour la sensibilité à la casse de CHAR et VARCHAR
 
même sans l'attribut BINARY on peut y stocker des majuscules/minuscules mais elles ne seront pas prises en compte au niveau des recherches (et des recherches uniquement).