Choisir les bons types de champ SQL

Commençons par un exemple de table mal optimisée (créée avec phpMyAdmin) :

Je teste une requête sur cette table, voilà la requête :
Code : SQL
Et ensuite, je regarde le poids de cette ligne ("Espace utilisé" dans phpMyAdmin) : 52 octets.

Essayons maintenant avec cette configuration :

Les données restent exactement les mêmes sauf que le poids, lui, passe à 28 octets.

Bref sur une table aussi simple que celle-là contenant 1000 lignes, on peut économiser 23 Ko !

Optimiser ses structures de table SQL consiste à choisir les formats les plus petits possibles pour stocker ses données.

En gros, c'est comme si vous deviez envoyer à 1000 personnes :

• un courrier
• un outil
• un mp3

Et que vous deviez commander vos enveloppes et cartons en lots.
Si vous n'envoyez que des petits courriers vous prenez un lot de 1000 petites enveloppes, pas des enveloppes A3, vous allez perdre des sous.
L'outil peut être gros, il vous faut un carton extensible
Et les mp3, c'est fragile, si vous ne mettez pas de papier-bulle, ils vont se casser.

En SQL, il faut aussi adapter le conteneur au contenu. Voyons maintenant les formats disponibles.

Pourquoi préférer les types numériques aux textuels ?

Tout ce qui peut être rapporté à un nombre entier doit être enregistré dans un format INT (TINYINT, SMALLINT, INT...).


Parce qu'au final, tout sera enregistré en binaire dans des petits octets.
Un octet peut prendre 256 formes. Pour une valeur numérique, ça revient à pouvoir stocker un nombre entre 0 et 255, et pour un texte, c'est une lettre ou un symbole ou un chiffre. Ce qui implique que si vous prenez un type texte limité à 1 octet pour stocker un nombre, vous irez de 0 à 9 et c'est tout ! On est loin des 255.

Comment choisir le bon INT ?

Et oui, des INT, il y en a pas mal. Ils ont chacun une taille définie et des limites que voici :

Les nombres entiers en SQL

Type	Poids en octets	Minimum	Maximum
TINYINT	1	-128	127
SMALLINT	2	-32 768	32 767
MEDIUMINT	3	-8 388 608	8 388 607
INT	4	-2 147 483 648	2 147 483 647
BIGINT	8	-9 223 372 036 854 775 808	9 223 372 036 854 775 807

Il faut donc choisir le type dont les bornes encadrent vos nombres au plus près.

Le nombre à virgule

Pour les nombres à virgule, c'est moins compliqué, vous avez FLOAT et DOUBLE. (Il y a aussi DECIMAL mais il revient à peu près au même que DOUBLE).
La seule chose qui différencie les deux types, c'est la précision. 24 chiffres pour FLOAT et 53 chiffres pour DOUBLE.

Les attributs des types numériques

Et bien il y a mieux que de soustraire 127 à la taille pour l'enregistrer en TINYINT, vous pouvez choisir l'attribut UNSIGNED qui signifie non-algébrique, cela limite les champs aux nombres positifs. Le minimum des tous les types devient alors 0 et les maximums sont les suivants :

Les maximums des types

Type	Poids en octets	Maximum
TINYINT	1	255
SMALLINT	2	65 535
MEDIUMINT	3	16 777 215
INT	4	4 294 967 295
BIGINT	8	18 446 744 073 709 551 615

Talus me propose de vous expliquer pourquoi le maximum augmente en UNSIGNED.
Les plus curieux n'ont donc qu'à ouvrir le secret ci-dessous.
Secret (cliquez pour afficher)

Et le deuxième attribut qui s'avère utile moins souvent mais quelques fois tout de même : ZEROFILL. Comme son nom l'indique, il remplit les champs avec des zéros.

N'ayez crainte, aucun mal ne sera fait à aucun d'entre nous, il s'agit du chiffre zéro et non des utilisateurs de ce site. (Comment ça, c'est pas drôle )

