Et tu parlais de 200 colonnes.. tu rigoles ? Quid d'un peu de normalisation dans tout ça ?

Exactement ce qui est écrit dans le texte que tu cites :

Normalized structure and a lot of joins is right way to design your database as textbooks teach you... but when dealing with large data sets it could be recepie to disaster.
[...]
Do not take me as going against normalization or joins. It is great principle and should be used when possible.

ce qui n'est pas une raison pour dénormaliser à outrance..

Tu fais des comparaisons dans tes wheres, tu provoque un scan complet de la table, et donc, de tout ce qu'il contient. Si tu ne peux pas limiter ce contenu (en utilisant par exemple des simples colonnes char ou varchar plutôt que texte pour un simple 'oui'), il n'y a rien faire, sinon se prendre un mur de face.

Ne confondons pas les deux problèmes :
1) la dénormalisation
2) le fait d'utiliser des text au lieu des varchar

Sur le point n°1 : dans des tables de taille importante utilisées pour de la consultation, moins il y a de jointures, plus c'est rapide. Au contraire, pour ce genre d'utilisation (et je ne parle que de ça), il faut dénormaliser à outrance.

Sur le point n° 2 : à mon avis, il faut faire des tests pour savoir si un where sur un varchar est plus rapide qu'un where sur un text. Je pencherais évidemment pour une meilleure vitesse avec un varchar, mais cela demande confirmation.
Ou alors, comme je l'indiquais plus haut, avoir une colonne code (numérique par exemple) sur laquelle portera la clause where et une colonne libellé qui contiendra le "oui" à afficher.

Il suffit de faire des jointures intelligemment, et de les faire dans un bon ordre. Si il a un champ ou il n'y a jamais que 4 valeurs différentes (oui,non, bof, demain), on aurait tout à y gagner à utiliser un indice numérique pour lequel on utilisera une jointure pour récupérer sa valeur texte.

Si il a un champ ou il n'y a jamais que 4 valeurs différentes (oui,non, bof, demain), on aurait tout à y gagner à utiliser un indice numérique pour lequel on utilisera une jointure pour récupérer sa valeur texte.

@Berzemus
Oui et mille fois oui dans le cas d'une base de données "normale".

Mais là on se place dans le cas de base de données avec des dizaines de millions de lignes utilisée en consultation. On est dans des cas qui sont (ou qui se rapprochent) des techniques de datawarehouse. Et comme je le répète, dans ce genre de système, on évite les jointures et on fait péter les règles sur la normalisation : ce n'est pas grave, on sait qu'on fait quelque chose qui n'est pas dans les standards et on le fait de manière consciente et contrôlée.

Exemple : On va créer une table de consultation (script très simplifié) des abonnés du téléphone Ce script va tourner une fois tous les week-end. Les jointures ne seront faites qu'une seule fois. Peu importe que cela prenne 1 heure.
Après on met des index sur codepostal et codedepartement
Au final, on a une table consulation (nom, prenom, telephone, codepostal, ville, codedepartement, departement)

C'est cette table qui est utilisée tous les jours par les milliers d'utilisateurs. Et tu vas avoir des requêtes : qui seront beaucoup plus rapides que d'exécuter plusieurs milliers de fois par jour Encore une fois, entendons-nous bien : il ne s'agit pas de mettre ce genre de structure en place pour de mises à jour, des insert, ... mais uniquement pour des consultations et en plus, pas sur des tables qui sont mises à jour en temps réel, mais sur des tables de consultation qui sont mises à jour tous les jours, toutes les semaines, toutes les années,...

Et d'un autre côté, on aura les tables remplies tous les jours chaque fois que quelqu'un déménage ou se fait poser le téléphone. Celles-là sont bien sûr parfaitement normalisées.

Pour la consultation, on dénormalise, on embarque les libellés avec les codes : ce n'est pas ce qui est écrit dans les manuels SQL, c'est vrai. Mais du moment que l'on sait que l'on n'est plus dans les clous, que l'on sait pourquoi, qu'on justifie pourquoi et que tout ça est bien documenté, il n'y a pas de problème.

