Discussion :

[dcz]

Bonjour,

Donc, je suis en train de mettre en place une application dont le but principal sera d'effectuer des recherches multicritères sur mysql (4.1).

La question que je me pose, vu le nombre relativement important de critères (plus de 15) est la suivante :

Vaut il mieux définir une table "element", avec une colonne par critère (element_id, inof1, info2, .., infoN, critere1, critere2 ... critereN) mise en index, ce qui implique pas mal d'index sur la table (un par critère), ou faire une table par critère (table critereN : element_id, critereN) et sortir les résultats avec un left join sur la table contenant les informations de l'élément, au risque d'avoir un nombre important de join?

J'ajoute que les recherches seront majoritairement multicritères, et pour une bonne partie avec la majorité des critères, il y aura relativement peu d'insert comparé aux select.

[ced]

Bonjour,

Personnellement, je penche pour le second schéma, et pour plusieurs raisons, notamment :

• trop d'index sur une même table peut dégrader les performances ;
• avec le second schéma, tu peux ajouter, voire enlever, des critères sans toucher à la structure des bases.

ced

[Antoun]

Si tu as des conditions de type "au moins 10 critères remplis sur les 15", la seconde solution s'impose.

[dcz]

Merci pour cette réponse.

C'est un peu ce que je me disais, j'ai juste un peu d'appréhension pour des left join sur 15 tables.

A propos des index, j'ai pas mal lu que ça ralentissait les insert, mais en lecture, et avec les colonnes sélectionnées dans l'ordre du plus grand nombre de doublons vers le plus petit, cela compte-il autant ?

Et du coup question subsidiaire, penses tu qu'une sorte d'entre deux puisse valoir la peine, genre regrouper les critères par catégorie de critère, et en faire une table. Ce qui donnerait des left join sur moins de tables, mais qui impliquerait des index dans les tables groupant des critères (en tout autant que sur la table qui les regrouperait tous).

L'évolution des critères n'est pas le plus important, c'est une opération qui serait dans mon cas exceptionnelle. Je cherche avant tout à optimiser les select.

Merci encore

[edit] @Antoun on poste en même temps lol.

Donc, pour préciser, l'application ne fera pas de select type au moins 10 crières sur 15, mais plus du type critere1 = xx and critere2 > yy and critere3 between zz and zzz etc ...

[Antoun]

Merci pour cette réponse.

C'est un peu ce que je me disais, j'ai juste un peu d'appréhension pour des left join sur 15 tables.

des LEFT JOIN sur 15 tables ???

[dcz]

Désolé, je dois pas être très clair.

Donc, si j'ai une table article avec des colonne purement informative qui définissent l'article de manière générale id, catégorie titre etc.
Je veux pouvoir trier ces information selon une 15 aine de critères pouvant tous être utilisés en même temps pour construire une liste d'articles satisfaisant ces critère. Par "critère" j'entends une colonne dont le contenu va très souvent être utilisé pour trier les résultats.

Genre tous les article de couleur bleu et dont le prix est inférieur à tant et la date de fin de promo et le etc ... sur une quinzaine de critères principalement des nombres qui varient en nombre de doublons de presque tous à presque aucun.

Je refait pas kelkoo, mais l'exemple est parlant. Un article dans plusieurs catégories à la fois en quelques sorte, le bleu, les vert ... .

Donc, comme cette table va vraiment être la plus sollicitée de l'appli, et très majoritairement en lecture, je suis intéressé par vos avis et expériences.

Je vois trois alternatives, soit tout mettre dans la même table avec des index, soit créer une table par critère, ce vers quoi ced à l'air de pencher, soit créer un nombre plus limité de table, qui grouperaient les critères, genre une table : aspect => colonnes : - id - couleur - matière - style
etc ..
Pour limiter le nombre de table en jointure.

Donc oui, j'ai toujours un peu d'appréhension pour des left join sur 15 tables, mais je vois pas comment éviter ça si je fais une table par critère. La troisième possibilité pourrait limiter le nombre de tables sélectionnées, mais j'ai l'impression qu'on y gagnerait pas tellement en index, donc ...

Bon je me prend peut être un peu la tête, mais comme c'est le moment

[Antoun]

Une requête qui utilise les quinze critères sollicitera les quinze index (s'ils existent et si l'optimiseur les juge rentables), qu'ils soient dans une seule table ou dans quinze. Elle sera donc plus rapide si tout est dans la même table, parce que tes 15 jointures externes vont être très pénalisantes.

