Pour pouvoir utiliser une base de données MySQL dans de bonnes conditions, vous devez connaître la méthode pour créer correctement ses tables. Nous étudierons donc les différents types de champs disponibles, puis la manière d'optimiser ses tables.

1- Les types de champs

Chaque table d'une base de données est composée d'un ou plusieurs champs (les colonnes). Il existe plusieurs types de champs :

Les champs numériques

Le nombre d'octets détermine la place que prend chaque champ et influe, donc, sur la grandeur des nombres que l'on peut y stocker.

• Par exemple, un colonne de type TINYINT occupe 1 octet. Vous pouvez y stocker 2^8 (256) valeurs différentes. C'est à dire des nombres allant de -128 à +127 si on prend en compte le signe, ou de 0 à 255 si le nombre est non signé.
• Une colonne de type MEDIUMINT occupe 3 octets (24 bits) et permet donc de stocker 2^24 (3*8), soit 16 777 216, valeurs différentes.
• En pratique, les champs INT et BIGINT seront donc rarement utiles.

Les types entiers ont un paramètre optionnel permettant de définir le nombre de caractères utilisés pour représenter les valeurs de la colonne. Un champ de type TINYINT(2) permet donc de stocker des nombres entre -9 et 99. Si vous entrez un nombre plus important (154 par exemple), la valeur par défaut du paramètre sera alors utilisée (4 dans le cas de TINYINT). Bien entendu, il est impossible d'insérer des valeurs supérieures à 255 dans un champ de type TINYINT, le nombre serait alors tronqué sans provoquer de message d'erreur.

Nous ne nous attarderons pas sur les types décimaux. Il faut simplement savoir que FLOAT et DOUBLE (REAL est un alias) ont deux paramètres optionnels : le premier est identique au paramètre des types entiers et le second indique la place des chiffres après la virgule. Le type DECIMAL (NUMERIC est un alias) est par contre différent : il est stocké sous forme de chaîne et possède deux paramètres obligatoires, le premier indique le nombre de chiffres, et le second le nombre de ces chiffres utilisés pour la partie après la virgule. Vous pouvez consulter le manuel pour en savoir plus.

Concernant le signe, nous avons vu que les champs pouvaient être signés ou non. Les champs sont signés par défaut. Pour demander à MySQL de ne pas tenir compte du signe, il faut ajouter l'argument UNSIGNED à la suite de la définition de la colonne (ex : TINYINT(4) UNSIGNED)

Enfin, vous pouvez aussi utiliser l'attribut AUTO_INCREMENT pour, vous l'avez deviné, incrémenter automatiquement un champ.

Les chaînes de caractères

Les champs de type CHAR et VARCHAR ont un argument obligatoire : leur taille. Dans le cas de CHAR, il s'agit de la taille de la chaîne. Vous pouvez y stocker des chaînes plus courtes mais les caractères restants seront comblés par des espaces. Pour VARCHAR, par contre, il s'agit de la taille maximale de la chaîne.
Lorsque vous utilisez des chaînes ayant une taille variable, un champ de type VARCHAR tiendra donc moins de place, mais la table peut s'avérer être moins performante qu'avec des CHAR. Nous y reviendrons par la suite dans ce tutorial.
Il faut noter que ces champs ne sont pas sensibles à la casse. Autrement dit, lors d'une recherche, "texte" sera identique à "TexTe". En effet, pour rendre ces types de colonnes sensibles à la différence entre majuscule et minuscule, il faut ajouter l'argument BINARY dans la définition du champ (ex : CHAR(25) BINARY).

Les quatre types de champs suivants (BLOB et TEXT) n'ont, quant à eux, aucun argument. Ils sont utilisés pour stocker tout type de données (texte, images, etc). Il faut noter qu'une colonne de type BLOB est sensible à la casse tandis qu' une colonne de type TEXT ne l'est pas.

