MySQL : optimiser la requete SELECT (niveau utilisateur)
Suite du billets précédent, quelques conseils pour les utilisateurs un peu plus avertis… sans prétendre être experts 
Astuces et exemples pour optimiser vos bases…
/*******************************************************/
/*OPTIMISATIONS DES REQUETES MYSQL ET EXEMPLES A EVITER */
/*******************************************************/
Lorsque vous créez une page php, bien souvent vous ouvrez la connexion en début de page et la refermez tout à la fin, après avoir affiché le code html, effectué les requêtes, etc. Ceci est à proscrire. En effet la connexion sera ouverte pendant tout le temps de génération de la page, ce qui empêchera un autre utilisateur de pouvoir se connecter si vous avez trop de connexions ouvertes (max_users_connections dans ce cas)
Exemple à éviter :
//CONNEXION A LA BASE
$connexion_sql = @mysql_connect($sql_serveur,$sql_user,$sql_passwd);
if(!$connexion_sql) {echo "Connexion impossible à la base de données. Réessayez plus tard."; exit;}
mysql_select_db($sql_bdd, $connexion_sql);
//AFFICHAGE DONNEES
print('<html><head>etc');
//REQUETES (on suppose que la table contien une dizaine de champs)
$requete=mysql_query("select * FROM table") or die(mysql_error());
while($r=mysql_fetch_array($requete) {
$donnee=$rpseudo;
$texte=$rtexte;
print($donnee.' tatata par '.$pseudo);
} mysql_close($connexion_sql);
Tout d’abord là c’est l’exemple typique à éviter. On va commencer par l’oubli le plus simple : le mysql_close($connexion_sql) APRES la requête, et non à la fin de la boucle.
Ensuite, la requête ne doit pas être construite avec * alors qu’on ne sélectionne que deux champs sur la dizaine que comporte la table.
La requête correcte sera dans ce cas :
$requete=mysql_query("select pseudo,texte FROM table") or die(mysql_error());
Si il ne vous faut qu’une dizaine de résultats à renvoyer (par exemple dans le cas des news si vous en avez 100 je ne pense pas que vous allez toutes les afficher sur votre page d’accueil mais plutôt en sélectionner une dizaine.
La requête correcte sera du style $requete=mysql_query(« select pseudo,texte FROM table LIMIT 0,10 ») or die(mysql_error());
Le 0 dans la clause LIMIT est le premier enregistrement à prendre en compte. Le 10 est le nombre d’enregistrements renvoyés par la requête. Si vous souhaitez commencer à la 25 eme news et en rapporter 10, la clause LIMIT sera comme ceci :
LIMIT 25,10
Une troisième erreur, c’est la connexion inutile à la base en début de page.
Il faut faire des connexions avant les requêtes et les refermer juste après :
//AFFICHAGE DONNEES
print('<html><head>etc');
//CONNEXION A LA BASE
$connexion_sql = @mysql_connect($sql_serveur,$sql_user,$sql_passwd);
if(!$connexion_sql) {echo "Connexion impossible à la base de données. Réessayez plus tard."; exit;}
mysql_select_db($sql_bdd, $connexion_sql);
//REQUETES (on suppose que la table contien une dizaine de champs)
$requete=mysql_query("select * FROM table WHERE rubrique=1 AND pseudo=tata") or die(mysql_error());
mysql_close($connexion_sql); //FERME LA CONNEXION
while($r=mysql_fetch_array($requete) {
$donnee=$rpseudo;
$texte=$rtexte;
print($donnee.' tatata par '.$pseudo);
}
Il est de bon ton de faire un mysql_free_result à l’issue du traitement du select, de façon à ne pas encombrer la mémoire vive avec des données inutiles.
Vous verrez que la navigation sera déjà plus fluide, surtout si votre table contient beaucoup d’enregistrements.
Justement on y vient, quand une table contient beaucoup de données, il faut l’optimiser pour que le disque dur du serveur ne scanne pas toute la table avant de retourner un résultat correct.
On utilise pour cela les INDEX. Un index est en gros un lexique (quand vous regardez la table des matières d’un livre c’est plus rapide pour chercher une info bah pour l’index c’est pareil).
Cependant, il ne faut indexer que les champs importants. En effet un index ralentit les requêtes d’insertion (nous verrons cela plus loin).
Dans notre requête d’origine, nous utilisons seulement deux champs principaux : pseudo et rubrique (ceux qui se trouvent dans la clause WHERE).
On va donc placer un index sur ces deux champs. Rendez vous dans PHPMYADMIN, cliquez dans le menu de gauche sur votre table et cliquez ensuite à droite sur « créer une clé sur n colonnes ».
Vous mettez « 2 » dans la case et envoyez le formulaire.
Et là on va voir une autre méthode d’optimisation : la taille des index. Plus un index est petit, plus à taille égale il peut contenir de valeurs. Si votre champ pseudo est un champ de type VARCHAR (avec donc une longueur maximale de 255 caractères), il est inutile d’indexer les 255 caractères. Un pseudo peut avoir une longueur de 20 caractères maximum. Et comme les pseudos semblables sont très rares, on peut n’indexer que les 5 premiers caractères du champ pseudo.
Dans « taille » mettez le chiffre 5 après avoir sélectionné dans la liste déroulante le champ pseudo.
Passons maintenant au type INT, SMALL INT, TYNYINT
Le type le plus léger est le TINYINT, qui ne peut contenir que des valeurs allant de -128 à 127. Pour un forum (le champ rubrique correspond bien à un forum, c’est largement suffisant). Si votre forum n’a que 9 rubriques, dans ce cas la largeur à indexer sera 1 (2 pour des chiffres allant jusqu’à 99).
Non seulement vous économisez de la mémoire, mais la recherche dans l’index sera plus rapide.
/***********************************************************/
/*OPTIMISATIONS DE LA LONGUEUR DES CHAMPS ET DE LEUR CHOIX */
/***********************************************************/
Pour un champ contenant un pseudo, il est inutile d’avoir recours à un champ texte. Un champ VARCHAR limité à 30 caractères suffit amplement.
Voici une description de chaque type de champ (pompée sur phpinfo.net et remaniée merci à eux)
TINYINT UNSIGNED ZEROFILL taille en octets : 1 Très petit entier. Va de -128 à 127, de 0 à 255 si non signé
SMALLINT(M) UNSIGNED ZEROFILL taille en octets : 2 Petit entier. Va de -32 768 à 32 767, de 0 à 65 535 si non signé
MEDIUMINT(M) UNSIGNED ZEROFILL taille en octets : 3 Entier moyen. Va de -8 388 608 à 8 388 607, de 0 à 16 777 215 si non signé
INT(M) UNSIGNED ZEROFILL taille en octets : 4 Va de -2 147 483 648 à 2 147 483 647, de 0 à 4 294 967 295 si non signé
INTEGER (M) UNSIGNED ZEROFILL taille en octets : 4 Synonyme de INT
BIGINT (M) UNSIGNED ZEROFILL taille en octets : 8 Grand entier. Va de -9 223 372 036 854 775 808 à 9 223 372 036 854 775 807, de 0 à 18 446 744 073 709 551 615 si non signé
FLOAT(precision) ZEROFILL taille en octets : 4 si precision <= 24 ou 8 si 25 <= precision <= 53 Flottant
FLOAT (M,D) ZEROFILL taille en octets : 4 Flottant à precision simple. Va de -1.175494351E-38 à 3.402823466E+38
DOUBLE PRECISION (M,D) ZEROFILL taille en octets : 8 Synonyme de DOUBLE
REAL (M,D) ZEROFILL taille en octets : 8 Synonyme de DOUBLE
DECIMAL (M,D) ZEROFILL M+2 octets si D > 0, M+1 octets si D = 0 (D+2, si M < D) Flottant stocké comme une chaîne de caractères
NUMERIC(M,D) >ZEROFILL M+2 octets si D > 0, M+1 octets si D = 0 (D+2, si M < D) Synonyme de DECIMAL
DATE taille en octets : 3 Date ‘YYYY-MM-DD’ allant de ‘1000-01-01’ à ‘9999-12-31’
DATETIME taille en octets : 8 Date et heure ‘YYYY-MM-DD HH:MM:SS’ allant de ‘1000-01-01 00:00:00’ à ‘9999-12-31 23:59:59’
TIMESTAMP (M) taille en octets : 4 Date allant de ‘1970-01-01 00:00:00’ à quelque part en 2037. L’affichage dépend de M : YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, ou YYMMDD pour, respectivement : 14, 12, 8, ou 6
TIME taille en octets : 3 Heure ‘HH:MM:SS’, allant de ‘-838:59:59’ à ‘838:59:59’
YEAR[(2|4)] taille en octets : 1 Année à 2 ou 4 chiffres allant de 1901 à 2155 pour 4 chiffres et de 1970-2069 pour 2 chiffres
[NATIONAL] CHAR(M) [BINARY] M octets, 1 <= M <= 255 Chaîne de charactères (jusq'à 255) remplie d'espaces à la fin. Ces espace sont enlevés lors d'une requête. BINARY entraine une recherche sensible à la casse
BIT taille en octets : 1 Synonyme de CHAR(1)
BOOL taille en octets : 1 Synonyme de CHAR(1)
CHAR taille en octets : 1 Synonyme de CHAR(1)
[NATIONAL] VARCHAR(M) [BINARY] L+1 octets, où L <= M et 1 <= M <= 255 Chaîne de charactères (jusqu'à 255). BINARY entraine une recherche sensible à la casse
TINYBLOB L+1 octets, où L < 2^8 Chaîne de charactères (jusqu’à 255), sensible à la casse
TINYTEXT L+1 octets, où L < 2^8 Chaîne de charactères (jusqu’à 255), insensible à la casse
BLOB L+2 octets, où L < 2^16 Chaîne de charactères (jusqu’à 65 535), sensible à la casse
TEXT L+2 octets, où L < 2^16 Chaîne de charactères (jusqu’à 65 535), insensible à la casse
MEDIUMBLOB L+3 octets, où L < 2^24 Chaîne de charactères (jusqu’à 16 777 215), sensible à la casse
MEDIUMTEXT L+3 octets, où L < 2^24 Chaîne de charactères (jusqu’à 16 777 215), insensible à la casse
LONGBLOB L+4 octets, où L < 2^32 Chaîne de charactères (jusqu’à 4 294 967 295), sensible à la casse
LONGTEXT L+4 octets, où L < 2^32 Chaîne de charactères (jusqu’à 4 294 967 295), insensible à la casse
ENUM(‘value1′,’value2’,…) 1 ou 2 octets, selon le nombre d’énumérations (65 535 valeurs maximum) Enumération de 65 535 chaînes différentes
SET(‘value1′,’value2’,…) 1, 2, 3, 4 ou 8 octets, selon de nombre de membres de l’ensemble Ensemble qui peut contenir de 0 à plusieurs valeurs choisies dans une liste de valeurs
On constate donc qu’utiliser un type INT pour stocker les 10 rubriques d’un forum est inadapté. On préfèrera un type TINYINT limité en taille à 2 (pour afficher des nombres jusqu’à 99 car deux chiffres).
Le type text pour stocker un pseudo est lui aussi inadapté. Préférez un type VARCHAR limité à 30 caractères (c’est déjà beaucoup).
/*******************************/
/*LES CHAMPS NULL ET LES AUTRES */
/*******************************/
Si dans votre table il y aura des champs qui pourront contenir une valeur nulle, vous devez spécifier l’option NULL dans PHPMYADMIN.
Si vous êtes sûr que le champ sera toujours rempli, même par un zéro, il faut mettre l’option NOT NULL et dans défaut ne rien mettre.
Un champ de type NOT NULL accélère les requêtes, et donc les recherches dans la base.
/***************/
/*LES JOINTURES */
/***************/
Lorsque l’on a besoin de sélectionner des données dans plusieurs tables, on utilise une ou plusieurs jointures. Voyons un exemple avec deux tables, nommées « adresses » et « comptes ». Normalement il aurait mieux valu placer un champ adresse dans la table comptes mais si il y a beaucoup plus de champs dans la table adresse une jointure est préférable. De toute façon c’est pour l’exemple :
Contenu de la table COMPTES :
ID | PSEUDO | TELEPHONE |
1 | pseudo1 | 0102030405
Contenu de la table ADRESSES :
ID | ADRESSE
1 | 2 rue de paris
Pour sélectionner l’adresse des membres, on a ici un champ commun, ID.
La requête sera construite comme ceci :
SELECT comptes.pseudo,compte.telephone,adresses.adresse FROM comptes LEFT JOIN adresses ON comptes.id=adresses.id WHERE pseudo=pseudo1
LEFT JOIN …. ON
L’ordre des tables dans le LEFT JOIN est important. Quand dans ma requête j’ai écrit : comptes LEFT JOIN adresses il y a une raison : si la table adresses ne contient rien (que le membre n’a pas encore rempli son adresse par exemple), l’enregistrement sera quand même renvoyé. Si on avait mis comme requête adresses LEFT JOIN comptes et que le champ adresses était nul, il n’y aurait pas eu d’enregistrement retourné.
Pour info, on placera un INDEX sur le champ ID pour accélérer les recherches et donc la jointure.
/****************************************************/
/*OPTIMISER LES REQUETES EN AJOUTANT DES PARAMETRES */
/****************************************************/
Quand on sait d’avance à peu près si une requête va renvoyer un grand nombre de résultats ou non, on peut optimiser un peu la requete :
$requete= »SELECT SQL_SMALL_RESULT pseudo FROM table WHERE pseudo=pseudo1″;
Là on sait qu’il n’y aura qu’un enregistrement de retourné. On ajoute donc la condition SQL_SMALL_RESULT.
Si un grand nombre d’enregistrements sont retournés, on utilise l’option SQL_BIG_RESULT
/********************************************************/
/*TRAITER LES ENREGISTREMENTS RETOURNES PAR UNE REQUETE */
/********************************************************/
Lorsque l’on effectue une requête, il faut savoir traiter efficacement les enregistrements :
$requete="SELECT pseudo,sujet,message FROM forum LIMIT 20";
$requete=mysql_query($requete);
Pour afficher le résultat, on a l’option très lourde : mysql_result. (à proscrire mais alors vraiment par pitié ne mettez pas ça sur les serveurs sauf si vous ne retournez qu’un enregistrement).
Il y a déjà mieux, la commande mysql_fetch_array($requete)
On utilise une boucle comme ceci :
while($r=mysql_fetch_array($requete)) {
$pseudo=$r[pseudo];
$sujet=$r[sujet];
$message=$r[message];
}
Mais il y a encore plus rapide que mysql_fetch_array, c’est la commande mysql_fetch_row($requete)
Elle s’utilise de la même manière mais on utilise les indices numériques, ce qui est plus rapide que de spécifier un champ texte :
while($r=mysql_fetch_row($requete)) {
$pseudo=$r[0];
$sujet=$r[1];
$message=$r[2];
}
Les chiffres ne sont pas placés au hasard. C’est l’ordre de sélection des champs dans la requête qui les détermine. Comme j’ai sélectionné d’abord pseudo, puis sujet et enfin message dans ma requête, les chiffres sont 0 pour pseudo, 1 pour sujet et 2 pour message. On aurait pu écrire ça (ne confondez pas ) :
while($r=mysql_fetch_row($requete)) {
$sujet=$r[1];
$pseudo=$r[0];
$message=$r[2];
}
Merci beaucoup. J’ai enfin élucidé un grand problème pour moi (et pour d’autres) : je liais mes tables avec WHERE au lieu de LEFT JOIN, et du coup, quand je fesais une requete SELECT ou certains champs étaient nuls, ca ne m’affichait pa les lignes qui comprenaient des champs nuls. Avec LEFT JOIN ca les affiche tous.
(dur d’expliquer x| )
Sinon, tu parles de fetch_row et mysql_result, mais il y a aussi fetch_object et fetch_assoc.
Et fetch_assoc est plus rapide que fetch_array. Tu devrais donc plutot conseiller ce premier. (fetch_row reste le plus rapide, on est d’accord)
Bon ben bonne continuation à tous !
Ca m’a sauvé la vie, merci.
I enjoyed this site. Totally awesome and I guess this can be very usefull in life!
Très utile, merci