Clé primaire & index ...

Statut
N'est pas ouverte pour d'autres réponses.

SkYlEsS

Elite
Dans les chapitres précédents nous avons introduit une nouvelle notion sans la décrire précisement. En effet, à chaque élément de la table catégories nous avons associé un champ id, non null,qui identifie de façon unique une catégorie et qui nous sert de référence pour établir des liens avec d'autres tables (Cf. table films). C'est ce que l'on appelle une clé primaire. On pourra à la création de la table définir un champ comme étant une clé primaire par la requête

CREATE TABLE categories (id int4 NOT NULL AUTO_INCREMENT, categorie varchar(64), PRIMARY KEY(id));

Pourquoi préciser qu'une clé est primaire?

Comme il s'agit d'un champ qui sert de référence, la base de données est susceptible d'être souvent solicitée pour retrouver l'enregistrement de la table correspondant à l'identifiant id donné. Cette recherche doit donc être la plus rapide possible. La base de données est alors informée qu'elle doit chercher à optimiser cette recherche en introduisant ce que l'on appelle un index.
Index

Comme indiqué ci dessus, un index sert à optimiser les recherches. Car si le moteur de la base de données passe en revue chaque enregistrement pour retrouver celui correspondant à certains critères cela prendra enormément de temps si par malheur la requête porte sur des enregistrements se trouvant en fin de table. Pour éviter cela, il est possible en quelque sorte d'établir des parcelles dans cette table. Pour par exemple être capable de dire que les champs ayant un id entre 0 et 1000 se trouvent dans le 1er quart de la base, ceux entre 1001 et 1500 dans le 2nd quart, ceux entre 1501 et 3000 dans le 3ème quart et enfin ceux entre 3001 et 3500 dans le dernier quart. (REM: L'enregistrement ayant l'identifiant 300 n'est pas nécessairement le 300ème enregistrement puisque certains enregistrements ont pu être supprimés). Dans ce cas, la recherche systèmatique ne portera alors que sur un quart de la table (réduisant ainsi par quatre les temps de recherche des éléments en fin de table). Toutefois, la création et l'utilisation de cet index à un coût (cela prends du temps). Il n'est donc pas concevable de l'appliquer à tous les champs, c'est pourquoi le concepteur de la base doit choisir judicieusement et préciser quels doivent être les champs devant bénéficier de cet indexage. Les clés primaires sont les premiers candidats tout désignés mais d'autres champs souvent utilisés dans des clauses WHERE peuvent eux aussi légitement en être bénéficiaire. Seul un champ défini comme "NOT NULL" peut être indéxé (c'est du moins vrai pour MySQL)

CREATE TABLE matable (id int4, nom varchar(64) NOT NULL, INDEX (nom));

Remarques:

1. Contrairement à PRIMARY KEY, INDEX n'implique pas l'unicité du champs. Pour le rendre unique il faut utiliser UNIQUE INDEX.
2. L'algorithme d'indexage présenté ici, n'est qu'une vulgarisation du principe d'indexage, il ne reflète que très peu la réalité mais permet de prendre conscience de la problématique.
Malgré cela, je ne comprends pas bien quand utiliser PRIMARY KEY, INDEX et UNIQUE INDEX ... Quelqu'un pourrait m'expliquer les différences (avec exemples si possible) et les utilités de chacun ?

Merci :)

Edit : et pour quel type de champ opter si je veux enregistrer une date complète ? (Jour, mois, année, heure, minute voire seconde)
 

Tifox

ou pas
PRIMARY KEY, cela indique que le champs en question est une référence unique pour l'entrée dans la table, donc il est unique sur la table : 2 entrée n'auront jamais un même champs PRIMARY KEY identique, et une tentative d'insérer une entrée avec une valeur de PRIMARY KEY qui existe déjà dans la table échouera. De plus, sur un champs PRIMARY KEY, la base de donnée crée un index qui permet d'accélérer la récupération d'une entrée en donnant sa PRIMARY KEY dans la clause WHERE du SELECT. il n'y a toujours qu'une PRIMARY KEY par table*.