plus généralement, le partitionnement vertical (déplacer des colonnes dans une autre table) n'améliorera que les requêtes qui n'utilisent pas ces colonnes. Les autres requêtes seront au contraire ralenties par la jointure. Autrement dit, le partitionnement vertical n'est intéressant que pour isoler des colonnes rarement utilisées.

[Antoun]

... En fait, j'avais mal compris ton histoire de "une table par critère". Dans mon idée (et je pense que ced avait compris comme moi), il s'agissait de faire un schéma comme ça :

• element(element_id, info1, info2...)
• criteres(critere_id, critere_nom)
• valeurscriteres(element_id, critere_id, critere_valeur)

Ce qui te donne effectivement plus de souplesse pour ajouter/supprimer des critères et pour calculer des conditions de type 10 critères remplis sur 15. Si tu n'as besoin ni de l'un ni de l'autre, une seule table te donnera de meilleures performances.

Si tu n'en as pas besoin, je pense que le mieux est de créer un index multi-colonnes, par ordre de sélectivité. Par exemple, si tu as trois criteres couleur (choix entre 200 coloris différents), matière (choix entre 10 matières) et style (deux styles différents), tu as intérêt à créer un index sur couleur et coloris, dans cet ordre (avec une sélectivité de 50%, un index sur style n'est pas très intéressant).

D'un autre côté, si les utilisateurs font un critère de matière sans critère de couleur, ils n'utiliseront pas ton index composé. Pour eux, il vaut mieux créer un index avec simplement la matière.

Au total, tu vas donc devoir créer un index mono-colonne sur chaque critère, ainsi qu'une série d'index multi-colonnes. Ralentir les INSERT n'est pas un problème, parce que les INSERT sont très rapides. L'objectif est d'accélérer les gros SELECT complexes, qui peuvent, eux, être assez long.

[dcz]

C'est exactement ça

Donc, va pour la grosse table fourrée d'index. Je ne pensais pas que des index multi-colonnes seraient rentables, car j'avais lu qu'il y avait une limite dans le nombre d'index par table (15 dans l'article qui en parlais, pas sur developpez.net je précise, car je n'ai pas retrouvé cette limite dans le manuel mysql 4.1, mais il a pu m'échapper).

Mais si ce n'est pas le cas, je peux effectivement ajouter quelques index multi-colonne dans un deuxième temps, une fois que l'appli aura suffisamment tournée pour que j'ai des stats sur les combinaisons de critères les plus utilisés.

Et désolé si je suis gourmand de vos réponses, mais me viens à l'esprit une question subsidiaire :
Je pense ne proposer certains critères qu'aux membres d'un certain groupe, les autres auront une version allégée, ne reprenant pas tous les critères ni leur valeurs.
Du coup, je me demande s'il vaudrait mieux :

1. se borner à limiter le nombre de champs dans le select pour les membres n'ayant pas suffisamment de droits, dans l'hypothèse de la table unique;
2. séparer les critères utilisable sous réserve d'autorisations suffisantes dans une autre table que je joindrait à la première le cas échéant: critere_auth(element_id, critere1, critere2 etc ...) avec le même genre d'index sur les critères que sur la table "principale".

L'applis devrait être majoritairement utilisée par des membre du groupe pouvant effectuer des recherches sur tous les critères, mais si il y a un petit gain pour les autres sans trop de perte pour les premiers, ça pourrait valoir le coup.

Bon, c'est certainement du chipotage, mais c'est aussi le signe que vous m'avez été d'un grand secours

Et puis, c'est le genre de truc que je pourrais tester par la suite, avec une db conséquente (toutes les requêtes sur cette table seront construites par la même fonction). Si je peux ajouter 15 index sur la même table sans mettre le serveur à plat, la solution à une table me parait donc être un bon début.

Je vais pouvoir passer aux choses sérieuses

[Antoun]

Du coup, je me demande s'il vaudrait mieux :

1. se borner à limiter le nombre de champs dans le select pour les membres n'ayant pas suffisamment de droits, dans l'hypothèse de la table unique;
2. séparer les critères utilisable sous réserve d'autorisations suffisantes dans une autre table que je joindrait à la première le cas échéant: critere_auth(element_id, critere1, critere2 etc ...) avec le même genre d'index sur les critères que sur la table "principale".

L'applis devrait être majoritairement utilisée par des membre du groupe pouvant effectuer des recherches sur tous les critères, mais si il y a un petit gain pour les autres sans trop de perte pour les premiers, ça pourrait valoir le coup.

Je ne crois pas que ça t'apporte grand-chose, sans doute pas assez pour compenser la jointure externe, mais rien ne t'empêche de tester.

[dcz]

Du coup, !

Marci