Requête sur table avec beaucoup de champs

**Lideln75** · 25/11/2008, 17h50

Bonjour,

J'ai une table avec beaucoup de champs (disons 20), et j'aimerais permettre aux utilisateurs de faire des requêtes sur cette table (enfin, via un formulaire bien sûr).
J'ai lu dans la doc mysql que si je crée un seul index (déjà ça marchera pas puisque c'est maximum 15 champs, et j'en ai 20) avec tous les champs, il faut absolument que le premier des champs soit renseigné dans la requête, et ce par une clause AND (genre "WHERE nom='bla' OR prenom='bli' n'utilisera pas l'index sur plusieurs colonnes nom+prenom).
Mais en même temps on ne peut aussi mettre que 15 index max dans une table j'ai cru lire (et puis créer 20 index pour une table ne va pas risquer de péter la taille et la mise à jour à chaque mise à jour ?).

Donc voilà, si je ne mets pas d'index (ce que j'ai fait pour l'instant), j'ai peur que les requêtes soient super longues...

Merci pour les conseils des connaisseurs

Bonne soirée !

**Maljuna Kris** · 25/11/2008, 17h59

Saluton,
Tu devrais nous afficher la structure de la table pour qu'on s'assure qu'il n'y a pas, déjà, un gros souci de modélisation.

**Lideln75** · 25/11/2008, 18h51

Bonjour,

Si vous voulez, la voici :

Code :

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

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
 
