[SQL] Conception intelligente

Dev
1

yo! les geeks,

Pour les pros du SQL, j’ai quelques questions. Je bosse sur un gros site qui contiendra une base de données d’articles, de revues, et plein d’autres choses. Ce site contiendra un moteur de recherche que je voudrais le plus optimum et optimisé possible.
Mes question tournent autour de la création/conception des tables. Voilà, typiquement, ce que j’ai fait pour les articles, mais que l’on retrouve partout :
CREATE TABLE hc_articles
(
 ID INT(1) AUTO_INCREMENT PRIMARY KEY,
 numero INT(1),
 titre VARCHAR(255),
 page INT(1),
 texte INT(1),
 article_image VARCHAR(255)
); 
Ma question particulière tourne autour des index. Comme pour les articles, chaque table est conçu avec un index auto-incrémenter (ID INT(1) AUTO_INCREMENT PRIMARY KEY). Pour ce que j’en ai lu dans la doc de MySQL c’est ce qu’il faut faire, notamment pour les recherches qui utilisent cet index pour éviter les parcours séquentiel et construire du B-Tree. Donc, vive les fleurs et joie. Ca fonctionne aussi avec l’AUTO_INCREMENT ?

Et d’un point de vue général quelles sont les choses auxquelles il faut penser, les pièges à éviter, les astuces ultimes qui font que la base est bien conçue et que les requêtes (SELECT en particulier) vont tout bien super vite ?

Merci pour les infos.
Antoine

2

Il te faut des index sur ce que tu vas utiliser pour selectionner (genre le primary id) mais aussi si tu veux les trier par date, un index sur la date va aider. En mysql je sais pas si ca existe (je suis a peu pres sur que non mais bon on sait jamais j’ai pas regarde depuis un moment) mais en mssql tu peux lancer un “profiler”, faire tourner ton site et il va te dire exactement ou il a passe son temps et te conseiller des index a rajouter. T’es pas oblige de l’ecouter sur tout et il rate certains trucs dans les requetes complexes mais pour un cas simple comme ca, ca permet de pas en oblier des evidents.

En gros des que t’as une requete qui est faite super souvent, il est bon d’avoir les clauses les moins restrictives (si t’en a plusieurs avec un AND) dans un index (ben oui une fois qu’il a reduit le jeu des possible a 10 avec la premiere clause ton index il va pas etre super utile pour trier ces 10 restants).

Enfin il suffit d’etre logique, pour une table en dessous de quelques dizaines de millier d’entree te fais pas de soucis de toute facon… si tu fais pas ca comme un porc et que tu fais les trucs les plus evidents ca va tracer. Ca se joue beaucoup plus sur le design des requetes et de l’appli. (pour faire le moins de connec/requetes possibles). Apres si t’attaque les 1 000 000 d’entrees la, on commence a vraiment parler optimisation du schema. Mais je pense pas que tu en sois la avec un schema qui recense des articles

3

[quote]En gros des que t’as une requete qui est faite super souvent, il est bon d’avoir les clauses les moins restrictives (si t’en a plusieurs avec un AND) dans un index (ben oui une fois qu’il a reduit le jeu des possible a 10 avec la premiere clause ton index il va pas etre super utile pour trier ces 10 restants).