Si on veut accélerer la récupération d'entrées en fonction de certains champs, on doit construire aussi un index pour ces champs. c'est la qu'intervient la déclaration INDEX et UNIQUE INDEX. Il peut y avoir plusieurs INDEX par table. Si il peut y avoir plusieurs entrées avec une même valeur, tu utilisera INDEX. Par contre, si tu es sur que toutes les entrées auront une valeur différentes, tu utiliseras INDEX UNIQUE. Le point négatif dun INDEX est que ça prend de la place dans la mémoire de la base de donnée.

Ca c'est la théorie, j'espère que c'est plus claire.


En pratique maintenant. Quand tu crées une table, tu dois toujours lui donner une PRIMARY KEY (donc un champs qui identifiera de manière unique une entrée).
Ensuite, si tu fais souvent des SELECT sur les mêmes champs, déclare ces champs comme INDEX ou INDEX UNIQUE, ca accélèrera le SELECT.


Je te fais un exemple de suite.


*Il n'y a toujours qu'une PRIMARY KEY par table, mais celle-ci peut être composée de plusieurs champs. Dans ce cas, c'est la combinaison des champs qui la rend unique.
De la même manière, je pense que tu peux faire un index sur une combinaison de champs.
 

Tifox

ou pas
Exemple :

Imaginons que tu as un table pour une liste de livre, avec comme champs :
id_livre : un numéro spécifique a ton application pour le livre (unique)
isbn : numéro ISBN (unique)
titre :
auteur :
nbr_pages : nombre de page

Ton application propose une recherche par ISBN et par titre, il y aura donc beaucoup de SELECT sur ces champs, ça peut donc être bien de les accélérer avec un INDEX.
Une fois arrivé dans ton application a la liste des résultats, tu propose pour chaque livre un lien vers une autre page donnant toutes les infos sur le livre. il faut donc passer a cette page un paramètre identifiant de manière unique le livre afin que la page retrouve toutes les infos dans la base de données. on passera donc la PRIMARY KEY.

Comme PRIMARY KEY, on va utiliser l'id_livre. On aurait pu utiliser l'isbn, mais vu que l'isbn fait partie des caractéristique du livre, j'ai préférer utiliser un numéro propre a l'application qui sera unique (il le sera grace a un AUTO INCREMENT)

On va mettre un INDEX UNIQUE sur le champs ISBN, et un INDEX sur le titre (le titre n'est pas nécéssairement unique).


Pour créer ta table, tu aura donc :

CREATE TABLE liste_livre (
id_livre int(10) unsigned NOT NULL auto_increment,
isbn varchar(32) NOT NULL default '',
titre varchar(127) NOT NULL default '',
auteur varchar(64) NOT NULL default '',
nbr_pages int(10) unsigned NOT NULL default 0,
PRIMARY KEY (id_livre),
UNIQUE INDEX (isbn),
INDEX (auteur)
)
 
1er
OP
SkYlEsS

SkYlEsS

Elite
D'ors et déjà merci beaucoup de tes explications en tout cas ! :-'
 

Tifox

ou pas
De rien, exemple ajouté.
 

II phl II

Touriste
Merci je cherchais aussi depuis lgtps une explication simple et précise :-D
 
1er
OP
SkYlEsS

SkYlEsS

Elite
Youpie, j'ai tout compris ! :cool:

Mais que veut dire 'unsigned' pour les attributs d'un champ de type int ?

Sinon, je cherche également de la documentation sur chaque type de champ et quand utiliser tel ou tel type ... Par exemple, dans ce cas, pourquoi utiliser varchar et pas text ? :roll:
 
S

Shrekju

ex membre
Dans le cas d'un nombre unsigned
00001001
Aucun bit ne sert à définir le signe. Les 8 bits en blanc pour la valeur.
Le domaine sera [ 0 ; 255 ]

Si tu sais compter en binaire tu comprendras directement. C'est l'affaire de 5 min sur google.

Pour les caracteres, le varchar est fait à la base pour des chaine plutot courte. Dans les premieres versions de mysql de 0 à 255 caracteres et dans les dernieres versions, il accepte jusque 65532 caracteres comme le type text simple (depent de la config aussi, je ne rentre pas dans les detail). Le text stocke les caracteres sous forme de blob, donc il accepte n'importe quoi comme valeur. Varchar ne permet pas autant de fantaisie et c'est parfois pas plus mal, par exemple au niveau des espaces en fin de chaine qui sont effacé automatiquement entre autres. C'est surement configurable aussi.. Le plus simple est de regarder dans la doc de la version qu'on utilise.
 

oNi-

:baille: Lecteur
Ca devrait répondre à tes questions : types mysql

Sinon pour l'exemple, un varchar peut contenir maximum 255 caractères (et on précise la taille à réserver) et un 'text' 65532.
Quand tu sais que tu dois stocker une valeur qui ne dépassera jamais x caractères, pas la peine de réserver plus. (en gros hein ^^).

EDIT : ha ben grillé ^^
 
1er
OP
SkYlEsS

SkYlEsS

Elite
Quelle différence entre les champs sensibles à la casse et les champs insensibles à la casse ? :-'
 

Nikko

...
Dans un champ sensible à la casse,
"GamerZ" est différent de "gamerz"

Dans un champ insensible à la casse, "GamerZ"= "gamerz"

La casse, ce sont les majuscules.
 
1er
OP
SkYlEsS

SkYlEsS

Elite
... au fond, quel est l'intérêt de combiner un index sur plusieurs champs au lieu d'indexer chacun de ses champs ? :)
 
