Hello prog,
 
Je souhaite modéliser un cube avec ses coordonnées. Ce cube fait 205 de côté soit un peut plus de 8 millions de points.
De base, en coordonnées cartésiennes, on a donc des valeurs de x, y et z allant de 1 à 205.
 
Je suis parti sur une table avec une clé primaire composé de 3 champs : coordx, coordy, coordz.
Le truc, c'est que je trouve ça un poil lent.
 
En sachant que je compte forcément accéder aux données en passant par les coordonnées, y aurait-il une alternative à ce type de modèle ?
Je pensais essayer avec un seul champs avec les coordonnées sous la forme 'x,y,z' mais j'ai peur que ce soit pire pour l'indexation vu qu'on est plus sur des entiers mais du texte
 
Précision : je suis sous MySQL 5.0.45 avec le moteur InnoDB
 
Merci pour vos conseils

Non, utilise bien les 3 champs et pas une autre combinaison.
 
Au pire, vu que 205 < 255, et que tout processeur travaille au moins en 32 bits, tu peux faire un truc bien gore genre :
 
int coord = x << 16 + y << 8 + z;
 
=> Et ainsi ne stocker les coordonnées que dans un seul champ. Cependant, niveau perfs, je suis très loin d'être convaincu du gains réel (parcequ'aurant ce seratoujours aussi rapide pour trouver x, autant pour y et z ça va être plus aléatoire niveau performances.
 
Par contre, peux-tu en dire un peu plus sur ton cube ? Tu stockes quoi là dedans ?
Chaque élément du cube est rempli ? Ne peux-tu pas effectuer des regroupements en méta-éléments ?
 
Lorsque tu recherches un élément ou une série d'éléments, tu utilises quoi ? x = val1 and y = val2 and z = val3 ou des calculs géométriques ?
Dans le second cas, alors je te conseille de laisser tomber MySQL pour passer à PostGre, qui dispose de libs géométriques, qui devraient te permette de gagner en performances : genre un champ peut être de type "point", et du peux appliquer une fonction de recherche en fonction d'une distance sur d'autres points, nativement dans le moteur plutôt qu'avec une requête SQL imbittable et plus lente.

y a des points 2D dans postgresql, mais pas 3

Ah, c'est ballo ça

mysql aussi sait gérer des coordonnées :
http://dev.mysql.com/doc/refman/5. [...] model.html
Par contre là aussi ça semble être un système 2D.

Quand ça veut pas, ça veut pas

RiderCrazy > Juste pour savoir c'est quoi la requête de création de ta table? (ne la donne que si tu sais que tu as le droit de le faire)
C'est quoi que t'appelle "un poil lent"? (1/10éme de seconde, 10 secondes, 20 minutes)
C'est à quel moment que tu trouves ça lent? (a l'insertion, à la suppression, à la consultation, ...)
 
T'es obligé d'avoir des données pour toutes les coordonnées?
 
EDIT : C'est quoi ton besoin de rapidité?

ok
 

Ca me parait tordu... et niveau perf, pas sur du coup non plus
 

J'ai juste deux champs en plus dans la table. Sinon, elle est aussi reliée à d'autres tables. D'où le souhait d'optimiser tout ça.
Y'aura des affichages de base en 2D (20 par 20 sur un plan 2D) mais aussi des calculs. Enfin, c'est hétérogène et du coup, ça risque de charger, surtout en lecture. En écriture, ça devrait être plus léger.

Merci. Du coup, c'est pas nécessaire de passer sous postgresql

Si tu affiches des plans 2D, selon les traitements que tu vas faire dedans, il peut être judicieux d'utiliser donc un point2D plus un indice de profondeur.
ceci dit, il faut que tes tranches 2D soit toujours // pour que ça soit utile.
 
effectivement, le fait que tu emploies des termes d'imagerie pour parler de ta base me font penser à des traîtements géométriques à faire dans la base, d'où mon insistance sur l'utilisation de ce type.
 
ça me rappelle un jeu que je voulais faire à un moment.
j'avais laissé tombé après avoir saturé le disque dur à la création de la base

Bon, le int(11), faut que je le change. C'est un coup de powerAMC ça
 
Enfin, rien de bien transcendant pour la création. Bon, après, j'ai fait un tit script en php pour peupler ça. Enfin, 3 boucles imbriqués de base. Ca tourne un moment et voilà.
 
Là où je trouve ça lent, c'est sur la consultation :

