MySQL limité à 16 index par table

Version imprimable

30/04/2024, 10h13
clavier12AZQSWX

MySQL limité à 16 index par table

bonjour,

j'ai une table avec plus de 40 champs dont 24 ayant besoin d'un index.
Au moment de la création, j'ai une erreur francisée qui dit :

#1070 - Trop de parties specifiées dans la clef. Maximum de 16 parties

je suppose que c'est l'erreur anglaise : too many key parts specified max 16 parts allowed

est-ce que ce 16 est modifiable ?
Ma version de mysql est 5.7.42-log (sur un hébergement mutualisé)

Merci de votre conseil et lecture.
30/04/2024, 10h53
Séb.

Tu veux créer 1 index contenant plus de 16 colonnes.

Citation:

A maximum of 16 columns is permitted for multicolumn indexes. Exceeding the limit returns an error.

Citation:

ERROR 1070 (42000): Too many key parts specified; max 16 parts allowed

https://dev.mysql.com/doc/refman/5.7...db-limits.html
30/04/2024, 11h11
clavier12AZQSWX
bonjour,

j'ai fait une erreur de manipulation.

J'utilise phpmyadmin, je me suis rendu compte que cocher plusieurs colonnes et utiliser l'option "index" en bas de la liste des champs, ne permettait pas de créer un index pour chacune des colonnes mais un index de colonnes fusionnées:ptdr:

en gros, au lieu de générer ça quand on coche les colonne 1 2 et 3:
Code:

1 2 3 4 ALTER TABLE `personnes` ADD INDEX(`creneau01`); ALTER TABLE `personnes` ADD INDEX(`creneau02`); ALTER TABLE `personnes` ADD INDEX(`creneau03`);
il générait ceci :

Code:

ALTER TABLE `personnes` ADD INDEX( `creneau01`, `creneau02`,`creneau03` );

ce qui n'est pas du tout la même chose ! ça m'apprendra à être fénéant et utiliser les outils IHM plutôt que les lignes de commandes !
Je passe en résolu!

même si je viens de découvrir qu'il existe aussi une limite de 64 champs indéxés par table !!:aie:

Citation:

#1069 - Trop de clefs sont définies. Maximum de 64 clefs alloué

Conclusion : on peut pas utiliser une seule table pour gérer une matrice 3d de 24 champs !!
30/04/2024, 16h38
Séb.

Citation:

Conclusion : on peut pas utiliser une seule table pour gérer une matrice 3d de 24 champs !!

Cela dépend sûrement de ta modélisation ;)
30/04/2024, 19h40
SQLpro

Citation:

Envoyé par clavier12AZQSWX

Conclusion : on peut pas utiliser une seule table pour gérer une matrice 3d de 24 champs !!

Une matrice 3 D c'est du spatial. Le problème de MySQmerde c'est qu'il ne ait pas gérer le spatial à 3 dimenion. Même avec 2 dim il donne déjà des résultats faux.

Dans une cartouche SIG tu peut créer des nuages de points à 3 dimensions : X, Y et Z.... Voir mon cours sur le spatial. Mais les seuls SGBDR qui avent faire ça c'est Oracle, MS SQL Server PG ne gère pas le Y...

Exemple MS SQL Server :

Code:

DECLARE @POINTS GEOMETRY = CAST('MULTIPOINT ((25 12 3), (35 11 6))' AS GEOMETRY)

A me lire : https://blog.developpez.com/sqlpro/p...on_geographiqu

A +
04/05/2024, 16h53
Artemus24

Salut à tous.

Citation:

j'ai une table avec plus de 40 champs dont 24 ayant besoin d'un index.

Les habitudes ont la vie dure. Il ne s'agit pas de champs mais de colonnes.
Pourquoi autant d'index pour une table contenant 40 colonnes ?
06/05/2024, 11h33
clavier12AZQSWX

Citation:

Envoyé par Artemus24

Salut à tous.

Les habitudes ont la vie dure. Il ne s'agit pas de champs mais de colonnes.
Pourquoi autant d'index pour une table contenant 40 colonnes ?

une table qui gère des "produits" à plus de 24 propriétés sélectionnables (donc 24 tables étrangères différentes liées).
Dans un vieux poste, avant la création du schéma, je m'étais demandé s'il fallait créer une table catégorie "fourtout" où des valeurs numériques serait mélangées à des valeurs AN, pour simplifier le modèle relationnel.
Contre mon envie personnelle, j'ai donc opté pour la solution de rester "sémantique" et pas aller dans la bidouille. Même si je me suis rapproché du "keep it simple", je reste convaincu que la solution foure-tout aurait été la meilleure...

Le cas d'étude est simple : comment formaliser un produit à 40 propriétés.
-1 : Une seule table maitre et 40 tables étrangères pour chacune des propriétés
- 2 : une table maitre et une table "typologie (id,lib,valeur,typologie) et une table-relation appartenance pour faire le lien.
Le fait de mélanger dans une seule table des données métrique,date,AN était déconseillée....