CREATE TABLE `profiles` (
  `pro_mem_id` int(10) unsigned NOT NULL,
  `pro_height` tinyint(3) unsigned NOT NULL default '0',
  `pro_weight` tinyint(3) unsigned NOT NULL default '0',
  `pro_bulk` tinyint(3) unsigned NOT NULL default '0',
  `pro_appearance` tinyint(3) unsigned NOT NULL default '0',
  `pro_hair_length` tinyint(3) unsigned NOT NULL default '0',
  `pro_hair_colour` tinyint(3) unsigned NOT NULL default '0',
  `pro_eyes_colour` tinyint(3) unsigned NOT NULL default '0',
  `pro_ethnic` tinyint(3) unsigned NOT NULL default '0',
  `pro_religion` tinyint(3) unsigned NOT NULL default '0',
  `pro_religion_practice` tinyint(3) unsigned NOT NULL default '0',
  `pro_marital_status` tinyint(3) unsigned NOT NULL default '0',
  `pro_kids_has` tinyint(3) unsigned NOT NULL default '0',
  `pro_kids_wants` tinyint(3) unsigned NOT NULL default '0',
  `pro_lives` tinyint(3) unsigned NOT NULL default '0',
  `pro_studies` tinyint(3) unsigned NOT NULL default '0',
  `pro_profession` tinyint(3) unsigned NOT NULL default '0',
  `pro_income` tinyint(3) unsigned NOT NULL default '0',
  `pro_cigarette` tinyint(3) unsigned NOT NULL default '0',
  `pro_alcohol` tinyint(3) unsigned NOT NULL default '0',
  `pro_food` tinyint(3) unsigned NOT NULL default '0',
  `pro_style` tinyint(3) unsigned NOT NULL default '0',
  `pro_purity` smallint(5) unsigned NOT NULL default '0',
  KEY `pro_mem_id` (`pro_mem_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

C'est pour un site de rencontres (sans prétention, bien entendu).

Donc j'ai 3 options, donc :
1) ne laisser qu'un seul index sur l'id du membre (pro_mem_id)
2) ajouter deux index sur 11 colonnes chacun pour tout contenir (mais je suis même pas sûr que ce serait intéressant)
3) ajouter 15 index et laisser les 7 autres colonnes sans index

Il y a un souci dans ma table ? Je ne vois pas comment faire autrement.

Merci pour ta réponse

**Maljuna Kris** · 25/11/2008, 19h18

Extrait de la doc pour les tables 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://www.developpez.net/forums/new...te=1&p=3814136Donc je ne sais pas d'où tu tires qu'on ne peut avoir que 20 index par table.

Je n'ai trop rien à redire sur la structure de la table, la majorité des items sont probablement des clés étrangères dans d'autres tables de nomenclature.
Je m'interroge seulement sur l'utilité d'un code à trois digits TINIYINT(3) pour la couleur des yeux, par exemple.
Pourquoi mettre '0' plutôt que 0 comme valeur par défaut (mais ça c'est peut-être PMA qui déraille)

**Lideln75** · 25/11/2008, 22h33

Pour les tables de référence, il n'y en a pas, je préfère mettre ça dans un fichier de defines PHP, ça me fait des jointures et des tables en moins. Et je ne vois pas de contre-indication majeure

Pour le nombre maximum d'index, j'avais pourtant lu quelque part 15 ou 16. Bon au temps pour moi alors !

Mais donc j'ai 3 options :
- soit je me contente du seul index sur mem_id
- soit je crée deux index contenant chacun 10 colonnes (mais les requêtes risquent de ne pas utiliser ces index)
- soit je crée 22 nouveaux index (mais ça risque de ralentir les perf pour chaque maj de la table, et de faire gonfler le poids de celle-ci)

Non ?

Ah, et ... Kenavo

**Lideln75** · 26/11/2008, 12h14

PS : ah oui le "0" c'est PMA qui déraille comme vous dites

Et je mets tinyint tout simplement parce que je n'aurai pas 10.000 valeurs, donc je trouve ça mieux de mettre le type le moins gourmand possible. Le gain n'est pas énorme, mais je trouve ça plus rigoureux.

Donc je suis preneur pour tout avis concernant la méthode qu'il faudrait que j'utilise concernant ces index... Ne pas en mettre, mais la recherche risque d'être lente puisqu'elle n'utilisera pas d'index ? Mettre deux index sur plusieurs colonnes, mais il n'est pas dit que ces index seront utilisés si on se réfère à la doc MySQL ? En mettre un partout, mais la base risque de gonfler, et ralentir à chaque update sur cette table ?

Merci de vos conseils

**Maljuna Kris** · 26/11/2008, 14h25

Ma remarque sur les TINYINT(3) UNSIGNED a trait au fait que cela permet de coder de 0 à 255 et que je ne vois pas l'intérêt d'avoir autant de combinaisons pour la couleur des yeux, par exemple.
Mais c'est une remarque sans grand intérêt parce que je ne suis pas sûr, au final, qu'un TINYINT(2 ou 1) UNSIGNED soit plus économique. Car moins d'un octet pour stocker une valeur ça ne doit pas exister.
Pour les index, essaye sans, regarde les performances (en SELECT et en UPDATE, INSERT, DELETE), puis mets tous les index et refait les même essais.
En fonction de la comparaison des résultats tu auras des billes pour décider de les garder ou non.

**Lideln75** · 27/11/2008, 20h06

Oui pour le tinyint(3) en effet inutile d'aller en dessous d'1 octet.
Pour les index, il n'y a donc pas de règle générale ?
Bon, et bien dans ce cas oui il ne me reste plus qu'à tester avec un nombre correct d'enregistrements en base pour voir les perfs.
Merci tout de même pour les infos

**Maljuna Kris** · 28/11/2008, 06h01

Envoyé par Lideln75

Pour les index, il n'y a donc pas de règle générale ?
Bon, et bien dans ce cas oui il ne me reste plus qu'à tester avec un nombre correct d'enregistrements en base pour voir les perfs.

C'est toujours un compromis entre les performances en consultations et en mise à jour.

**Lideln75** · 29/11/2008, 12h47

Je pense qu'il y aura beaucoup beaucoup plus de consultations que de mises à jour.
Mais je ferai des tests et je les posterai à titre d'information, si ça peut aider certains à l'avenir.
A bientôt !

**Sivrît** · 30/11/2008, 14h14

Envoyé par Lideln75

C'est pour un site de rencontres (sans prétention, bien entendu).

Donc j'ai 3 options, donc :
1) ne laisser qu'un seul index sur l'id du membre (pro_mem_id)
2) ajouter deux index sur 11 colonnes chacun pour tout contenir (mais je suis même pas sûr que ce serait intéressant)
3) ajouter 15 index et laisser les 7 autres colonnes sans index

Il y a un souci dans ma table ? Je ne vois pas comment faire autrement.

En ce qui me concerne le schéma semble tout à fait viable, c'est juste qu'il s'agit d'une problématique délicate. Avec un volume de données relativement faible (et surtout tenant en mémoire) et assez peu de consultations la solution 1 pourrait le faire. Mais pour un site internet pour lequel ce genre de recherches va être la raison d'être... je doute que ça tienne la distance.

Envoyé par Lideln75

Je pense qu'il y aura beaucoup beaucoup plus de consultations que de mises à jour.

Ca et un volume raisonnable... Indexer chaque colonne pourrait peut-être se faire (en écartant, éventuellement, celles qui sont trop peu sélectives, c'est à dire oui/non comme `pro_cigarette`, et peut-être `pro_eyes_colour`). Mais ça reste perfectible tant qu'une recherche donnée ne peut utiliser qu'un seul indexe, ce qui est le cas jusqu'à la version 5.0. A partir de la 5.0 mysql dispose d'une fonctionnalité d'"index merge". C'est moins efficace qu'un indexe composé qui colle parfaitement mais ça pourrait aider. Je n'ai aucune expérience avec donc je ne sais pas s'ils peuvent suffire (à tester avec des données et des EXPLAIN). C'est aussi une opération qui peut consommer pas mal de CPU et de mémoire. Si le trafic est important c'est à surveiller.

Tout ça me fait penser qu'il faudrait considérer d'autres approches, comme les indexes fulltext ou (et surtout) sphinx. Je n'ai jamais utilisé sphinx et je soupçonne qu'il ne puisse aider pour rechercher par exemple une étendue de tailles, mais pour le reste ça semble prometteur. Je suis tombé là dessus : http://blog.kovyrin.net/2008/05/19/u...ltext-queries/. S'il ne gère pas les données numériques (taille surtout) certaines valeurs pourraient être segmentées : cheveux courts, mi-long, longs.

En espérant que ça aide.

Envoyé par Lideln75

Mais je ferai des tests et je les posterai à titre d'information, si ça peut aider certains à l'avenir.

Comme c'est vraiment un sujet délicat ; ça serait super de savoir ce que ça donne. Bonne chance

**Lideln75** · 30/11/2008, 17h21

Merci pour la réponse Sivrit !

Mes tables sont en MyISAM, car je compte gérer les problèmes de cohérence (clefs étrangères) moi-même, un peu de rigueur suffit. Ca me permettra de profiter des performances bien meilleures (jusqu'à 50 fois en INSERT à ce que j'ai pu lire sur un blog) de ce moteur par rapport à InnoDB.

Je ne maîtrise pas bien les bases de données (d'où mon post) donc je ne connais pas (ou peu) les différences entre :
- pas d'index
- plein d'index
- un index multiple
D'après ce que j'ai pu lire, un index multiple n'est utilisé que si la première colonne est référencée dans la requête par un AND.

Je vais faire les tests de suite. Je comptais attendre un peu mais finalement j'ai hâte de connaître les perfs.

Je vous tiens au courant dans la soirée, s'il y en a que ça intéresse !

**Lideln75** · 30/11/2008, 21h06

Benchmark

Utilisation de 3 tables :

profiles : table avec un seul index sur l'id membre
profiles2 : table avec un index sur chaque champ
profiles3 : table avec 2 index multi-colonnes pour englober tous les champs

Les tables contiennent 200.000 enregistrements, ce sont les mêmes valeurs pour les 3 tables. Les valeurs des enregistrements sont générées aléatoirement (1 chance sur 2 d'avoir un champ à 0, à voir pour les tests).
Au début je voulais faire 10.000 itérations de requêtes, mais vous comprendrez en voyant les résultats qu'en fait je me sois limité à 10 requêtes.
Les résultats sont exprimés en secondes.
J'ai lancé 5 fois les jeux de test, afin d'avoir une valeur moyenne plus acceptable.
Les temps comprennent les requêtes suivantes (avant chaque requête) :

FLUSH STATUS
RESET QUERY CACHE

Pour chaque test, j'indiquerai la requête (changer simplement le nom de la table pour obtenir les 2 autres requêtes), ainsi que la liste des 3 moyennes obtenues (respectivement pour les 3 tables profiles, profiles2, et profiles3), et enfin un listing des explain de la requête pour comprendre les résultats.

Tables utilisées :

Code :

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

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
 
CREATE TABLE `profiles` (
  `pro_mem_id` int(10) unsigned NOT NULL,
  `pro_height` tinyint(3) unsigned NOT NULL default '0',
  `pro_weight` tinyint(3) unsigned NOT NULL default '0',
  `pro_bulk` tinyint(3) unsigned NOT NULL default '0',
  `pro_appearance` tinyint(3) unsigned NOT NULL default '0',
  `pro_hair_length` tinyint(3) unsigned NOT NULL default '0',
  `pro_hair_colour` tinyint(3) unsigned NOT NULL default '0',
  `pro_eyes_colour` tinyint(3) unsigned NOT NULL default '0',
  `pro_ethnic` tinyint(3) unsigned NOT NULL default '0',
  `pro_religion` tinyint(3) unsigned NOT NULL default '0',
  `pro_religion_practice` tinyint(3) unsigned NOT NULL default '0',
  `pro_marital_status` tinyint(3) unsigned NOT NULL default '0',
  `pro_kids_has` tinyint(3) unsigned NOT NULL default '0',
  `pro_kids_wants` tinyint(3) unsigned NOT NULL default '0',
  `pro_lives` tinyint(3) unsigned NOT NULL default '0',
  `pro_studies` tinyint(3) unsigned NOT NULL default '0',
  `pro_profession` tinyint(3) unsigned NOT NULL default '0',
  `pro_income` tinyint(3) unsigned NOT NULL default '0',
  `pro_cigarette` tinyint(3) unsigned NOT NULL default '0',
  `pro_alcohol` tinyint(3) unsigned NOT NULL default '0',
  `pro_food` tinyint(3) unsigned NOT NULL default '0',
  `pro_style` tinyint(3) unsigned NOT NULL default '0',
  `pro_purity` smallint(5) unsigned NOT NULL default '0',
  KEY `pro_mem_id` (`pro_mem_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
CREATE TABLE `profiles2` (
  `pro_mem_id` int(10) unsigned NOT NULL,
  `pro_height` tinyint(3) unsigned NOT NULL default '0',
  `pro_weight` tinyint(3) unsigned NOT NULL default '0',
  `pro_bulk` tinyint(3) unsigned NOT NULL default '0',
  `pro_appearance` tinyint(3) unsigned NOT NULL default '0',
  `pro_hair_length` tinyint(3) unsigned NOT NULL default '0',
  `pro_hair_colour` tinyint(3) unsigned NOT NULL default '0',
  `pro_eyes_colour` tinyint(3) unsigned NOT NULL default '0',
  `pro_ethnic` tinyint(3) unsigned NOT NULL default '0',
  `pro_religion` tinyint(3) unsigned NOT NULL default '0',
  `pro_religion_practice` tinyint(3) unsigned NOT NULL default '0',
  `pro_marital_status` tinyint(3) unsigned NOT NULL default '0',
  `pro_kids_has` tinyint(3) unsigned NOT NULL default '0',
  `pro_kids_wants` tinyint(3) unsigned NOT NULL default '0',
  `pro_lives` tinyint(3) unsigned NOT NULL default '0',
  `pro_studies` tinyint(3) unsigned NOT NULL default '0',
  `pro_profession` tinyint(3) unsigned NOT NULL default '0',
  `pro_income` tinyint(3) unsigned NOT NULL default '0',
  `pro_cigarette` tinyint(3) unsigned NOT NULL default '0',
  `pro_alcohol` tinyint(3) unsigned NOT NULL default '0',
  `pro_food` tinyint(3) unsigned NOT NULL default '0',
  `pro_style` tinyint(3) unsigned NOT NULL default '0',
  `pro_purity` smallint(5) unsigned NOT NULL default '0',
  KEY `pro_mem_id` (`pro_mem_id`),
  KEY `pro_height` (`pro_height`),
  KEY `pro_weight` (`pro_weight`),
  KEY `pro_bulk` (`pro_bulk`),
  KEY `pro_appearance` (`pro_appearance`),
  KEY `pro_hair_length` (`pro_hair_length`),
  KEY `pro_hair_colour` (`pro_hair_colour`),
  KEY `pro_eyes_colour` (`pro_eyes_colour`),
  KEY `pro_ethnic` (`pro_ethnic`),
  KEY `pro_religion` (`pro_religion`),
  KEY `pro_religion_practice` (`pro_religion_practice`),
  KEY `pro_marital_status` (`pro_marital_status`),
  KEY `pro_kids_has` (`pro_kids_has`),
  KEY `pro_kids_wants` (`pro_kids_wants`),
  KEY `pro_lives` (`pro_lives`),
  KEY `pro_studies` (`pro_studies`),
  KEY `pro_profession` (`pro_profession`),
  KEY `pro_income` (`pro_income`),
  KEY `pro_cigarette` (`pro_cigarette`),
  KEY `pro_alcohol` (`pro_alcohol`),
  KEY `pro_food` (`pro_food`),
  KEY `pro_style` (`pro_style`),
  KEY `pro_purity` (`pro_purity`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
CREATE TABLE `profiles3` (
  `pro_mem_id` int(10) unsigned NOT NULL,
  `pro_height` tinyint(3) unsigned NOT NULL default '0',
  `pro_weight` tinyint(3) unsigned NOT NULL default '0',
  `pro_bulk` tinyint(3) unsigned NOT NULL default '0',
  `pro_appearance` tinyint(3) unsigned NOT NULL default '0',
  `pro_hair_length` tinyint(3) unsigned NOT NULL default '0',
  `pro_hair_colour` tinyint(3) unsigned NOT NULL default '0',
  `pro_eyes_colour` tinyint(3) unsigned NOT NULL default '0',
  `pro_ethnic` tinyint(3) unsigned NOT NULL default '0',
  `pro_religion` tinyint(3) unsigned NOT NULL default '0',
  `pro_religion_practice` tinyint(3) unsigned NOT NULL default '0',
  `pro_marital_status` tinyint(3) unsigned NOT NULL default '0',
  `pro_kids_has` tinyint(3) unsigned NOT NULL default '0',
  `pro_kids_wants` tinyint(3) unsigned NOT NULL default '0',
  `pro_lives` tinyint(3) unsigned NOT NULL default '0',
  `pro_studies` tinyint(3) unsigned NOT NULL default '0',
  `pro_profession` tinyint(3) unsigned NOT NULL default '0',
  `pro_income` tinyint(3) unsigned NOT NULL default '0',
  `pro_cigarette` tinyint(3) unsigned NOT NULL default '0',
  `pro_alcohol` tinyint(3) unsigned NOT NULL default '0',
  `pro_food` tinyint(3) unsigned NOT NULL default '0',
  `pro_style` tinyint(3) unsigned NOT NULL default '0',
  `pro_purity` smallint(5) unsigned NOT NULL default '0',
  KEY `pro_mem_id` (`pro_mem_id`),
  KEY `index_full1` (`pro_height`,`pro_weight`,`pro_bulk`,`pro_appearance`,`pro_hair_length`,`pro_hair_colour`,`pro_eyes_colour`,`pro_ethnic`,`pro_religion`,`pro_religion_practice`,`pro_marital_status`,`pro_kids_has`,`pro_kids_wants`,`pro_lives`,`pro_studies`),
  KEY `index_full2` (`pro_profession`,`pro_income`,`pro_cigarette`,`pro_alcohol`,`pro_food`,`pro_style`,`pro_purity`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Tests lancés :

1) Une seule colonne, id membre

Code :

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

1
2
 
SELECT pro_mem_id FROM profiles WHERE pro_mem_id = 59

Résultats :

0,003988
0,003534
0,003392

Explain :

id : 1, select_type : SIMPLE, table : profiles, type : ref, possible_keys : pro_mem_id, key : pro_mem_id, key_len : 4, ref : const, rows : 1, Extra : Using index
id : 1, select_type : SIMPLE, table : profiles2, type : ref, possible_keys : pro_mem_id, key : pro_mem_id, key_len : 4, ref : const, rows : 1, Extra : Using index
id : 1, select_type : SIMPLE, table : profiles3, type : ref, possible_keys : pro_mem_id, key : pro_mem_id, key_len : 4, ref : const, rows : 1, Extra : Using index

2) Une seule colonne, première colonne de l'index multi-colonnes

Code :

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

1
2
 
SELECT pro_mem_id FROM profiles WHERE pro_height = 0

Résultats :

0,483313
2,483181
2,978085

Explain :

id : 1, select_type : SIMPLE, table : profiles, type : ALL, possible_keys : , key : , key_len : , ref : , rows : 200000, Extra : Using where
id : 1, select_type : SIMPLE, table : profiles2, type : ref, possible_keys : pro_height, key : pro_height, key_len : 1, ref : const, rows : 97282, Extra :
id : 1, select_type : SIMPLE, table : profiles3, type : ref, possible_keys : index_full1, key : index_full1, key_len : 1, ref : const, rows : 97678, Extra :

Ici problème : il a trouvé les index pour les tables 2 et 3, de type "ref". Et il parcourt 2 fois moins de lignes. Pourtant c'est 6 fois plus long. Pas compris...

3) Une seule colonne, pas première colonne de l'index multi-colonnes

Code :

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

1
2
 
SELECT pro_mem_id FROM profiles WHERE pro_weight = 0

Résultats :

0,486560
2,509621
0,486723

Explain :

id : 1, select_type : SIMPLE, table : profiles, type : ALL, possible_keys : , key : , key_len : , ref : , rows : 200000, Extra : Using where
id : 1, select_type : SIMPLE, table : profiles2, type : ref, possible_keys : pro_weight, key : pro_weight, key_len : 1, ref : const, rows : 93602, Extra :
id : 1, select_type : SIMPLE, table : profiles3, type : ALL, possible_keys : , key : , key_len : , ref : , rows : 200000, Extra : Using where

Ici encore problème : ok pour la 3ème table, il ne devrait pas trouver d'index, donc tout va bien. Par contre pour la table 2, même en utilisant un index, et en ne parcourant que 93.602 enregistrements, la requête est presque 6 fois plus lente que lors d'un simple WHERE (tables 1 et 3) qui pourtant parcourt les 200.000 enregistrements...

4) Plusieurs colonnes, index multi-colonnes 1

Code :

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

1
2
 
SELECT pro_mem_id FROM profiles WHERE pro_height = 0 AND pro_weight = 0

Résultats :

0,430885
2,397126
1,528556

Explain :

id : 1, select_type : SIMPLE, table : profiles, type : ALL, possible_keys : , key : , key_len : , ref : , rows : 200000, Extra : Using where
id : 1, select_type : SIMPLE, table : profiles2, type : ref, possible_keys : pro_height,pro_weight, key : pro_weight, key_len : 1, ref : const, rows : 93602, Extra : Using where
id : 1, select_type : SIMPLE, table : profiles3, type : ref, possible_keys : index_full1, key : index_full1, key_len : 2, ref : const,const, rows : 48645, Extra :

Normalement, on devrait avoir les mêmes résultats que la requête précédente, or les explain et les temps sont différents...

5) Plusieurs colonnes, index multi-colonnes 1, index multi-colonnes 2

Code :

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

1
2
 
SELECT SELECT pro_mem_id FROM profiles WHERE pro_height = 0 AND pro_weight = 0 AND pro_profession = 0

Résultats :

0,385203
2,449725
1,485718

Explain :

id : 1, select_type : SIMPLE, table : profiles, type : ALL, possible_keys : , key : , key_len : , ref : , rows : 200000, Extra : Using where
id : 1, select_type : SIMPLE, table : profiles2, type : ref, possible_keys : pro_height,pro_weight,pro_profession, key : pro_weight, key_len : 1, ref : const, rows : 93602, Extra : Using where
id : 1, select_type : SIMPLE, table : profiles3, type : ref, possible_keys : index_full1,index_full2, key : index_full1, key_len : 2, ref : const,const, rows : 48645, Extra : Using where

Mêmes temps que la requête précédente, or les explain sont différents...

6) Plusieurs colonnes, index multi-colonnes 1, PAS index multi-colonnes 2