Précision ! Lorsque l'on parle d'être sensible à la casse ou non, cela concerne uniquement les recherches et les tris. Ainsi, un champ de type TEXT peut contenir des majuscules (et les affichera telles quelles) mais ne fera pas la différence entre majuscule et minuscule lors des tris et des recherches.

Enfin, les types ENUM et SET permettent de stocker une ou plusieurs (pour SET) valeurs parmi un ensemble défini de valeurs. On peut donc associer ENUM aux champs de type radio, et SET aux champs de type checkbox. A noter que ces deux types peuvent contenir des valeurs nulles.

Date et heure

Chacun de ces différents types a un argument optionnel permettant de changer légèrement le formatage, mais ce n'est pas important, nous ne nous y attarderons donc pas.

Il faut, par contre, savoir que MySQL représente les dates ainsi : l'année, suivie du mois, puis du jour. Le 24 février 2005 est donc représenté sous la forme "2005-02-24". C'est de cette manière qu'il est possible de trier les dates.

2- Exemple

Voici un exemple de table :

CREATE TABLE test (
 id int(11) NOT NULL auto_increment,
 titre varchar(48) NOT NULL,
 categorie tinyint(3) unsigned,
 PRIMARY KEY (id)
);

3- Optimiser ses tables

Ajouter des INDEX

Une fois votre table créée, pensez à ajouter des INDEX sur les champs qui sont souvent utilisés dans les comparaisons et le tri. Nous expliquerons comment les utiliser en détail dans un prochain tutorial. Pour l'instant, vous pouvez consulter le manuel.

Il ne faut cependant pas en abuser. Ajoutez uniquement les INDEX vraiment utiles car ils occupent de l'espace disque et les opérations d'insertion et de modification sont ralenties. MySQL risque également d'avoir du mal à optimiser les requêtes si il y a trop d'INDEX.

Privilégier les nombres aux chaînes

Les opérations numériques sont plus rapides que les opérations sur les chaînes.

Utiliser ENUM et SET pour les chaînes lorsque cela est possible

Si un champ fait appel à un nombre défini de valeurs, préférez utiliser une colonne de type ENUM ou SET à une colonne de type chaîne.

Réduire la taille de ses champs

Essayez de réduire au maximum la taille de vos champs. Il vaut mieux utiliser, par exemple, SMALLINT que INT lorsque vous n'avez pas besoin de stocker des nombres trop importants. Pensez aussi à utiliser des colonnes de type UNSIGNED lorsque vous n'utilisez pas le signe négatif, cela permet de doubler la valeur des nombres que vous pouvez stocker (65535 pour un SMALLINT UNSIGNED).

Utiliser si possible des chaînes de longueur fixe

MySQL peut utiliser deux types de table :

• Une table fixe lorsqu' elle est composé uniquement de champs de longueur fixe (pas de VARCHAR, BLOB/TEXT et DECIMAL).
• Une table dynamique lorsqu' elle contient un champ de taille variable.

Les tables fixes sont plus rapides. Il vaut donc mieux utiliser uniquement des champs de type CHAR plutôt que des champs de type VARCHAR. C'est, par exemple, ce que fait Phorum. Si vous regardez le schéma de la table, vous remarquerez qu'il n'y a pas de champ VARCHAR et que le corps du message (de type TEXT) est même stocké dans une table à part. Cela permet d'accélérer l'affichage de la liste des messages

Privilégier NOT NULL à NULL

Un champ de type NULL occupe 1 octet supplémentaire. Il vaut donc mieux définir vos colonnes en NOT NULL lorsque cela est possible.

4- Conclusion

Créer une table n'est pas très compliqué. Par contre, créer une table optimale est plus complexe et nécessite un peu d'expérience. Pour en savoir plus, je vous conseille d'acheter le livre "MySQL" de Paul Dubois. Vous pouvez également consulter le manuel en ligne et poser vos questions dans les forums de PHPFrance.