Parce qu'à un moment, il faut bien répondre au besoin de l'utilisateur : il est hors de question que l'on dise à l'utilisateur "vous allez attendre 20 secondes parce que les règles SQL disent qu'il faut normaliser". Il y a un moment où il faut être pragmatique. Comme le disait un de mes premiers responsable "Appliquer des règles sans réfléchir est aussi dangereux que de ne pas appliquer de règles" ou "L'excès de méthode est aussi dangereux que l'absence de méthode".

Donc pense ce que tu veux, trouve cela horrible, impie, hérétique, ... mais la dénormalisation est une méthode couramment employée pour les consultations des grosses bases de données.

Je suis surpris que personne n'aie encore donné ce lien qui répertorie les différents moteurs de stockage MySQL existants et leurs cas d'utilisation typiques : http://sony-noel.developpez.com/tutoria ... l/moteurs/

Avant de coder un nouveau moteur de stockage, cela vaut sans doute le coup de s'attarder sur tout ce qui existe et de faire quelques tests.

Le moteur memory par exemple pourrait peut-être être intéressant à exploiter pour ce besoin extrême de perfs : http://sony-noel.developpez.com/tutoria ... ?page=heap

Le moteur memory par exemple pourrait peut-être être intéressant à exploiter pour ce besoin extrême de perfs

Je pense que cela a été fait puisque pierreC dit :

ensuite si vraiment il faut j'ai suffisament de RAM sur le serveur et je met la table en memoire, cela gagne fait tomber le temps d'execution à 12 sec

Non, je refuse. Je tiens ma position qu'une DB bien indexée avec des jointures sera bien plus rapide que cette grosse table.

Pour quelques raisons:
- la taille. Un gros bazar dénormalisé, avec quelques millions de champs "texte", prend bien plus de taille qu'une table un peu plus normalisée, qui n'utilise qu'une dizaine de ses champs. Et donc, on y gagne en espace, et ça libère la mémoire vive pour d'autres opérations plus intéressantes.
- si bien indexé, l'impact sur la performance sera tout simplement minime. Largement rattrapé par
- comparer des chiffres de petite taille va bien plus vite que de comparer un champ texte.
- l'espace libéré dans la mémoire vive servira au cache (memcache ou autre)

La dénormalisation ici est utile que pour les champs qui ne sont utilisés que pour la lecture. Mais pas pour être employés lors d'une recherche ! Je ne parle pas de normaliser le bazar à outrance, mais uniquement les champs qui sont utilisés lors d'une recherche.

Car alors, et si avec toute la mémoire vive du monde on ne peut stocker le tout, les accès aléatoires avec les index et les jointures seront bien moins rapides qu'un full scan, or c'est ce qu'on veut éviter. Donc, plus de ram (il me semble qu'InnoDB utilise plus la ram), et normaliser les champs importants.