Enfin il suffit d’etre logique, pour une table en dessous de quelques dizaines de millier d’entree te fais pas de soucis de toute facon… si tu fais pas ca comme un porc et que tu fais les trucs les plus evidents ca va tracer. Ca se joue beaucoup plus sur le design des requetes et de l’appli. (pour faire le moins de connec/requetes possibles). Apres si t’attaque les 1 000 000 d’entrees la, on commence a vraiment parler optimisation du schema. Mais je pense pas que tu en sois la avec un schema qui recense des articles
En gros, je tourne à moins de 20000 articles de presse, et ce, pour un bon bout de temps (y a bon les mensuels ). En plus de ça, j’ai déjà conçu tout mon code pour limiter au maximum le nombre de requêtes. En revanche certaines requêtes vont être bourrées de AND, genre :
SELECT article FROM hc_article_cat_links WHERE cat=10 AND cat=23 AND cat=18 AND … etc.
Suivi de SELECT * FROM hc_articles WHERE ID=…
Requête typique du moteur de recherche : trouve moi les articles qui parlent de ça, de ci, de cela mais aussi de truc.
Je compte beaucoup aussi sur le cache. Etant donné que le poids des informations sera assez limité, je vais étudier la possibilité de TOUT mettre en RAM (qui a passé par un RAM drive ?), aussi bien le site (les sites en fait, ça sera un portail) que la base SQL. J’activerai le cache de requête SQL pour que tout soit caché : les requêtes elles-mêmes et leurs résultats.
J’angoisse un peu parce que la mise en ligne est dans trois mois et il y a de fortes chances pour que les sites soient très, très, très beaucoup fréquentés. Je préfère tout blinder dès maintenant.
Le tout sur une bécane bien armée, j’espère que ça tiendra la route comme il faut.

Antoine

4

[quote]SELECT article FROM hc_article_cat_links WHERE cat=10 AND cat=23 AND cat=18 AND … etc.[/quote]Euh, à mon avis, ça, ça va pas ramener grand chose. Tu veux sans doute dire OR.
Et les OR, la vache, ça coûte cher. Souvent, l’opérateur IN est beacoup plus rapide (cat IN {10,23,18}). On peut aussi faire des UNION à la place de OR, c’est moins cher aussi.

5

Mets des index sur les champs qui seront le plus souvent appelées en critère ou en jointure.

Attention il faut que ces colonnes aient des valeurs discriminantes sinon cela ne sert pas à grand chose.

Tu peux aussi utiliser des index multi-champs dans le cas où ces champs seront utilisés en critère ou en jointure. Le fait d’utiliser plus d’un champ permet d’augmenter l’aspect discriminant de ton index si tant est que les n champs de l’index soient appelés en même temps dans la requête.

Après tu peux spécifier le type d’index (en tout cas sous Oracle) selon tes besoins.

6

Fallait pas dormir pendant tes cours de merise/sql/db…

7

