Discussion :

[Sethenssen]

Bonjour,

Est-ce qu'une clé primaire composite est déjà indexée ?

Exemple:

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE `terminate` (
  `date` datetime NOT NULL,
  `type` varchar(10) NOT NULL,
  `session` int(15) NOT NULL,
  `server` char(5) NOT NULL,
  `status` char(3) NOT NULL,
  PRIMARY KEY (`date`,`session`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='LOG';

Ici j'ai une table où les champs date et session représentent une clé primaire composite.
Est-elle déjà indexée ou dois-je créer un index également sur les champs date et session?

Merci d'avance,
Seth.

[ced]

Bonjour,

La réponse est oui : elle est automatiquement indexée (si ce n'est pas le cas, alors MySQL est encore pire que ce que j'imaginais...).
Pour vous en convaincre (et me rassurer ), interrogez le catalogue en vous aidant du tutoriel suivant : http://cedric-duprez.developpez.com/...atalogue/#LVII

[SQLpro]

Toute clef primaire et toute contrainte d'unicité sur tout SGBDR est toujours indexé, car c'est le seul moyen d'assurer l'unicité des données d'une clef.

A +

[Sethenssen]

Merci pour vos lumières.

[fsmrel]

toute contrainte d'unicité sur tout SGBDR est toujours indexée

Pour la perite histoire, tu sacrifies à un montage datant du milieu des années soixante-dix, quand le prototype System R d’IBM ne permettait pas de définir au niveau relationnel les clés primaires ou plus généralement candidates (a fortiori les clés étrangères !) :
Le seul moyen de garantir l’unicité des clés d’une table était alors de descendre au niveau physique pour brancher des index de type UNIQUE sur cette table (à l’époque on utilisait l’instruction CREATE IMAGE, devenue peu après CREATE INDEX).

Les choses en sont resté là jusqu’en 1988, quand IBM a sorti la V2 de DB2 et enfin proposé la clause « PRIMARY KEY » (ainsi que la « FOREIGN KEY » tant qu’à faire...) : on est remonté au niveau relationnel, après 19 ans de non-conformité au Modèle Relationnel de Données...

Mais IBM n’allait quand même pas s’em... à permettre qu’on puisse se dispenser de ce genre d’index pour faire respecter l’unicité des clés : le bricolage de 1976 a été élevé 12 ans plus tard au rang de diktat, de dogme, à chaque clé candidate son index « unique ». Je pense que la majorité des SGBD SQL ont repris ce bricolage sans sourciller.

De son côté, la norme SQL n’impose pas de définir de clé primaire et permet de coder par exemple :

Code SQL :

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

1
2
3
CHECK
    (UNIQUE (SELECT (Date, Session)
             FROM TERMINATE)) ;

Et reste muette sur l’indexation (comme dirait Chris Date, c’est orthogonal...) Certes, il est plus simple de coder cette contrainte sous la forme conviviale :

Code SQL :

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

PRIMARY KEY (Date, Session)

Mais il n’y a pas de raison supplémentaire pour indexer d’office.

Brancher un index (« unique » ou pas) devrait être du ressort du seul DBA, jugeant efficace d’indexer, pour des raisons évidentes de performance dès que les tablespaces commencent à occuper plus de quelques pages. Un index branché sur une table des titres de civilité ou une table des sexes n’est qu’une surcharge pondérale dont on se passerait bien, même chose dans le cas d’une table des divisions des codes NAF, laquelle représente moins de cent lignes, soit au pire deux pages physiques de 4K, une misère. En revanche, il est probable qu’il faudra indexer la table des sous-classes de ces codes NAF (plus de 700 lignes), car l’encombrement des données représente environ 8 pages de 4K. Mais on rentre là dans le quotidien du DBA qui mesure et décide en conséquence.

Sic vivit prima clavis...

[SQLpro]

Si je suis d'accord avec toi sur le plan théorique (et effectivement SQL est muet sur l'index...) je m'oppose à ce que tu dis pour des raisons transactionnelles ! En effet, comment garantir l'unicité le temps d'insérer en concurrence sans index.... Là c'est très caca....


A +

[fsmrel]

Index ou pas index, du moment qu'on a déclaré une contrainte d'unicité (ou autre), codée « UNIQUE » ou « PRIMARY KEY », le SGBD doit assurer. Maintenant si la table contient non pas une ou deux mais un million de pages, il est évident que le DBA aura branché fissa l'index qui va bien. Ce que je vise concerne les tables du genre "tables de codes" (titres de civilité, etc.) qui foisonnent dans les bases de données (et qui soit dit en passant ne sont mises à jour que très exceptionnellement).

[SQLpro]

1) UNIQUE et son raffinement DISTINCT sont des opérateurs ensemblistes rarement implémentés
2) même pour une petite table, il y a toujours un intérêt à placer un index unique par le biais d'une PK : elle peut devenir grande, la lecture séquentielle d'un index est plus rapide que la table !