Code :

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

1
2
 
SELECT pro_mem_id FROM profiles WHERE pro_height = 0 AND pro_weight = 0 AND pro_income = 0

Résultats :

0,389099
2,451293
1,471346

Explain :

id : 1, select_type : SIMPLE, table : profiles, type : ALL, possible_keys : , key : , key_len : , ref : , rows : 200000, Extra : Using where
id : 1, select_type : SIMPLE, table : profiles2, type : ref, possible_keys : pro_height,pro_weight,pro_income, key : pro_weight, key_len : 1, ref : const, rows : 93602, Extra : Using where
id : 1, select_type : SIMPLE, table : profiles3, type : ref, possible_keys : index_full1, key : index_full1, key_len : 2, ref : const,const, rows : 48645, Extra : Using where

Je comprends toujours pas... Le PIRE cas est celui sans index normalement (bon à part les jointure avec Extra : temporary table). Or la table 3 est toujours plus "lente" que la 1, alors que au pire elle devrait être aussi longue. (idem pour la 2 bien sûr, encore pire)

7) Plusieurs colonnes, PAS index multi-colonnes 1, PAS index multi-colonnes 2

Code :

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

1
2
 
SELECT pro_mem_id FROM profiles WHERE pro_weight = 0 AND pro_income = 0