S

Shrekju

ex membre
SkYlEsS a dit:
... au fond, quel est l'intérêt de combiner un index sur plusieurs champs au lieu d'indexer chacun de ses champs ? :)
Si tu fais un index sur plusieurs champs, le 2e sera seulement utilisé si le premier l'est, etc. Donc c'est plus léger de faire un index sur plusieurs champs.
 

Tifox

ou pas
SkYlEsS a dit:
... au fond, quel est l'intérêt de combiner un index sur plusieurs champs au lieu d'indexer chacun de ses champs ? :)
Prenons un exemple : un table contenant comme champs un nom et un prénom.
Si tu fais souvent une recherche juste sur le nom, tu indexeras le nom.
Si tu recherche souvent le prénom, tu indexera le prénom.
Mais si tu recherche en utilisant le nom et le prénom, indexe les 2 ensembles, ce sera plus efficace que d'indexer les deux séparément.
 
1er
OP
SkYlEsS

SkYlEsS

Elite
Pour enregistrer une date & heure dans la BDD, j'ai le choix entre :

Soit un champ de type 'int' (unsigned) et d'enregistrer au format date("U") ou time() (une différence entre eux ?).

Soit un champ de type 'timestamp' (14) et d'enregistrer au bon format. (dans ce cas, c'est quoi la fonction php ou msql pour générer une date à ce format déjà ?)

Qu'est-ce qui est mieux, le plus rapide, etc. ? :-D
 

Froggy

fake geek
ça se sent que les exams arrivent hein ... :mrgreen: :D
 

Bingo

Beer Addict
SkYlEsS a dit:
Pour enregistrer une date & heure dans la BDD, j'ai le choix entre :
Pourquoi tu aurais un choix à faire ?
Si tu enregistres une date et heure, tu enregistres ça au format datetime, c'est tout.
Pourquoi se compliquer la vie ?
 
1er
OP
SkYlEsS

SkYlEsS

Elite
Froggy a dit:
ça se sent que les exams arrivent hein ... :mrgreen: :D
... Je fais des études de langues et littératures françaises et romanes :=)
 
1er
OP
SkYlEsS

SkYlEsS

Elite
Bingo a dit:
Pourquoi tu aurais un choix à faire ?
Si tu enregistres une date et heure, tu enregistres ça au format datetime, c'est tout.
Pourquoi se compliquer la vie ?
Pour prendre le moins de place dans la BDD et pour avoir une meilleure rapidité ;)
 

Bingo

Beer Addict
SkYlEsS a dit:
Pour prendre le moins de place dans la BDD et pour avoir une meilleure rapidité ;)
Commence par choisir le type de données qui correpond vraiment à ce que tu stockes dans le champ, tu t'occuperas de l'optimisation après !!!
C'est pas les 2 millisecondes que tu gagnerais (si tu les gagnes, ce qui est loin d'être sûr) avec un autre type de données que changeront quoi que ce soit.
 
Statut
N'est pas ouverte pour d'autres réponses.
Haut