Optimisation Index MySQL

Version imprimable

Bonjour,

J'utilise:
Windows XP & Cygwin
perl, v5.10.0
MySQL 5.1.44
Toad for MySQL Beta 4.6.0.522

J'ai 3 tables:
bdd_operator: qui ne comporte que 2 colonnes de moins de 10 entrées
bdd_broadcaster: idem que la première
bdd_billing: c'est la table principale avec 8 colonnes et 1 million d'entrées, également des indexes et deux clés étrangères.

J'ai actuellement en indexe:
smartcard_id (colonne pouvant aller à des millions de référence différentes)
account_id (colonne pouvant aller à des millions de référence différentes)
billing_date (colonne la date en jour/mois/année/heure/minute/seconde)
product_id (colonne pouvant aller à des centaines de millier référence différentes)
broadcaster_id (colonne qui ne comporte qu'une vingtaine de référence unique)

Je voudrais savoir ce que vous pensez de ces indexes, s'il est judicieu ou pas d'en mettre sur la date etc...
Que dois-je corriger pour optimiser le temps de mes requêtes?

Ci-dessous un exemple de mes 3 tables:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
# Dumping structure for table bdd_report.bdd_operator
CREATE TABLE IF NOT EXISTS `bdd_operator` (
  `operator_id` int(4) unsigned NOT NULL,
  `operator_name` varchar(64) NOT NULL,
  PRIMARY KEY (`operator_id`),
  UNIQUE KEY `operator_id` (`operator_id`),
  KEY `operator_id_2` (`operator_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Liste des opérateurs';
 
# Dumping structure for table bdd_report.bdd_broadcaster
CREATE TABLE IF NOT EXISTS `bdd_broadcaster` (
  `broadcaster_id` char(8) NOT NULL,
  `broadcaster_name` varchar(64) NOT NULL,
  PRIMARY KEY (`broadcaster_id`),
  UNIQUE KEY `broadcaster_id` (`broadcaster_id`),
  KEY `broadcaster_id_2` (`broadcaster_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Liste des magasins';
 
# Dumping structure for table bdd_report.bdd_billing
CREATE TABLE IF NOT EXISTS `bdd_billing` (
  `smartcard_id` int(10) unsigned NOT NULL,
  `account_id` varchar(16) NOT NULL,
  `product_id` varchar(16) NOT NULL,
  `product_name` varchar(128) NOT NULL,
  `price` int(5) unsigned NOT NULL,
  `billing_date` datetime NOT NULL,
  `operator_id` int(4) unsigned NOT NULL,
  `broadcaster_id` varchar(8) NOT NULL,
  PRIMARY KEY (`smartcard_id`,`account_id`,`billing_date`,`product_id`,`broadcaster_id`),
  KEY `fk_opid` (`operator_id`),
  KEY `fk_brid` (`broadcaster_id`),
  KEY `account_id` (`account_id`,`product_id`,`billing_date`,`operator_id`,`broadcaster_id`,`smartcard_id`),
  CONSTRAINT `fk_brid` FOREIGN KEY (`broadcaster_id`) REFERENCES `bdd_broadcaster` (`broadcaster_id`),
  CONSTRAINT `fk_opid` FOREIGN KEY (`operator_id`) REFERENCES `bdd_operator` (`operator_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Liste des commandes';
 
INSERT INTO `bdd_broadcaster` (`broadcaster_id`, `broadcaster_name`) VALUES ('RIS', 'PARIS'), ('STB', 'STRASBOURG');
INSERT INTO `bdd_operator` (`operator_id`, `operator_name`) VALUES (3, 'FRANCE'), (12, 'ESPAGNE'), (15, 'ALLEMAGNE'), (16, 'BELGIQUE'), (17, 'LUXEMBOURG'), (460, 'SUISSE');
INSERT INTO bdd_billing VALUES("102652","1516135484","RIS4505001240","Le journal de 13h","499","20100223100115","16","RIS");
INSERT INTO bdd_billing VALUES("100768","4932156717","STBPdree15110","Espion(s)","752","20100228015556","3","STB");

Merci par avance pour votre aide.

01/03/2010, 09h53
CinePhil

1) Table bdd_operator
L'identifiant est de type entier non signé etr auto-incrémenté, c'est bien, mais il y a mieux.

Citation:

bdd_operator: qui ne comporte que 2 colonnes de moins de 10 entrées

Compte-tenu du nombre de lignes de la table, et si celle-ci n'est pas appelée à grossir, un TINYINT serait suffisant.

Comme operator_id est la clé primaire, elle est automatiquement indexée donc inutile d'ajouter UNIQUE KEY et KEY sur cette même colonne.
Par contre, un index sur operator_name serait utile.

2) Table bdd_broadcaster
Pourquoi ne pas avoir eu le même bon réflexe concernant le type de l'identifiant ?
Un CHAR(8) est deux fois plus gourmand en mémoire qu'un INTEGER qui est codé sur 4 caractères. Même remarque sur le niombre de lignes. Passer le type de l'identifiant en TINYINT serait suffisant.
Si cet identifiant est une référence plus ou moins officielle ou construite selon certaines règles, garder la colonne et y placer un index UNIQUE car il s'agit de ce qu'on appelle une clé alternative.
Même remarque que précédemment sur les index : UNIQUE KEY et KEY sont inutiles sur la clé primaire ; c'est automatique.
Placer par contre un index sur brodcaster_name

3) Table bdd_billing
Bon réflexe sur le type de l'identifiant smartcard_id.

account_id et product_id semblent être des clés étrangères faisant référence à des identifiants provenant d'autres tables, bien qu'elles ne soient pas déclarées comme tel. Idem, ces identifiants externes devraient être de type entier non signé.

En voyant la composition de la clé primaire, j'ai l'impression smartcad_id est aussi une clé étrangère non déclarée comme tel.
Il s'agit d'une table associative à 4 branches non ?

S'il y a le product_id, pourquoi avoir ajouté le product_name ? Cela fait probablement redondance avec la table des produits. Product_id devrait être clé étrangère.

Par voie de conséquence du point 2), broadcast_id devrait ici être de type entier non signé.

Tous les prix sont-ils entiers ?
C'est plutôt rare, sauf sur des produits chers pour lesquels les centimes ne sont pas significatifs.

Il manque un index sur product_id.

Si des recherches se font régulièrement sur la billing_date, il faudrait indexer cette colonne.

Les clés étrangères sont indexées, c'est bien.
01/03/2010, 16h10
Sethenssen

Bonjour CinePhil,

Merci d'avoir pris le temps de répondre, tout ça me sera très util.
Il en effet bon de savoir qu'un varchar est 2x plus gourmand en ressource qu'un INTEGER

Ma BDD ne comporte que ces 3 tables.
Sur la table principale bdd_billing j'ai :
- broadcaster_id qui est le même que sur la tableau bdd_broadcaster.
- operator_id qui est le même que sur la tableau bdd_operator.
J'ai mi un FOREIGN_KEY pour eviter d'ajouter dans bdd_billing des operateur ou broadcaster qui ne sont pas déjà renséigné dans les tables adjacentes.

Product_id a Product_name sont très utile, l'un détient l'id et l'autre le titre.

Tout les prix sont renseignés en centimes.

Enormément de recherche se font avec les dates, c'est pourquoi j'ai déjà mis un indexe mais je ne sais pas si c'était utile ou non. Car je ne sais pas comment la date est géré dans une BDD.
Je pensai que l'indexe pouvait être caduc car justement il s'agissait d'un champ date...

Account_id & Smartcard_id n'existe que dans la table principale, donc ce ne sont pas des clés étrangères.

Merci pour ces infos, je vais déjà changer les types et longeur de mes colonnes pour gagner en ressource et appliquer le reste.

Quelle est la définition de TINYINT ?

Merci.
01/03/2010, 16h29
CinePhil

Citation:

Envoyé par Sethenssen

Il en effet bon de savoir qu'un varchar est 2x plus gourmand en ressource qu'un INTEGER

Ce n'est pas exactement ça !
Pour stocker un VARCHAR, il faut autant d'octets que de caractères contenus + 1 octet pour la longueur de la chaîne.
Ainsi, un VARCHAR(12) occupera au maximum 13 octets.

Citation:

Ma BDD ne comporte que ces 3 tables.
Sur la table principale bdd_billing j'ai :
- broadcaster_id qui est le même que sur la tableau bdd_broadcaster.
- operator_id qui est le même que sur la tableau bdd_operator.
J'ai mi un FOREIGN_KEY pour eviter d'ajouter dans bdd_billing des operateur ou broadcaster qui ne sont pas déjà renséigné dans les tables adjacentes.

Que sont alors les autres id de cette table ?
Un identifiant ne sert en principe qu'au SGBD pour gérer les relations entre les tables, au programme pour mémoriser les identifiants des lignes mais n'est jamais présenté à l'utilisateur car il n'a pas de signification.

Citation:

Product_id a Product_name sont très utile, l'un détient l'id et l'autre le titre.

Ca je m'en doutais mais normalement tu devrais avoir une table des produits et une clé étrangère référençant les produits dans la table bdd_billing.

Citation:

Tout les prix sont renseignés en centimes.

OK.

Citation:

Enormément de recherche se font avec les dates, c'est pourquoi j'ai déjà mis un indexe mais je ne sais pas si c'était utile ou non.

Bien sûr que c'est utile ! Justement à cause de cette fréquence de recherche.

Citation:

Car je ne sais pas comment la date est géré dans une BDD.
Je pensai que l'indexe pouvait être caduc car justement il s'agissait d'un champ date...

Je ne vois pas pourquoi on ne pourrait pas indexer une colonne de type DATE ! 8O
Le type DATETIME se présente comme une chaîne de caractères formatée 'AAAA-MM-JJ hh:mm:ss' mais je ne sais pas si c'est stocké physiquement de cette manière.
Par contre, il existe pas mal de fonctions pour manipuler les dates qui sont bien pratiques.

Citation:

Account_id & Smartcard_id n'existe que dans la table principale, donc ce ne sont pas des clés étrangères.

Que contiennent ces colonnes ?

Citation:

Quelle est la définition de TINYINT ?

TINYINT = petit entier.
Tu trouveras toutes les précisions dans la doc MySQL.
Tu verras notamment en bas de la page que le TINYINT est codé sur un seul octet.
01/03/2010, 18h46
Sethenssen

Ok,

En faite je ne peux pas isoler le product & le product name car je n'ai pas la main dessus.
Au moment de la transaction les 2 champs apparaissent, je suis obligé de les traiter.

Pour ma table principale elle est composé de:
`smartcard_id` => que des nombres ne dépassant pas 10 ex:'12323454'
`account_id` => idem que smartcard
`product_id` => lettre + nombre ne dépassant pas 13 caractères
`product_name` => lettre + nombre ne dépassant pas 40 caractères
`price` => nombre en centime
`billing_date` format classique yyyy/mm/dd hh:mm:ss
`operator_id` nombre ne dépassant pas 3 caractères ex: '345'
`broadcaster_id lettre + nombre ne dépassant pas 3 caractères