Résultats :

0,426850
2,385575
0,432977

Explain :

id : 1, select_type : SIMPLE, table : profiles, type : ALL, possible_keys : , key : , key_len : , ref : , rows : 200000, Extra : Using where
id : 1, select_type : SIMPLE, table : profiles2, type : ref, possible_keys : pro_weight,pro_income, key : pro_weight, key_len : 1, ref : const, rows : 93602, Extra : Using where
id : 1, select_type : SIMPLE, table : profiles3, type : ALL, possible_keys : , key : , key_len : , ref : , rows : 200000, Extra : Using where

Pour résumer, à chaque fois, il repère les index, cela réduit de moitié les lignes parcourues, mais c'est toujours 6 fois plus long que le type "ALL" (qui est le pire). Et pourquoi on n'a pas le "Extra : Using index" comme pour la requête 1 ? ...

Au niveau poids des index pour les tables :

2 Mo
38 Mo
10 Mo

Voilà, donc très sincèrement, mes connaissances sont très modestes en BDD, donc je pige pas pourquoi il utilise pas les index qu'on lui file (et qu'il trouve), et pourquoi c'est plus long les SELECT sur une table avec index que sans index...

Les avis sont les bienvenus !!!

**Lideln75** · 30/11/2008, 21h45

Tableau récapitulatif :
(pas trouvé comment mettre des balises html ici, donc je vous le poste en "code")