C'est la même question qu'à du se poser l'ingénieur date de site amazon : un table avec chaque type de propriété ou une seule table propriété où l'on mélangerait des données de vêtements avec des données alimentaires avec des données techniques...
14/05/2024, 08h06
escartefigue

Pour ce qui concerne les recherches, hors considérations d'index couvrant donc, un index n'est pertinent que s'il est discriminant.
Par exemple, créer un index sur le code sexe d'une table des personnes physiques ne présente aucun intérêt concernant les recherches puisque grosso-modo, chaque valeur représentera 50% de l'effectif de la table.

Donc, avant de créer un index pour chaque colonne (et non pas "champ") de la table, vérifiez combien de valeurs distinctes chaque colonne peut prendre et quels sont les besoins de recherche sur chaque colonne.

Il ne faut pas oublier que chaque index impose une mise à jour supplémentaire lors des UPDATE, INSERT et DELETE, puisqu'il faut maintenir non seulement la page DATA et la page de l'index PK, mais aussi tous les autres index.
Evidement, les servitudes (réorgs, sauvegardes...) sont également pénalisées par la pléthore d'index, ainsi que l'espace disque.
06/06/2024, 17h23
StringBuilder

Aussi, indexer 24 colonnes, c'est bien, mais il est rare que dans une requête, la clause WHERE porte sur une unique valeur, et qu'il ne se balade pas un ORDER BY quelque part.

Ainsi, il est généralement préférable de créer quelques index composites (limités à 16 colonnes, ce qui est déjà pas mal) plutôt que 24 index mono-colonnes qui n'amélioreront pas significativement les performances dans la plupart des cas.

Par exemple pour une adresse, on aura les 4 colonnes villes/cp/rue/numéro (et éventuellement pays si on fait de l'international).
=> Ca n'a aucun sens d'indexer les 4 colonnes séparément. en revanche, créer un index sur ville / rue / numéro permettra d'accélérer toutes les recherches qui portent sur la ville, sur ville et rue, ainsi que ville, rue et numéro. CP ici ne sera pas indexé, ou alors il y aura un second index qui remplace ville par cp... le mieux étant d'avoir les villes + cp dans une table séparée, et à ce moment on n'a qu'une clé étrangère à indexer.
07/06/2024, 09h16
tbc92

Si demain l'utilisateur veut ajouter une 41ème propriété, il doit donc faire appel à l'informaticien, qui va modifier les structures des tables et modifier les programmes ?
07/06/2024, 11h14
clavier12AZQSWX

Citation:

Envoyé par tbc92

Si demain l'utilisateur veut ajouter une 41ème propriété, il doit donc faire appel à l'informaticien, qui va modifier les structures des tables et modifier les programmes ?

Dans mon cas, sans trop dévoiler le projet, j'étais dans un système métrique fini à 24 unités (24 créneaux horaires), donc à moins qu'on invente la 25eme heure, ce n'est pas prêt d'arriver !
07/06/2024, 11h35
escartefigue

Quoi qu'il en soit, 24 colonnes indexées est symptomatique d'une erreur de modélisation et sera la source de nombreux inconvénients déjà cités plus haut.

Plusieurs solutions sont possibles, par exemple une seule colonne heure valorisée de 1 à 24 et un partitionnement des données sur cette colonne.
07/06/2024, 12h06
Artemus24

@ Escartefigue : si j'ai bien compris le problème, il s'agit d'une sélection multicritères.
07/06/2024, 14h45
escartefigue

Selon le cas de figure, on peut soit utiliser l'héritage, soit un méta-modèle, soit une combinaison des deux.

Cf. § pertinence des caractéristiques pour un type d'entité dans ce tuto
https://merise.developpez.com/faq/?p...eVariablePptes
11/06/2024, 19h29
Michel.Priori

Citation:

Envoyé par clavier12AZQSWX

une table qui gère des "produits" à plus de 24 propriétés sélectionnables (donc 24 tables étrangères différentes liées).

Le fait qu'il y ait 24 propriétés n'implique pas qu'il y ait des tables "étrangères différentes liées" !

Une propriété est normalement associée à un scalaire.
De ce que je comprends, la fameuse table produit serait associable à 24 classes de propriétés.
Du coup c'est la table produits qui serait une table "fourretout".

Peux tu nous en dire plus sur ton besoin réel ?

J'ai bien peur que ton modèle va imposer des outer join à tire larigot et poser très rapidement des problèmes de performances.
Surtout si un produit ne relève, au final, que d'une seule classe à la fois.
14/06/2024, 16h09
SQLpro
Citation:

Envoyé par clavier12AZQSWX

Dans mon cas, sans trop dévoiler le projet, j'étais dans un système métrique fini à 24 unités (24 créneaux horaires), donc à moins qu'on invente la 25eme heure, ce n'est pas prêt d'arriver !

Dans ce cas il faut créer une table de chonodatation avec toutes les dates et une ligne par heure. Exemple :
Code:

1 2 3 4 5 CREAT TABLE T_CHRONO_CHR (CHR_ID INT PRIMARY KEY, CHR_DATE DATE NOT NULL, CHR_TIME TIME NOT NULL, UNIQUE (CHR_DATE, CHR_TIME));
A +