Donc si j'enregistre 23 dans un TINYINT UNSIGNED ZEROFILL (ZEROFILL implique l'attribut UNSIGNED), SQL enregistrera 023. Ou encore dans un SMALLINT UNSIGNED ZEROFILL : 00023.


Et bien, c'est là, qu'intervient la taille des champs.
Code : SQL
Ou sur phpMyAdmin :

Il existe 5 types temporels.


TIME n'a rien en commun avec le fonction time() de php
Et TIMESTAMP n'a rien à voir avec le timestamp de Linux.

En SQL, on stocke la date et l'heure comme ça :

Type	Taille en octets	Format
DATE	3	AAAA-MM-JJ
DATETIME	8	AAAA-MM-JJ HH:MM:SS
TIME	3	HH:MM:SS
TIMESTAMP	4	AAAAMMJJHHMMSS
YEAR	1	AAAA

Les données ont des limites logiques, les minutes et secondes sont comprises entre 0 et 59, les heures entre 0 et 23, les jours entre 0 et 28 à 31 selon le mois, les mois entre 1 et 12.

Seules les années sont un peu spéciales. On lit parfois que les années peuvent aller de 1000 à 9999. Hors si certains systèmes SQL le permettent, dans le cas qui nous concerne, nous qui voulons optimiser nos tables, nous considèrerons les limites de YEAR qui limite la taille de l'année à 1 octet (souvenez-vous, il y a 256 solutions donc pour aller de 1000 à 9999, on est obligé d'utiliser 2 octets, c'est pas bon). Ces limites sont : 1901 et 2155.


Voilà un peu tout ce que vous pouvez faire avec des champ de type temporel.

• CURRENT_DATE() ou CURDATE() pour obtenir la date courante (exemple : 2009-05-21)
• CURRENT_TIME() ou CURTIME() pour obtenir l'heure (exemple : 19:02:43)
• CURRENT_TIMESTAMP() ou NOW() pour obtenir la date et l'heure (exemple : 2009-05-21 19:02:43)
• Les champs ci-dessus peuvent être convertis en entier en faisant CURTIME()+0 qui donne 190243
• YEAR() récupère l'année d'un champs (exemple : YEAR(`date`) donne 2007)
• MONTH() récupère le mois
• DAYOFMONTH() récupère le jour du mois
• HOUR() récupère l'heure
• MINUTE() récupère la minute
• SECOND() récupère la seconde
• MONTHNAME() récupère le nom anglais du mois
• DAYNAME() récupère le nom anglais du jour de la semaine

Code : SQL - Exemple d'utilisation
Ce code renverra par exemple :
Code : Autre - Résultat

Vous pouvez enfin comparer tous les champs de type temporel avec >, <, =...

Il existe deux types quasi-identiques pour sélectionner des valeurs prédéfinies : SET et ENUM.

Le type SET

Les valeurs possibles de `piece` sont donc 'pile', 'face' mais aussi 'pile' et 'face' (noté 'pile,face' dans une requête SQL) et aucune valeur (noté ''). Et tout ceci ne prend qu'un quart d'octet alors qu'il faudrait 4 octets pour stocker le texte dans un VARCHAR. Bien sûr on ne peut pas couper un octet en 4, mais je signifiais par là qu'il y a encore de la place pour 6 autres valeurs au choix dans ce champ SET.
Voilà comment est stocké en binaire les 4 valeurs possibles pour un champ SET qui contient 2 valeurs prédéfinies :

Valeur SQL	Valeur binaire
''	00000000
'pile'	00000001
'face'	00000010
'pile,face'	00000011

Donc pour chaque bit de l'octet, une valeur peut être sélectionnée ou non (1 ou 0). Les SET stockent ensuite ce nombre binaire comme les types numériques. Il peut donc peser 1, 2, 3, 4 ou 8 octets selon le nombre de valeurs prédéfinies :

Nombre de valeurs	Taille en octets
1 à 8	1
9 à 16	2
17 à 24	3
25 à 32	4
33 à 64	8

Le type ENUM