Mais de toute façon, le plus important ce n'est pas la normalisation, on étudie ça après avoir fait tout le reste (et le cache est la pour s'en occuper). Et je me rends enfin compte que les indexes utilisés sont tout sauf bons. Pourquoi ?
Notre ami pierreC utilise un index pour chaque champ. Or, dans sa requête, il n'y a qu'un index qui est utilisé dans les deux AND (de part et d'autre du OR). C'est pour ça que MySQL utilise un full scan pour les deux autres champs, et n'utilise qu'un index sur trois, cqfd.

La solution serait d'utiliser un index multi-colonnes, qui aurait même peut-être l'avantage d'augmenter la cardinalité de l'index, et donc son efficacité: Ce qui serait encore accéléré par l'emploi de chiffres au lieu de lettres, mais bon, c'est un autre débat.

Peut-être que je ce WE j'aurais de quoi faire un benchmark, mais bon, l'envie ne me tue pas.

eh bien tu es tetu Berzemus

caroube à pourtant très bien résumé la problématique dans son exposé

alors pour faire avancé le débat je vais reprendre tes idées, et dire ce qui ne me convient pas :

et ça libère la mémoire vive pour d'autres opérations plus intéressantes.

j'ai un Bi-quad xeon avec 12Gà de ram pour maxi 8 requetes simultanés, on va dire que y'a pas de problème de ce coté là.

- si bien indexé, l'impact sur la performance sera tout simplement minime. Largement rattrapé par
- comparer des chiffres de petite taille va bien plus vite que de comparer un champ texte.

c'est pour cela que caroube propose de doublé les colonnes texte en créant un equivalent de type numérique et sans jointure. Sachant que le client ne voudra pas ecrire des requetes de type "civilite=3" mais toujours "civilite='Mlle'"
De mon coté et vu ton insistance sur ce sujet je vais demander à mon client d'etre plus precis sur les types et tailles des champs.

Je ne parle pas de normaliser le bazar à outrance, mais uniquement les champs qui sont utilisés lors d'une recherche.

TOUTES les colonnes peuvent etre utilisées pour effectuer des recherches. et d'un mois à l'autre le nombre de colonne peut varier. Si je part sur une solution de table avec jointure cela signifierai donc que le nombre de table et de jointure peuvent varier également (ca sent le gaz de l'usine là). Je trouve que l'idée de caroube est pas mal de douvber les colonnes text avec leur equivalent numérique, et à moi de transformer en php un civilite='Mlle' en civilite_num=3 avant l'execution,

concernant le reste de ta réponse (et j'en ai déjà parlé), je n'ai aucune maitrise des requêtes qui peuvent etre exécuter.
Il m'est donc impossible de pré-créer ce genre de chose :

PRIMARY KEY (`id_client`),
KEY `ind1` (`champ1`, `champ2`),
KEY `ind2` (`champ1`, `champ3`)

à moins de créer tout les cas possible, sur 200 colonnes je pense pas que ce soit une bonne idée

Peut-être que je ce WE j'aurais de quoi faire un benchmark, mais bon, l'envie ne me tue pas.

J'ai bien compris tes idées et ton raisonnement Berzemus, caroube l'a aussi compris, mais de ton coté tu prends pas en comptes les contraintes de mon projet (pas de vison des requêtes, pas de vision de l'évolution de la table chaque mois) et ses atouts (lecture seul, pas de problème d'intégrité, serveur puissant) qui font qu'il sort d'un cadre traditionnel.

sinon concernant l'ecriture d'une solution maison (pas un moteur mysql finalement, mais un binaire bien optimisé ecrit en C, qui charge tout en mémoire lui-meme) les tests sont pour l'instant tres concluant, mais pas de conclusion attive, on a testé qu'une requete ecrite en dur

hum.. bon.. C'est le client qui fait le difficile, à mon avis..

Ben si vraiment il n'y a plus rien à faire côté structure (mais je ne m'incline pas! Jamais!), il y'a quand même d'autres choses a considérer que de réécrire un moteur, non ?

Prévoir du cache à outrance, répartir la charge sur plusieurs serveurs, utiliser un autre sdbmgr..

Pourquoi pas Drizzle tiens, ça ressemble à du Mysql, c'en est même un fork avec quelques inutilités en moins pour le rendre plus rapide, avec l'avantage d'être optimisé pour le multi-core, et prévu pour le web (donc, il aime bien les lectures).. Ou d'autres alternatives..

Si vraiment tu ne peux pas avoir le contrôle de ce qui y rentre et de ce qu'on en demande (même pas par un module intermédiaire), et que pour seul solution tu ne peux mettre que de la puissance brute.. quel gâchis.

En gros on te demande de concevoir un nouveau sgbd.. c'est pas le bon forum alors

En gros on te demande de concevoir un nouveau sgbd.. c'est pas le bon forum alors

A moins que tu ne veuilles le faire en PHP

Normaliser certain champ serait interressant.
Je suis d'ailleurs étonné que, par exemple, pour un champ comme civilité vous n'utilisiez pas un type ENUM qui permet de faire une requete sur 'Mlle' par exemple tout en prenant un minimum de place et permettant l'optimisation de l'indexation de ce champ.
Il faudra penser également à optimiser les requetes si des fonctions SQL sont utilisées, il me semble que la position de la fonction à droite ou a gauche de l'opérateur influence sur l'utilisation des index.
Pensez également a doubler des champs en inversant la chaine de caractère si certaine requetes utilise des like pour optimiser les performance par exemple :
recherche sur un email par sur un domaine :
where email like ' %@mondomaine.com'
sera moins performant que :
where email like 'moc.eniamodnom@%'
car dans le premier cas aucun index ne sera utilisé.

L'utilisation de type de colonne fixe sera également meilleur CHAR plutot que VARCHAR,...

Faudrais déjà optimiser la requête