Code :

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

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
 
<table border='1' style='border-collapse:collapse'>
<tr>
<th>query</th>
<th>id</th>
<th>select_type</th>
<th>table</th>
<th>type</th>
<th>possible_keys</th>
<th>key</th>
<th>key_len</th>
 
<th>ref</th>
<th>rows</th>
<th>extra</th>
<th>time (s)</th>
</tr>
<tr>
<td style="color:#000000">SELECT pro_mem_id FROM profiles WHERE pro_mem_id = 52968</td>
<td style="color:#000000">1</td>
<td style="color:#000000">SIMPLE</td>
<td style="color:#000000">profiles</td>
<td style="color:#000000">ref</td>
 
<td style="color:#000000">pro_mem_id</td>
<td style="color:#000000">pro_mem_id</td>
<td style="color:#000000">4</td>
<td style="color:#000000">const</td>
<td style="color:#000000">1</td>
<td style="color:#000000">Using index</td>
<td style="color:#000000">0,003988</td>
</tr>
<tr>
<td style="color:#3333CC">SELECT pro_mem_id FROM profiles2 WHERE pro_mem_id = 52968</td>
<td style="color:#3333CC">1</td>
 
<td style="color:#3333CC">SIMPLE</td>
<td style="color:#3333CC">profiles2</td>
<td style="color:#3333CC">ref</td>
<td style="color:#3333CC">pro_mem_id</td>
<td style="color:#3333CC">pro_mem_id</td>
<td style="color:#3333CC">4</td>
<td style="color:#3333CC">const</td>
<td style="color:#3333CC">1</td>
<td style="color:#3333CC">Using index</td>
<td style="color:#3333CC">0,003534</td>
</tr>
<tr>
<td style="color:#33CC33">SELECT pro_mem_id FROM profiles3 WHERE pro_mem_id = 52968</td>
<td style="color:#33CC33">1</td>
<td style="color:#33CC33">SIMPLE</td>
<td style="color:#33CC33">profiles3</td>
<td style="color:#33CC33">ref</td>
<td style="color:#33CC33">pro_mem_id</td>
<td style="color:#33CC33">pro_mem_id</td>
<td style="color:#33CC33">4</td>
 