Le type ENUM lui ne peut pas prendre plusieurs valeurs. C'est pile ou face... Ou tranche à la limite mais pas de mélange. Cela permet en revanche de prendre beaucoup moins de place. À chaque valeur est associée un nombre converti en binaire et stocké dans un octet. Donc de 2 à 255 valeurs prédéfinies, le ENUM ne prend qu'un seul octet. Et bien que sa taille maximale soit de 2 octets, cela permet tout de même d'aller jusqu'à 65 535 valeurs.

Bon, un tableau de plus ou de moins ne pourra faire de différence, je vais donc simplement résumer les possibilités directement tirées de la doc en essayant de trier un peu les informations et de les vulgariser.


La taille minimale de tous les types textuels est de 1 caractère. Dans le tableau suivant, je considèrerai que M est la taille maximale (choisie dans la colonne Taille/Valeurs de phpMyAdmin ou entre parenthèses dans une requête SQL). Et L sera la longueur du texte enregistré à l'insertion (elle est donc variable dans une même table)

Type	Taille maximale	Espace requis
CHAR	255	M
VARCHAR	255	L+1
TINYBLOB, TINYTEXT	255	L+1
BLOB, TEXT	65 535	L+2
MEDIUMBLOB, MEDIUMTEXT	16 777 215	L+3
LONGBLOB, LONGTEXT	4 294 967 296	L+4

Différences entre BLOB et TEXT

BLOB stocke les informations en binaire directement, ce qui permet par exemple d'y insérer le contenu d'une image ou d'un exécutable mais on peut aussi y mettre du texte comme dans TEXT (il n'y a alors pas besoin de définir un interclassement) et la seule différence restante est la suivante :

Citation : doc MySQL
Donc si on prend une table qui ressemble à ça :

text	blob
Camion	Camion (en binaire)
camion	camion (en binaire)
Voiture	Voiture (en binaire)
voiture	voiture (en binaire)

Et que vous lancez la requête suivante :
Code : SQL
Vous obtenez deux résutats : 'Camion' et 'camion'.

Alors que :
Code : SQL
ne donne qu'un seul résultat : 'camion'.

Reprenons donc maintenant la table que je vous avais présentée au départ comme plus optimisée :

id SMALLINT(4) au lieu de INT(4)

Si je sais que id sera un nombre à 4 chiffres (ici, ce serait par exemple si je ne comptais pas inscrire plus de 9999 lignes dans cette table) alors le SMALLINT suffit puisqu'il va jusqu'à 65 535.

pseudo VARCHAR(40) au lieu de TEXT

On aurait aussi pu choisir TINYTEXT ou TINYBLOB. Tous prendrons comme espace la longueur du pseudo + 1 et le pseudo sera limité à 40 caractères sans avoir besoin d'aucun traitement. (Pour un pseudo, c'est plutôt bien de ne pas pouvoir dépasser une certaine longueur, sinon gare à l'affichage, les pseudo qui dépassent l'écran...). Niveau optimisation, on ne gagne qu'un seul octet mais c'est déjà ça.

date DATE au lieu de VARCHAR(10)

Ici on gagne 7 octets (soit 70% de la place initialement occupée) et les dates sont forcément valides. Pas besoin de vérifier que la date insérée est au bon format. Vous pouvez insérer mysql_real_escape_string($_POST['date']) directement dans la table sans risquer d'avoir des blagues du genre "aujourdhui" qui aurait pu être écrit dans un VARCHAR.

design_choisi SET au lieu de VARCHAR(16)

Toujours plus fort, là on gagne 15 octets (soit 94%). Avec 3 choix, SET ne prend que 1 octet. On aurait aussi pu choisir ENUM mais pour le même prix, on peut avoir la possibilité de n'avoir aucun design, ou plusieurs (après, c'est à vous de voir...)

telephone INT(10) au lieu de VARCHAR(10)

Gain de 6 octets (60%) et l'attribut ZEROFILL se charge de rajouter les zéros qui manquent pour faire 10 chiffres.