A +

[fsmrel]

Je n’ai pas le sentiment que la stabilité d’une table « de codes », genre table des titres de civilité puisse être remise en cause. Mais qu’une petite table n’entrant pas dans la catégorie des tables de codes croisse soudain suite à des événements imprévus ou à une lubie de décideur ou que sais-je : le DBA qui suit ses statistiques ne manquera évidemment pas de brancher un index.

[Sethenssen]

Bonsoir,

J'ai une autre question,
Donc merci pour la réponse sur le fait que la clé primaire composite est déjà indexée.

Mais si je fais une requête avec uniquement "session" en clause where
Est-ce que cette table session est indexée?
Ou est-ce juste la combinaison de date+session qui est indexée?

Code :

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

1
2
3
4
5
6
7
8
CREATE TABLE `terminate` (
  `date` datetime NOT NULL,
  `type` varchar(10) NOT NULL,
  `session` int(15) NOT NULL,
  `server` char(5) NOT NULL,
  `status` char(3) NOT NULL,
  PRIMARY KEY (`date`,`session`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='LOG';

[fsmrel]

A priori, il n’y a pas de raison que la colonne session soit indexée, mais pour en avoir le cœur net il faudrait coder une requête dans le style :

Code SQL :

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

1
2
3
SELECT *
FROM   terminate
WHERE  session = 1 ;

Et en demander l’EXPLAIN détaillé, façon tavarlindar.

Du reste, par sécurité, il serait bon de demander aussi l’explain de la requête :

Code SQL :

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

1
2
3
SELECT *
FROM   terminate
WHERE  date = '19/01/2014' AND session = 1 ;

[Sethenssen]

Merci fsmrel pour la réponse, je vais étudier cela.

Autre question,

Sur une PRIMARY KEY, est-ce que l'ordre que l'on donne est important?
Par exemple existe t-il y a un effet d'entonnoir?
Du style on part toujours de gauche à droite avec les tables contenant le plus de ligne etc...

Code :

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

PRIMARY KEY (`date`,`session`)

[SQLpro]

Merci fsmrel pour la réponse, je vais étudier cela.

Autre question,

Sur une PRIMARY KEY, est-ce que l'ordre que l'on donne est important?
Par exemple existe t-il y a un effet d'entonnoir?
Du style on part toujours de gauche à droite avec les tables contenant le plus de ligne etc...

Code :

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

PRIMARY KEY (`date`,`session`)

Oui, tout dépend de la distribution des données et du prédicat de recherche. Lisez mon livre à ce sujet, chapitre INDEXATION, car la réponse est loin d'être évidente....

A +

[fsmrel]

Bonjour Sethenssen,

Sur une PRIMARY KEY, est-ce que l'ordre que l'on donne est important?

L’ordre est important, pour diverses raisons, disons de niveau fonctionnel.

Si (date, session) est clé primaire de la table TERMINATE sur laquelle le SGBD a automatiquement branché un index de clé (date, session), et si les requêtes sont plutôt du style « WHERE session = ... », vous devrez brancher un index supplémentaire de clé (session). Or, si un index facilite les lectures, il pénalise les mises à jour dès lors qu’il est touché.

Notez que l’ordre des colonnes dans une clé primaire conditionne l’ordre des clés étrangères dans les contraintes référentielles qui sont les conséquences de cette clé primaire. Supposez que vous ayez une table T, ayant une clé étrangère FK_TERMINATE référençant la clé primaire (date, session) de la table TERMINATE, alors l’ordre des colonnes de FK_TERMINATE doit encore être (date, session), avec un index de clé (date, session). Si les requêtes sont encore du genre « SELECT ... FROM T WHERE session = ... », une fois de plus vous n’y couperez pas d’un index supplémentaire de clé (session), là encore avec un impact non nul lors des mises à jour.

Dans la mesure où les accès porteraient le plus souvent sur la colonne session, l’ordre des colonnes dans la clé primaire de la table TERMINATE aurait plutôt intérêt à être permuté et devenir : (session, date).