<td style="color:#33CC33">const</td>
<td style="color:#33CC33">1</td>
<td style="color:#33CC33">Using index</td>
<td style="color:#33CC33">0,003392</td>
</tr>
<tr>
<td style="color:#000000">SELECT pro_mem_id FROM profiles WHERE pro_height = 0</td>
<td style="color:#000000">1</td>
<td style="color:#000000">SIMPLE</td>
<td style="color:#000000">profiles</td>
<td style="color:#000000">ALL</td>
 
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">200000</td>
<td style="color:#000000">Using where</td>
<td style="color:#000000">0,483313</td>
</tr>
<tr>
<td style="color:#3333CC">SELECT pro_mem_id FROM profiles2 WHERE pro_height = 0</td>
<td style="color:#3333CC">1</td>
<td style="color:#3333CC">SIMPLE</td>
<td style="color:#3333CC">profiles2</td>
 
<td style="color:#3333CC">ref</td>
<td style="color:#3333CC">pro_height</td>
<td style="color:#3333CC">pro_height</td>
<td style="color:#3333CC">1</td>
<td style="color:#3333CC">const</td>
<td style="color:#3333CC">97282</td>
<td style="color:#3333CC">&nbsp;</td>
<td style="color:#3333CC">2,483181</td>
</tr>
<tr>
<td style="color:#33CC33">SELECT pro_mem_id FROM profiles3 WHERE pro_height = 0</td>
 
<td style="color:#33CC33">1</td>
<td style="color:#33CC33">SIMPLE</td>
<td style="color:#33CC33">profiles3</td>
<td style="color:#33CC33">ref</td>
<td style="color:#33CC33">index_full1</td>
<td style="color:#33CC33">index_full1</td>
<td style="color:#33CC33">1</td>
<td style="color:#33CC33">const</td>
<td style="color:#33CC33">97678</td>
 
<td style="color:#33CC33">&nbsp;</td>
<td style="color:#33CC33">2,978085</td>
</tr>
<tr>
<td style="color:#000000">SELECT pro_mem_id FROM profiles WHERE pro_weight = 0</td>
<td style="color:#000000">1</td>
<td style="color:#000000">SIMPLE</td>
<td style="color:#000000">profiles</td>
<td style="color:#000000">ALL</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">&nbsp;</td>
 
<td style="color:#000000">200000</td>
<td style="color:#000000">Using where</td>
<td style="color:#000000">0,486560</td>
</tr>
<tr>
<td style="color:#3333CC">SELECT pro_mem_id FROM profiles2 WHERE pro_weight = 0</td>
<td style="color:#3333CC">1</td>
<td style="color:#3333CC">SIMPLE</td>
<td style="color:#3333CC">profiles2</td>
<td style="color:#3333CC">ref</td>
<td style="color:#3333CC">pro_weight</td>
 
<td style="color:#3333CC">pro_weight</td>
<td style="color:#3333CC">1</td>
<td style="color:#3333CC">const</td>
<td style="color:#3333CC">93602</td>
<td style="color:#3333CC">&nbsp;</td>
<td style="color:#3333CC">2,509621</td>
</tr>
<tr>
<td style="color:#33CC33">SELECT pro_mem_id FROM profiles3 WHERE pro_weight = 0</td>
<td style="color:#33CC33">1</td>
<td style="color:#33CC33">SIMPLE</td>
 
<td style="color:#33CC33">profiles3</td>
<td style="color:#33CC33">ALL</td>
<td style="color:#33CC33">&nbsp;</td>
<td style="color:#33CC33">&nbsp;</td>
<td style="color:#33CC33">&nbsp;</td>
<td style="color:#33CC33">&nbsp;</td>
<td style="color:#33CC33">200000</td>
<td style="color:#33CC33">Using where</td>
<td style="color:#33CC33">0,486723</td>
</tr>
<tr>
<td style="color:#000000">SELECT pro_mem_id FROM profiles WHERE pro_height = 0 AND pro_weight = 0</td>
<td style="color:#000000">1</td>
 
