Discussion :

[koantik]

Bonjour a tous,

Je travaille sur mysql5 sur les moteurs MyISAM.

j'ai quelques questions sur le fonctionnement ou plutôt sur la création des index sur les moteurs MyISAM.

J'ai 2 tables
1 première table de 220 millions d'enregistrements pour un poids de données de 9.7 Go, et avec un index ( en dehors de la clé primaire ) sur un entier qui me coute 8.5 Go

1 deuxième table de 2.6 millions d'enregistrement pour un poids de données de 3.1 Go, et avec un index ( en dehors de la clé primaire ) sur un entier qui me coute 179 Mo


Je ne comprend pas pourquoi sur la première table l'index coute aussi cher en place.
Les questions sont donc les suivantes :

Comment peut on simuler/comprendre le poids que va prendre la construction d'un index sur un champ sur une table en moteur MyISAM ?

Quelles sont les questions que je pourrais me poser pour savoir si j'ai mal configurer un paramètre ( je reste vague car je ne vois pas .. )

Merci

[CinePhil]

Un petit coup d'OPTIMIZE sur la première table réduirait peut-être la taille des index en faisant le ménage des lignes supprimées de la table ?

[koantik]

J'avais fait un optimize en espérant que ca diminue la taille mais ca n'a pas été le cas. Enfin j'avais du gagné ~100Mo sur ~ 8Go.

j'avais fait un repair avant l'optimize.


J'ai aussi droppé l'index et reconstruit l'index avec le même résultat.

Merci CinePhil en tout cas d'intervenir

[CinePhil]

Est-ce que par hasard l'entier de la seconde table serait un INT (4 octets) alors que celui de la première serait un BIGINT (8 octets) ?

[koantik]

on tient peut être quelque chose là !

mais attention les yeux

sur la première table de 220 millions d'enregistrements l'index de 8.5 Go.
Le champ indexé est un int.

sur la première table de 2.6 millions d'enregistrements l'index de 179 Mo.
Le champ indexé est un bigint.

C'est l'inverse.
je vais tester en changeant le typage si ca n'a pas une incidence. Sait on jamais !

[CinePhil]

D'après la doc MySQL, un INT est stocké sur 4 octets et BIGINT sur 8 octets; J'en déduis que le nombre entre parenthèses qu'on voit accolé au type dans phpMyAdmin n'a pas d'importance mais peut-être en a t-il finalement une ?

Il n'y a que la colonne de type entier qui est indexée (hors clé primaire) dans les deux tables ?

EDIT :
Regarde à la fin de ce message une estimation de taille d'index pour une grosse table par IBM DB2.

[koantik]

il y a d'autres index mais ils n'ont pas d'incidence majeure sur le poids du fichier d'index.
Pour le savoir j'ai droppé tous les index sauf ces 2 la.

J'ai fait le test pour bigint.

J'ai passé dans la première table le champ en bigint et la taille de l'index a grimpé, ce qui parait logique. ( j'ai fait le test sur une autre base ou j'ai moins d'enregistrements )

L'augmentation (sur la base de test) :taille du fichier d'index avec int 1.2Go , avec bigint 1.3Go pour 1.5Go de données et 37 millions de ligne

Dans la 2eme table j'ai passé le champ en int et le poids du fichier d'index a diminué.

Je vais faire le test en sur la première table en ne gardant que l'index int pour voir l'augmentation.

[ericd69]

salut,

le nombre représente le nombre d'octets utilisés pour la valeur entière, comme pour date ou autre... à ne pas confondre avec ce qui ce passe avec un char ou vahar dont la taille dépend u charset et où le nombre entre parenthèses repérsente le nombre de caractère et pas d'octet réservé (en ut8, char(5) représente 5x5=25 octets...)

dans tes requêtes mysql doit transtyper le plus petit nombre dans le type du plus long avant de faire la comparaison...

il vaut mieux uniformiser les type des index qui seront comparé pour évité ça...
BIGINT est juste un alias de int(8) et INt sans rien de int(4)...

int(4) te permet de gérer 4 milliards de valeurs environ, int(8) 16 milliards de milliards...

après à toi de voir ce qui va le mieux en fonction des évolutions et de tes besoins de place aussi...

à savoir que tu peux préciser une taille à l'octet près mais, pour des performances optimales, je te conseille des multiples de 2 du genre 2, 4, 8 qui correspondent aux tailles de registres des processeurs... ce qui évite au sgbd d'utiliser des méthodes de comparaison non optimisées...

[CinePhil]

le nombre représente le nombre d'octets utilisés pour la valeur entière

BIGINT est juste un alias de int(8) et INt sans rien de int(4)

Es-tu sûr de ça ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE test_innodb ADD numero INT NOT NULL

phpMyAdmin m'affiche maintenant INT(11) dans la structure de la table !
Je dirais que c'est plutôt le nombre de chiffres, y compris le signe :

Type 	Octets 	De 	A
INT 	4 	-2147483648 	2147483647

Il y a 10 chiffres + le signe dans la valeur mini.

[ericd69]

tu as raison... j'étais fatigué hier soir... quand on parle dans différents forums on finit par faire des raccourcis

c'est en fait moi qui me suis mal expliqué... je donnais l'équivalence avec les type numérique c/c++ sous-jacents...

en mysql, entre parenthèses, si tu le spécifie, c'est la taille d'affichage des nombre significatif... ce qui est permet de combler en fait l'affichage par des 0 à gauche si le nombre a un nombre de chiffre inférieur...

lire ça: http://dev.mysql.com/doc/refman/5.0/...ric-types.html

là je parlais en terme de stockage interne réel...

pour les nombres décimaux tu peux rajouter le nombre de chiffres décimaux affichés...

pour la syntaxe et la liste des types numériques lire ça:http://dev.mysql.com/doc/refman/5.0/...-overview.html

depuis les dernières versions de mysql, ils ont introduit (enfin) la notion de mathématiques en précision libre... les nombres qui utilisent les types numériques en précision libres sont stocké tels quels... ce qui peut engendré des tailles et des performance de calculs très disparates...

pour les index entiers il faut donc garder à l'esprit que si tu compare des types différents, il y aura toujours une conversion du plus petit type vers le plus gros avant la comparaison de valeur... donc plus de traitement moins de performances...

donc autan rationaliser à la conception pour éviter ce genre de petites mésaventures...

rappelons aussi que ça joue fatalement sur les tailles d'index... donc y a intérêt sur des grosses table à bien penser ça...