indexation de toutes les colonnes

14 déc. 2008, 23:18

Bonsoir,

Question simple, est ce que cela pose un problème d'indexer toutes les colonnes d'une table (pas de regroupement d'index, indexation individuelle de chaque colonne)

Pour les curieux c'est unu table de 150 colonnes 7 millions de lignes sur laquelle j'ai beaucoup de calcul du genre min max à effectuer.

Merci

14 déc. 2008, 23:23

Oui, tu exploses la taille de la table et allonge le temps de modification. Sur ta table tu dois indexer les colonnes qui ont le plus d'accès (enfin sur lesquelles un index pourraît être le plus efficace…). N'hésite pas à bencher…

14 déc. 2008, 23:31

Merci de ta réponse rapide pour un dimanche soir.

je suis dans un cas un peu particulier,

tu exploses la taille de la table

La taille n'est pas vraiment un problème, c'est une base (voir meme une table) dédié pour un seul serveur

et allonge le temps de modification

Et la table est en readonly, aucune requete d'insert ou d'update dessus.

Y a t'il d'autre contre-indication ?

15 déc. 2008, 00:05

Ce sont les deux seules que je vois, mais n'étant pas non plus un spécialiste, peut être y en a-t-il d'autres…

15 déc. 2008, 00:23

As tu réellement besoin d'indexer toutes les colonnes ? Tu pourrais peut-être te passer d'indexer celles sur lesquelles tu n'a pas souvent de recherches, auquel cas cela accélérerait le traitement des autres requêtes.
Comme l'a dit Sékiltoyai le verdict final est dans les bench

15 déc. 2008, 12:19

Clairement non, cela ne pose aucun problème.
Les index ne sont utilisés par l'optimiseur du SGBD que lorsque c'est nécessaire. Tu peux avoir une table avec 150 index sur 150 colonnes, si ta clause where ne porte que sur une seule colonne, il y a 149 index qui seront ignorés.

15 déc. 2008, 12:41

merci caroube, à chaque fois que tu me réponds c'est un plaisir

pas de troll, pas de réponse du genre (faut pas faire, parceque, ta base est mal concu ...)

je vais donc essayé d'indexer un max de colonne, mais j'ai déjà un problème mysql refuse de créer plus de 64 indexes ...
dommage ...

15 déc. 2008, 18:13

MySQL risque également d'avoir du mal à optimiser les requêtes si il y a trop d'INDEX.

http://www.phpfrance.com/tutoriaux/inde ... bles-mysql

Je savais bien que j'avais lu ça quelque part...

Sinon quand on regarde d'anciens topic

Une table MyISAM peut comporter 32 index

http://mysql.developpez.com/faq/?page=S ... ALEURS_MAX

La valeur par défaut est donc en augmentation, je dis par défaut car j'ai lu qu'on pouvait changer cette valeur en recompilant.

... y'a des fois où l'expertise d'Hubert nous manque

16 déc. 2008, 11:45

Pour MyISAM :

Le nombre maximal d'index par table est de 64 (32 avant MySQL 4.1.2). Cela peut être changé en recompilant. Le nombre de colonnes maximal par index est 16.

http://dev.mysql.com/doc/refman/5.0/fr/ ... ngine.html

Je n'ai rien trouvé pour le nombre d'index en InnoDB

17 déc. 2008, 17:18

bon en fin de compte l'indexation des colonnes ne fonctionne pas du tout ...
en effet toutes mes requetes sont de la forme :

select calcul_sur(champ_indexé) WHERE une_condition

et un explain de cela indique aucun utilisation d'index . Visiblement des qu'il y a une condition where, l'utilisation des indexes est désactivé dans le select ... cela parait logique mais c'est bien dommage...

Je change donc me fusil d'épaule et cherche une autre solution, rendez vous dans un autre post.

17 déc. 2008, 17:52

Ce n'est pas le problème du where puisque l'indexation sert à ça. C'est le fait que tu fasse un calcul sur chaque champ avant de le comparer qui oblige mysql à lister et calculer tous ces champs.

17 déc. 2008, 19:03

Euh non ...

L'indexation sert à récupérer les champs qui correspondent à la condition.
C'est après que le calcul est appliqué et uniquement sur les lignes sélectionnées par le WHERE

Code : Tout sélectionner

EXPLAIN SELECT round( c1 ) 
FROM test
WHERE c1 =1 
id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
1   SIMPLE       test   ref   c1             c1   4        const   1  Using index 

EXPLAIN SELECT c1
FROM test
WHERE c1 =1 
id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
1   SIMPLE       test   ref   c1             c1   4        const   1  Using index

Par contre, c'est dans le une_condition qu'il ne faut pas qu'il y ait de fonction (du moins en MySQL)

17 déc. 2008, 20:26

@caroube

Oui mais si j'ai bien compris son intention il est fort probable que la condition where inclue le champ du calcul, sinon les index seraient utilisés. Donc puisqu'ils ne le sont pas c'est l'explication la plus simple qui m'est venue à l'esprit

... à moins que son Where portent sur d'autres champs mais qui nécessitent également des calculs.

@pierreC
D'où la nécessité d'être le plus précis possible dans tes questions sinon on risque fort de perdre du temps (et toi aussi) en répondant à côté.

19 déc. 2008, 14:46

je peux vous coller une requête réel mais ca risque de vous surprendre :

SELECT sum(civilite='M') , sum(civilite='MLLE') , sum(civilite='MME'), sum(civilite='NR') ,
,sum(code_region='23'),sum(code_region='24') ,sum(code_region='25'), NORMANDIE', sum(code_region='26'), sum(code_region='31')
WHERE couplage_global IN ('CAN','CPL','CSA') OR ( anc_an > 25 AND ancien < 5 )

J'ai donc constaté que dans ce genre de requete, si je n'ai pas de WHERE et que les champs (civilite et code_region) dans le select son indexé, les calculs vont TRES vite, mais des qu'il y a un WHERE, les index ne sont plus utilisé.

et pour info c'est une toute petite requete. En prod le select comportera une centaine de sum() concernant une vingtaine de variable. Le where comprendra entre 1 et 15 conditions

indexation de toutes les colonnes

Qui est en ligne