<td style="color:#000000">SIMPLE</td>
<td style="color:#000000">profiles</td>
<td style="color:#000000">ALL</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">200000</td>
<td style="color:#000000">Using where</td>
<td style="color:#000000">0,430885</td>
</tr>
<tr>
<td style="color:#3333CC">SELECT pro_mem_id FROM profiles2 WHERE pro_height = 0 AND pro_weight = 0</td>
 
<td style="color:#3333CC">1</td>
<td style="color:#3333CC">SIMPLE</td>
<td style="color:#3333CC">profiles2</td>
<td style="color:#3333CC">ref</td>
<td style="color:#3333CC">pro_height,pro_weight</td>
<td style="color:#3333CC">pro_weight</td>
<td style="color:#3333CC">1</td>
<td style="color:#3333CC">const</td>
<td style="color:#3333CC">93602</td>
 
<td style="color:#3333CC">Using where</td>
<td style="color:#3333CC">2,397126</td>
</tr>
<tr>
<td style="color:#33CC33">SELECT pro_mem_id FROM profiles3 WHERE pro_height = 0 AND pro_weight = 0</td>
<td style="color:#33CC33">1</td>
<td style="color:#33CC33">SIMPLE</td>
<td style="color:#33CC33">profiles3</td>
<td style="color:#33CC33">ref</td>
<td style="color:#33CC33">index_full1</td>
<td style="color:#33CC33">index_full1</td>
 
<td style="color:#33CC33">2</td>
<td style="color:#33CC33">const,const</td>
<td style="color:#33CC33">48645</td>
<td style="color:#33CC33">&nbsp;</td>
<td style="color:#33CC33">1,528556</td>
</tr>
<tr>
<td style="color:#000000">SELECT pro_mem_id FROM profiles WHERE pro_height = 0 AND pro_weight = 0 AND pro_profession = 0</td>
<td style="color:#000000">1</td>
<td style="color:#000000">SIMPLE</td>
<td style="color:#000000">profiles</td>
 
<td style="color:#000000">ALL</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">200000</td>
<td style="color:#000000">Using where</td>
<td style="color:#000000">0,385203</td>
</tr>
<tr>
<td style="color:#3333CC">SELECT pro_mem_id FROM profiles2 WHERE pro_height = 0 AND pro_weight = 0 AND pro_profession = 0</td>
<td style="color:#3333CC">1</td>
<td style="color:#3333CC">SIMPLE</td>
 
<td style="color:#3333CC">profiles2</td>
<td style="color:#3333CC">ref</td>
<td style="color:#3333CC">pro_height,pro_weight,pro_profession</td>
<td style="color:#3333CC">pro_weight</td>
<td style="color:#3333CC">1</td>
<td style="color:#3333CC">const</td>
<td style="color:#3333CC">93602</td>
<td style="color:#3333CC">Using where</td>
<td style="color:#3333CC">2,449725</td>
</tr>
 
<tr>
<td style="color:#33CC33">SELECT pro_mem_id FROM profiles3 WHERE pro_height = 0 AND pro_weight = 0 AND pro_profession = 0</td>
<td style="color:#33CC33">1</td>
<td style="color:#33CC33">SIMPLE</td>
<td style="color:#33CC33">profiles3</td>
<td style="color:#33CC33">ref</td>
<td style="color:#33CC33">index_full1,index_full2</td>
<td style="color:#33CC33">index_full1</td>
<td style="color:#33CC33">2</td>
 
<td style="color:#33CC33">const,const</td>
<td style="color:#33CC33">48645</td>
<td style="color:#33CC33">Using where</td>
<td style="color:#33CC33">1,485718</td>
</tr>
<tr>
<td style="color:#000000">SELECT pro_mem_id FROM profiles WHERE pro_height = 0 AND pro_weight = 0 AND pro_income = 0</td>
<td style="color:#000000">1</td>
<td style="color:#000000">SIMPLE</td>
<td style="color:#000000">profiles</td>
<td style="color:#000000">ALL</td>
 
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">200000</td>
<td style="color:#000000">Using where</td>
<td style="color:#000000">0,389099</td>
</tr>
<tr>
<td style="color:#3333CC">SELECT pro_mem_id FROM profiles2 WHERE pro_height = 0 AND pro_weight = 0 AND pro_income = 0</td>
<td style="color:#3333CC">1</td>
<td style="color:#3333CC">SIMPLE</td>
<td style="color:#3333CC">profiles2</td>
 
<td style="color:#3333CC">ref</td>
<td style="color:#3333CC">pro_height,pro_weight,pro_income</td>
<td style="color:#3333CC">pro_weight</td>
<td style="color:#3333CC">1</td>
<td style="color:#3333CC">const</td>
<td style="color:#3333CC">93602</td>
<td style="color:#3333CC">Using where</td>
<td style="color:#3333CC">2,451293</td>
</tr>
<tr>
<td style="color:#33CC33">SELECT pro_mem_id FROM profiles3 WHERE pro_height = 0 AND pro_weight = 0 AND pro_income = 0</td>
 
<td style="color:#33CC33">1</td>
<td style="color:#33CC33">SIMPLE</td>
<td style="color:#33CC33">profiles3</td>
<td style="color:#33CC33">ref</td>
<td style="color:#33CC33">index_full1</td>
<td style="color:#33CC33">index_full1</td>
<td style="color:#33CC33">2</td>
<td style="color:#33CC33">const,const</td>
<td style="color:#33CC33">48645</td>
 