met un peu plus de 6/10ème de seconde.
 
Après, pour le besoin, j'ai pas vraiment évalué. Mais là, j'ai 3 tables qui font référence à celle-ci et à terme je vise au moins 250 utilisateurs simultané dessus. Donc, je venais me renseigner sur les possibilités d'augmenter légèrement les perfs

Tu vises juste

c'est quoi tes index ?
 
le plus fin niveau filtre, dans ton cas, c'est Z : mets-le donc en première position dans la PK.
 
et organise ta table en cluster selon la PK.

moi je voulais faire un civilisation multi-planète-systèmes solaires et tout ça.
sauf que j'avais visé un peu gros et gourré dansun calcul. la base, au lieu de faire 1 Go faisait 1 To... seulement sur un disque de 8 Go ça tenait pas

Lapin
Accessoirement, là j'ai fait un plan sur z, mais je souhaiterais à terme pouvoir le faire x ou y

Forcément
 
Et du coup, t'as laché le morceau ?

Ben du coup SQL Server Query Analyser a planté et j'avais pas sauvegardé mes scripts, ça m'a saoûlé

Lapin quoi ? (rien pigé moi )
 
Sinon, qu'est-ce qui t'empêche de t'affranchir de Z ?

Voilà (lapin compris... m'enfin)
 
Et je vois pas bien comment je pourrais me passer de z

sinon, les base pgsql on une extension spatial permettant de faire des requetes spatiales ( du genre extraire les point a moins de 3km d'un point d'origine, ou du genre calcul d'un temps de parcours )

C'est ce que j'ai suggéré dans mon premier poste, mais ça à l'air de n'être qu'en 2D.
 
Mais visiblement, c'est pas un problème puisque seul un plan Z semble être utilisé à la fois.

Je dis peut être une bêtise mais, si on a une coordonnée fixe pour chaque requête, on peut peut être définir 3 index, chacun portant sur une seule des coordonnées  
(c'est juste une piste, je prétends pas que ca résoudra tout )

Je vois toujours pas par quel moyen je pourrais me passer de Z.
Certes, je vais faire des affichages dans le plan. Mais je vais avoir des calculs dans l'espace et puis même l'affichage lui-même pourra changer de plan...

Euh... c'est pas ce que j'ai fait ? ou alors j'ai pas compris ta remarque...

Là :
PRIMARY KEY  (`coordx`,`coordy`,`coordz`)
tu définis 1 index (composé de 3 champs).
 
Une solution serait de créer 3 index, distincts. Chacun ne porterait que sur 1 seule coordonnée.
MySQL choisira l'index le plus adapté en fonction de la requête (sinon, il y a toujours le moyen de l'imposer dans la requête)

Bon, je teste avec la requête postée précédemment.
Déjà, j'ai modifié la taille des champs. J'suis passé de int(11) à tinyint(4). Résultat : j'ai gagné 5/10ème de seconde. Je pensais pas que ce serait autant...
Avec la clé primaire composée, ça tappe dans les 0,11-0,12 sec.
Avec les 3 index, on est à 0,13-0,14 sec.
 
Je vais donc rester sur la composée... mais y'a de l'idée

Nan, il filtre toujours sur les 3 champs : un critère = et deux between.
 
Donc il faut conserver l'index actuel.
Par contre, il peux le cloner pour avoir toujours un index donc le premier élément correspond au critère = (plus rapide) :
create unique index on cube (coordy, coordx, coordz);
create unique index on cube (coordz, coordx, coordy);
 
PS : oublie le coup du supprimer Z, j'avais mal lu une de tes réponses.
 
normalement, le dernier index que j'ai proposé doit te permettre d'améliorer les performances sur la requête que t'as posté en exemple.

Nan, les 3 index "simples" c'est une très mauvaise idée, dans la mesure où tu filtres sur chacun des 3 champs. Donc tu vas faire des rangescan inutiles sur les deux champs suivants.

Pas con ça... m'en vais tester voir ce que ça donne.

J'espère que ça va améliorer un peu.
Mais ne t'attends pas à des miracles non plus...

Bon, ça a mouliné un bon moment... pour pas grand chose. Au final c'est plus long (0,18-0,19). Ca doit certainement être à cause de la taille des index

Je vais me renseigner sur les possibilités d'optimisation du moteur (taille du cache, etc.) vu que là, je suis bloqué au niveau de la structure de la base...

un index meme utilisé correctement ne va pas encore rendre ta requete instantanée, même si tu dois t'assurer avant d'aller voir plus loin qu'il est bien utilisé dans ton explain plan.
 
des que tu commences a avoir des tables de millions de ligne et que tu veux des acces rapide il y a un autre facteur a prendre en compte, bien que la théorie sql te dit que tes données peuvent être stockée dans n'importe quel sens il est important de bien comprendre le mécanisme.
j'explique:
tu tronques ta tables et tu réinseres avec un truc du genre

cela veut dire que vraisemblablement dans la db se trouveront d'abord tes 4000 lignes avec x=1, puis tes 4000 lignes avec x=2 etc,
et donc si maintenant tu veux simplement toutes tes lignes avec un z=190, il va devoir aller chercher tes lignes dans 255*255 pages mémoires différentes car non contigue.
alors que si dans ta boucle d'insertion tu avais commencé par insérer les z, la toutes tes lignes se trouvait dans un nombre réduit de page mémoire et donc le moteur ne devra ramener que très peu du disque a ton buffer cache.
 
si ton serveur n'est pas véloce ca peut jouer, parceque la en réalité avec ton query il devra eu grand minimum scanner 4% de tes lignes si elles sont organisées tiptop, et en terme de page cela peut correspondre a beaucoup a cause du débordement.

Intéressant ça... Du coup, j'ai testé avec x fixe puis un between pour y et z et c'est flagrant. Ca tourne dans les 3/1000ème de seconde.
Comme je pense que le plan le plus utilisé sera x/y, je vais passer mon z en premier.
M'enfin, pour les changement de plan, on reviendra sur le même "problème" mais bon, si déjà 90% des accès (à la louche) sont optimisés, c'est toujours ça de gagné.

Normalement, cela ne dépend pas de l'ordre d'insertion, mais de l'ordre de la PK, si par défaut (généralement oui) la PK est organisée en cluster.
Mais en gros, c'est ça en effet.

Donc faut aussi que je modifie ma PK pour mettre z devant je suppose...

C'est préférable. Je ne sais pas si MySQL fonctionne avec des PK organisées en cluster, dans dans tous les cas, la PK commençant par Z sera plus rapide pour tes requêtes avec Z fixe (donc cas le plus courant apprement)

Oui, ce sera certainement le cas le plus courant. Et si le serveur rame trop pour les changements de plan, on supprimera la fonctionnalité

Pour tester chez moi, j'avais justement utilisé la méthode de casimimir. Bizarrement c'était tellement rapide que je ne voyais pas de différence notable même en changeant les index ou en essayant le partitionnement des tables de mysql5.1 . Je pensais que c'était ma machine qui était trop puissante pour être ralenti mais l'explication de casimir est plus probable (et logique une fois qu'on y a pensé).

Ah ben oui, complètement, si les donnés d'un même plan sont éparpillée (en plus, ici, de façon homogène) sur tout le fichier de la base, évidement ça va nécessiter une lecture complète sur le disque à chaque requête.
 
Cependant, je suis très étonné qu'une table "aussi petite" (pas en nombre de ligne, mais en taille physique) bouffe soit impactée par ceci. Car elle ne réprésente que quelques Mo en mémoire logiquement.
Et en mémoire, même si on doit faire plusieurs changements de page, cela devrait se quantifier en micro-secondes, pas en dixièmes...

En taille physique, on est sur du 250Mo là.
Pour la vitesse, dans les 2/100ème de seconde pour la première exécution puis on tombe à 1/1000ème pour les suivantes avec z fixe. Par contre, si je passe sur du x ou y fixe, on tape dans le dixième de seconde facile

Niveau config, je suis sur un centrino duo avec 1Go de ram. Enfin, c'est mon portable, le disque est pas non plus une bête de course. Faudrait que je test sur mon petit serveur pour voir ce que ça donne mais la différence devrait pas être énorme (sempron, 1Go, disque en SATA 7200 rpm)

au fait, tinyint(1) devrait être suffisant non (enfin... le nombre ça correspond à quoi ? au nombre de bits ou aux chiffres ? si c'est les chiffres alors ce sera 3... y'a pas un type "byte" ?
 
ça va réduire considérablement la taille.
 
sinon, vu la quantité et RAM et le fait que t'as un HD de portable... oublie les perfs pour le moment. t'es dans les pires conditions qui soient pour une bdd.