C0unt0 > D’abord c’était pas des cours de Merise, mais de l’ACSI (Analyse et Conception de Systèmes d’Informations) et puis je dormais pas… j’y allais pas !
(bon, bon ok… C’est quoi “discriminant” ?

boudin > nanan, c’est bien du AND et non pas du OR. La table hc_article_cat_links fait le lien entre les articles et les catégories. Un article peut faire partie de plusieurs catégories et la recherche se fait en multi-critères. Par exemple, je veux tous les articles qui sont dans la catégorie “20e siècle” et dans la catégorie “Moto”.

Plus généralement : il faut me parler en termes simples. Je me suis auto-formé à SQL, essentiellement à l’empirique. Donc  je ne connais pas forcément tous les termes associés, ou bien ils ont un sens un peu trop abstrait pour moi.
Par exemple, “Mets des index sur les champs qui seront le plus souvent appelées en critère ou en jointure”, ça m’échappe un peu. Pour moi, un index c’est juste un identifiant et on indique à SQL par PRIMARY_KEY que c’est un identifiant
Alors “index sur les champs”… heu… Pour moi un champ c’est un élément descriptif d’une table (le nom d’une colonne koa).
Mébon, aux vues de ce que m’a dit Glop, j’ai pas trop de quoi m’inquiéter : la quantité d’infos que je traite n’est pas suffisamment effrayante

Antoine

8

[quote]phili_b > J’ai rien compris, mais c’est gentil de vouloir m’aider C’est quoi “discriminant” ?

boudin > nanan, c’est bien du AND et non pas du OR. La table hc_article_cat_links fait le lien entre les articles et les catégories. Un article peut faire partie de plusieurs catégories et la recherche se fait en multi-critères. Par exemple, je veux tous les articles qui sont dans la catégorie “20e siècle” et dans la catégorie “Moto”.
[…]
Pour moi, un index c’est juste un identifiant et on indique à SQL par PRIMARY_KEY que c’est un identifiant [/quote]
Un discriminant c’est ce qui diffèrencie.
Mais il faut aussi le voir dans le contexte dans lequel on l’utilise. Par exemple “Homme” n’est pas discriminant dans le métier d’informaticien, comme “Femme” n’est pas discriminant dans le métier de sage-femme. Un numéro de téléphone est par contre un bon candidat pour être un index…sauf pour le numéro de téléphone d’un standard téléphonique d’une entreprise.  Pour continuer dans les exemples “lieux communs”, “Homme” avec “voiture rose bonbon” est un bon discriminant même chez les informaticiens. (index composé de plusieurs champs).

Attention une clé primaire est effectivement aussi un index (mais un index unique (=avec valeur unique  )  et n’acceptant pas de valeur nulle  )  mais un index n’est pas forcément une clé primaire.
Une clé primaire est souvent un champ incrementé et genéré, plutôt qu’un champ avec signification “venant de la vie réelle”. Une table ne possède qu’une seule clé primaire.

C’est toi qui te trompes. C’est un OR qu’il te faut. Sur une ligne un champ est égal à une valeur et pas à une autre en même temps. Select * from TOTO where ColonneA=1 and colonneA=2 ne donnera jamais de résultat, contrairement à Select * from TOTO where colonneA=1 or colonne A=2. Par exemple je cherche tout les véhicules peugeot et tout les véhicules suzuki, mais je ne trouverais pas de véhicules à la fois suzuki et peugeot.

Par rapport à ton exemple “20é siècle” et “moto” c’est qu’il y a un souci dans ton modèle de donnée. Il te faudra à ce moment là 2 champs diffèrent: “époque” et “type de véhicule”.

(Plusieurs édits pour rajouter des exemples et des précisions. 15h24)

Ce message a été édité par phili_b le 15/02/2004

9

Aaaaaaaaaargh, oui, non, peut-être… Chuis à la ramasse la plus totale moi…

Antoine
(honteux)

Ce message a été édité par AntoineViau le 15/02/2004
Ce message a été édité par AntoineViau le 15/02/2004

10
AntoineViau a dit: Edit 2 :  je crois que j'ai une piste...

Pour sélectionner les articles sur trois critères :
SELECT t1.article_id FROM links t1,links t2,links t3 WHERE t1.categorie_id=1 AND t2.categorie_id=4 AND t3.categorie_id=6 AND t1.article_id=t2.article_id AND t1.article_id=t3.article_id;
J’ignore à quel point c’est pouerk

Mon dieu!  Pourquoi faire simple quand on peut faire compliqué ?!

Si j’ai bien compris, pour contourner le problème d’avoir mis toutes les catégories dans le même champ, tu as plusieurs enregistrements (=lignes) pour un même véhicule pour exprimer les diverses catégories ?

article_id categorie_id libelle_catégorie 1   1 suzuki 1 4 moto 1 6 20é siècle
L'identifiant 1 étant en fait la même moto ? (aaaargl!) Alors qu'en fait il faudrait 3 champs diffèrents (marque, type véhicule,époque).

Mais bon fermons temporairement les yeux là-dessus (gnnn difficile), la solution serait en fait la suivante

SELECT article_id FROM LINKS WHERE categorie_id IN (1,4,6) GROUP BY article_id

Quand même plus simple non ?

Ce message a été édité par phili_b le 15/02/2004

11

[quote]Quand même plus simple non ? .
Pas question, donc, de déclarer des champs dans la définition de la table des articles (un truc du genre TABLE article(ID,titre,marque,période,type)).
L’objectif final est d’offrir à l’utilisateur un moyen de se “balader” dans les catégories pour rechercher très précisément les articles qu’il recherche.
Pour que ce soit clair pour l’utilisateur, les catégories sont regroupées dans des groupes au sein desquelles elles sont exclusives, voire nulles. Ainsi, dans le groupe Marque qui contient Honda et Suzuki, il ne pourra choisir que Honda OU Suzuki OU aucun des deux.

Un exemple bête et méchant (un de plus) limité à deux groupes, deux catégories par groupe et trois articles : 
Catégories : 
 - 1:Voiture (groupe “type”)
 - 2:Moto (groupe "type)
 - 3:Honda (groupe “marque”)
 - 4:Suzuki (groupe “marque”)

Articles :
 - 1:“Motos Honda” qui appartient aux catégories Moto et Honda
 - 2:“Voiture Honda” -> Voiture et Honda
 - 3:“Moto Suzuki” -> Moto et Suzuki

Maintenant, supposons que l’utilisateur veuille avoir tous les articles qui parlent de moto de la marque Honda. Il va sélectionner dans le groupe Marque la catégorie Honda et dans le groupe Type la catégorie Moto.
S’il veut toutes les motos, il ne sélectionne aucune marque et choisis Moto dans le groupe Type.
Voilà, ça c’est pour expliquer ce que je dois avoir au final.

Maintenant, mon modèle :
TABLE articles(ID,titre)
TABLE categories(ID,nom)
Comme je ne sais pas à combien de catégories un article appartiendra, je crée une table de liens :
TABLE links(article_id,categorie_id)
Ce qui nous donne dans notre exemple :
links : (1,2) (1,3) (2,1) (2,3) (3,2) (3,4)
Maintenant, pour avoir tous les articles sur les motos Honda, il faut qu’ils fassent partie de la catégorie 3 (Honda) ET de la catégorie 2 (Moto).

Si je reprends ta requête (avec le IN) j’aurai les articles qui parlent de motos et qui parlent de Honda… donc les motos Suzuki et les voitures Honda !

J’en reviens donc à ma requête qui fait 10 km de long dont le principe est de faire des filtres sur des résultats de filtrage sur une seule et même table.
Maintenant, y a ptet (surement !) plus propre aussi bien au niveau de la requête (je pense notamment au JOIN) que du modèle lui-même.

Le débat est ouvert

Antoine

Edit : quelques précisions par-ci, par-là.

Ce message a été édité par AntoineViau le 15/02/2004

12

ok ok. Tout est plus clair. Ta requête était alambiquée mais ton modèle à l’air d’être bon.

Ta table links est une table n-n de lien. Avec un nom pareil j’aurais du m’en douter.

Cheminement de ma réponse:

(doc IF:  MySQL: Les fonctions de contrôle)

1. On cherche les articles en catégorie 2 et 3. Pour cela on mets un 1 à la place de chaque catégorie cherchée et un zéro sinon.

SELECT links.article_id, links.categorie_id, If(categorie_id=2,1,0) AS catego_1, If(categorie_id=3,1,0) AS catego_2 FROM links GROUP BY links.article_id, links.categorie_id;

article_id categorie_id catego_1 catego_2
1 2 1   0
1 3 0 1
2 1 0 0
2 3 0 1
3 2 1 0
3 4 0 0[/quote]2.Donc on peut faire la somme des catégories exactes

SELECT links.article_id, Sum(If(categorie_id=2,1,0)+If(categorie_id=3,1,0)) AS Nb_catego_ok FROM links GROUP BY links.article_id;

article_id  Nb_catego_ok
1 2 
2 1
3 1
[/quote]3.Réponse (2 façons d’écrire)

Voilà la requête que je te propose. Comme on a 2 critères, il faut que le nombre de critères exactes soit égale à 2.

SELECT links.article_id FROM links GROUP BY links.article_id HAVING (((Sum(If(categorie_id=2,1,0)+If(categorie_id=3,1,0)))=2));[/quote]que l'on peut écrire aussi:
SELECT links.article_id FROM links GROUP BY links.article_id HAVING (((Sum(If(categorie_id In (2,3),1,0)))=2));[/quote]  Ces 2 requêtes vont nous sortir l'article 1 et pas les autres.

Ce message a été édité par phili_b le 16/02/2004

13

Wow… là je suis impressionné. Super bien trouvé, top-élégant, top-classe

Chapeau bas…

Antoine

Edit : correction sur la vitesse.
Ce message a été édité par AntoineViau le 16/02/2004