<td style="color:#33CC33">Using where</td>
<td style="color:#33CC33">1,471346</td>
</tr>
<tr>
<td style="color:#000000">SELECT pro_mem_id FROM profiles WHERE pro_weight = 0 AND pro_income = 0</td>
<td style="color:#000000">1</td>
<td style="color:#000000">SIMPLE</td>
<td style="color:#000000">profiles</td>
<td style="color:#000000">ALL</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">&nbsp;</td>
 
<td style="color:#000000">&nbsp;</td>
<td style="color:#000000">200000</td>
<td style="color:#000000">Using where</td>
<td style="color:#000000">0,426850</td>
</tr>
<tr>
<td style="color:#3333CC">SELECT pro_mem_id FROM profiles2 WHERE pro_weight = 0 AND pro_income = 0</td>
<td style="color:#3333CC">1</td>
<td style="color:#3333CC">SIMPLE</td>
<td style="color:#3333CC">profiles2</td>
<td style="color:#3333CC">ref</td>
 
<td style="color:#3333CC">pro_weight,pro_income</td>
<td style="color:#3333CC">pro_weight</td>
<td style="color:#3333CC">1</td>
<td style="color:#3333CC">const</td>
<td style="color:#3333CC">93602</td>
<td style="color:#3333CC">Using where</td>
<td style="color:#3333CC">2,385575</td>
</tr>
<tr>
<td style="color:#33CC33">SELECT pro_mem_id FROM profiles3 WHERE pro_weight = 0 AND pro_income = 0</td>
<td style="color:#33CC33">1</td>
 
<td style="color:#33CC33">SIMPLE</td>
<td style="color:#33CC33">profiles3</td>
<td style="color:#33CC33">ALL</td>
<td style="color:#33CC33">&nbsp;</td>
<td style="color:#33CC33">&nbsp;</td>
<td style="color:#33CC33">&nbsp;</td>
<td style="color:#33CC33">&nbsp;</td>
<td style="color:#33CC33">200000</td>
<td style="color:#33CC33">Using where</td>
<td style="color:#33CC33">0,432977</td>
</tr>
</table>

**Lideln75** · 02/12/2008, 22h19

Si quelqu'un sait pourquoi la requête est 5 fois plus longue sur la table avec index et avec 100.000 lignes parcourues, que sur une table sans index et 200.000 lignes lues (2 fois plus donc)... Je suis preneur

**Sivrît** · 03/12/2008, 01h36

Je tenterais d'élaborer un peu plus dès que possible mais ça vient du fait que les recherches portent sur des valeurs que chaque enregistrement a 50% de chance d'avoir. Donc en gros on trouve la moitié de la table, un quart si on teste deux champs. Dans un cas comme ça MySQL devrait (idéalement) en fait décider de ne pas utiliser d'index, mais comme ses statistiques sur le contenu des tables sont frustres il prend une mauvaise décision.

Un index permet de récupérer directement les enregistrement concernées et donc de limiter les données examinées et donc les accès disques. Pour une poignée d'enregistrement parmi des milliers c'est très bien. Pour 100000 sur 200000 c'est différent car on remplace une grosse lecture de données contigües (donc à la vitesse maximale du disque qui sur une machine de bureau se compte en dizaines de Mo par seconde) par 100 000 accès aléatoires (en fait moins pour diverses raisons mais l'ordre de grandeur y est) pour un volume total de données juste moitié moins grand. Bref c'est pas terrible.

Heureusement chercher 0 ne devrait pas être la norme. Je me demande aussi si mettre NULL ne serait pas mieux. Le jour où il faudra chercher les gens de moins de 1m80 ça sera plus pratique.

**Lideln75** · 04/12/2008, 02h37

Ah oui pas bête les accès disque, en effet !
Donc ça signifie que sur les 100.000 enregistrements, il peut très bien en lire un à un bout du fichier, et l'autre à l'autre bout, et ensuite revenir à la fin, etc. ce qui expliquerait la lenteur ?
Oui pas très malin c'est sûr. Mais il n'y a pas beaucoup de moyens d'empêcher ça, soit je fais un index, soit je n'en fais pas, je ne peux pas prévoir les valeurs et leur nombre, ni empêcher un utilisateur de faire une recherche comme j'ai fait.

Je devrais peut-être faire quelques tests supplémentaires, plus restrictifs, du genre porter la clause WHERE sur peut-être 10 champs, ce qui ramènerait à 200.000 / 2^10 lignes environ (si on part du principe d'une chance sur deux d'avoir un champ à 0) soit 195 lignes. Ca dera déjà moins de lectures du fichier. Après en fait je peux aussi rajouter une clause LIMIT (pour dire "désolé il y a trop de résultats" et limiter les résultats sélectionnés à disons 100 ou 200, ce qui fait déjà pas mal de profils à regarder, et je ne pense pas que beaucoup de monde parcoure plus de 200 profils d'un coup lors d'une recherche)

Et si ça fonctionne et que c'est plus rapide... Je devrai alors peut être utiliser la table avec tous les index... (car pour l'instant je me suis rabattu sur celle sans index pour continuer).

Merci pour l'astuce, je vais faire d'autres tests demain si j'ai le temps et je vous tiendrai au courant pour vérifier les temps mis !

Bonne fin de soirée,

Requête sur table avec beaucoup de champs

Requêtes MySQL

Discussions similaires

Partager

Partager