Discussion :

[kasey]

Bonjour.

Je suis actuellement en train de coder un site web embarquant en interne un moteur de recherche. J'indexe actuellement une base de donnée de document possédants plusieurs champs indexés (titre, tags, description, texte contenu).

Voici le schema de mes tables :

Table documents :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
CREATE TABLE `documents` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `nom` varchar(64) default NULL,
  `titre` varchar(128) default NULL,
  `tags` varchar(256) default NULL,
  `contenu` longtext,
  `texte` longtext,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Table mots :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE TABLE `mots` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `mot` varchar(32) default NULL,
  PRIMARY KEY  (`id`),
  KEY `mots_idx` (`mot`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Et quatre tables (titre tags description et texte ou docs) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE TABLE `index_docs` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `id_documents` int(11) unsigned default NULL,
  `id_mots` int(11) unsigned default NULL,
  `count` int(11) unsigned default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Actuellement j'ai une requête qui me permet de récupérer les documents contenant l'occurrence d'un ou plusieurs mots comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT documents.nom,SUM(index_docs.count) AS count
FROM documents, index_docs, mots
WHERE
    mots.id = index_docs.id_mots
    AND documents.id = index_docs.id_documents
    AND (mots.mot = "TOTO" OR mots.mot = "TATA")
GROUP BY documents.nom ORDER BY count DESC ;

Celle-ci fonctionne parfaitement et me retourne la liste des documents classé par nombre d'occurrences.

Maintenant j'aimerais bien faire la même chose mais avec les 4 tables (titre tags description et texte).

Cependant je ne m'en sors pas… Je ne sais pas trop comment construire ma requête.

J'ai essayé quelque chose comme :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
    documents.nom,
    SUM(index_docs.count) AS count_docs,
    SUM(index_resumes.count) AS count_resumes,
    SUM(index_tags.count) AS count_tags,
    SUM(index_titles.count) AS count_titles 
FROM
    documents,
    index_docs,
    index_resumes,
    index_tags,
    index_titles,
    mots 
WHERE
       ((mots.id = index_docs.id_mots    AND documents.id = index_docs.id_documents)
    OR  (mots.id = index_resumes.id_mots AND documents.id = index_resumes.id_documents)
    OR  (mots.id = index_tags.id_mots    AND documents.id = index_tags.id_documents)
    OR  (mots.id = index_titles.id_mots  AND documents.id = index_titles.id_documents))
    AND (
        mots.mot = "APOSTROPHE"
        ) GROUP BY documents.nom ;

Mais c'est un échec … des idées ? des critiques des conseils? Je suis un grand débutant en SQL alors aidez moi a progresser : critiquez moi ^^

[CinePhil]

Si tu utilisais la syntaxe normalisée depuis 1992 pour les jointures (ça fait quand même 17 ans, il serait temps de s'y mettre ! ), ta requête serait plus lisible et plus facile à écrire.

Rappel de la structure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT colonnes
FROM table1
JOIN table2 ON condition de jointure
WHERE condition de restriction
GROUP BY colonnes de regroupement
HAVING condition de restriction sur le résultat du regroupement
ORDER BY colonnes de tri

Si je comprends bien ta requête et la structure de tes tables, les tables Mots et Documents sont toutes deux en relation avec les 4 tables d'index.

Dès lors, si tu veux présenter toutes les jointures, il te faut plusieurs instances d'une partie des tables (plusieurs possibilités, en voici une :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
    documents.nom,
    SUM(index_docs.count) AS count_docs,
    SUM(index_resumes.count) AS count_resumes,
    SUM(index_tags.count) AS count_tags,
    SUM(index_titles.count) AS count_titles 
FROM documents AS d
LEFT JOIN index_docs AS ido ON d.id = ido.id_documents
LEFT JOIN index_resumes AS ir ON d.id = ir.id_documents
LEFT JOIN index_tags AS ita ON d.id = ita.id_documents
LEFT JOIN index_titles AS iti ON d.id = iti.id_documents
  RIGHT JOIN mots AS m ON iti.id_mots = m.id
    LEFT JOIN index_tags AS ita2 ON ita2.id_mots = m.id
    LEFT JOIN index_resumes AS ir2 ON ir2.id_mots = m.id
    LEFT JOIN index_docs AS ido2 ON ido2.id_mots = m.id
WHERE mots.mot = 'APOSTROPHE'
GROUP BY documents.nom ;

A essayer ou alors je n'ai rien compris.

[pacmann]

Salut !

@Cinephil :
Je n'ai pas très bien compris pourquoi tu dupliques les jointures...
Attention au passage à ne pas casser la jointure dans le where :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

WHERE mots.m is null or mot.m = '...'

Attention également à utiliser COALESCE avec les sum (si je me trompe pas, lorsque tout est null, la sum aussi)

Kasey :
Avec la syntaxe normalisée des jointures, non seulement ça aurait été plus lisible, mais surtout tu n'aurais pas réussi à faire la même erreur
Car le fait de mettre tes différentes condition de jointure avec des OR ne te permettra jamais de "choisir" une des tables... mais va juste te faire des produits cartésiens :
Car soit tu considères ton résultat comme une suite de jointures, où pour chaque jointure tu définis des critères, soit tu considères l'ensemble des combinaisons possibles entre les lignes de tes 4 tables, et tu appliques ton WHERE : les conditions OR vont laisser passer beaucoup de choses...

Sinon, tu peux aussi prendre ta requête qui marche, la refaire 4 fois, et coller tout ça ensemble avec des UNION ALL

[Yanika_bzh]

peut etre pouvez vous encore changer votre modele de données et ainsi ne faire qu'une table la ou vous en aviez quatre avec une colonne de typage (ce n'est qu'une idée).
Cela pourrait permettre de faciliter l'ecriture de vos requetes.

Attention a ne pas prendre aussi des mots reservés dans la définition de vos tables (count par exemple) cela pourrait vous poser des problemes.



Bon courage

[kasey]

Si tu utilisais la syntaxe normalisée depuis 1992 pour les jointures (ça fait quand même 17 ans, il serait temps de s'y mettre ! ), ta requête serait plus lisible et plus facile à écrire.

Rappel de la structure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT colonnes
FROM table1
JOIN table2 ON condition de jointure
WHERE condition de restriction
GROUP BY colonnes de regroupement
HAVING condition de restriction sur le résultat du regroupement
ORDER BY colonnes de tri

Merci pour al syntaxe je vais tacher de jouer un peu avec elle pour me familiariser et je tacherais de l'employer a l'avenir. Cela dit on m'as dit d'utiliser la syntaxe telle que je l'emplois et de réserver les JOIn pour les cas non triviaux. L'impact sur les performance est-il important entre les deux méthodes?

Si je comprends bien ta requête et la structure de tes tables, les tables Mots et Documents sont toutes deux en relation avec les 4 tables d'index.

Dès lors, si tu veux présenter toutes les jointures, il te faut plusieurs instances d'une partie des tables (plusieurs possibilités, en voici une :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
    documents.nom,
    SUM(index_docs.count) AS count_docs,
    SUM(index_resumes.count) AS count_resumes,
    SUM(index_tags.count) AS count_tags,
    SUM(index_titles.count) AS count_titles 
FROM documents AS d
LEFT JOIN index_docs AS ido ON d.id = ido.id_documents
LEFT JOIN index_resumes AS ir ON d.id = ir.id_documents
LEFT JOIN index_tags AS ita ON d.id = ita.id_documents
LEFT JOIN index_titles AS iti ON d.id = iti.id_documents
  RIGHT JOIN mots AS m ON iti.id_mots = m.id
    LEFT JOIN index_tags AS ita2 ON ita2.id_mots = m.id
    LEFT JOIN index_resumes AS ir2 ON ir2.id_mots = m.id
    LEFT JOIN index_docs AS ido2 ON ido2.id_mots = m.id
WHERE mots.mot = 'APOSTROPHE'
GROUP BY documents.nom ;

A essayer ou alors je n'ai rien compris.

Je ne comprend pas très bien la requête telle que tu me la ré-écrite mais le serveur sql me retourne la réponse suivante a l'exécution : Unknow colomn 'document.nom' in 'field list'

@Kasey :
Avec la syntaxe normalisée des jointures, non seulement ça aurait été plus lisible, mais surtout tu n'aurais pas réussi à faire la même erreur
Car le fait de mettre tes différentes condition de jointure avec des OR ne te permettra jamais de "choisir" une des tables... mais va juste te faire des produits cartésiens :
Car soit tu considères ton résultat comme une suite de jointures, où pour chaque jointure tu définis des critères, soit tu considères l'ensemble des combinaisons possibles entre les lignes de tes 4 tables, et tu appliques ton WHERE : les conditions OR vont laisser passer beaucoup de choses...

Oui en effet en triturant ma requête j'ai remarqué que le nombre d'occurrence pouvait parfois énorme alors que le nombre d'entrées est tout petit.

Sinon, tu peux aussi prendre ta requête qui marche, la refaire 4 fois, et coller tout ça ensemble avec des UNION ALL

Hum je vais essayé ceci mais je suis soucieux de rendre ma requête la plus performante possible au vue du grand nombre de documents indéxés dans le futur.

peut etre pouvez vous encore changer votre modele de données et ainsi ne faire qu'une table la ou vous en aviez quatre avec une colonne de typage (ce n'est qu'une idée).
Cela pourrait permettre de faciliter l'ecriture de vos requetes.

En effet je n'y avais pas penser. C'est une excellente idée

Attention a ne pas prendre aussi des mots reservés dans la définition de vos tables (count par exemple) cela pourrait vous poser des problemes.

Je ne savais pas que count était un mot réservé :-/ Je vais changer le nom.

Bon courage

Merci bien c'est gentil ^^

[CinePhil]

Merci pour la syntaxe je vais tâcher de jouer un peu avec elle pour me familiariser et je tâcherais de l'employer à l'avenir.

Ca c'est une bonne résolution !

Cela dit on m'a dit d'utiliser la syntaxe telle que je l'emploie et de réserver les JOIN pour les cas non triviaux.

Ca c'est un mauvais conseil et celui qui te dit ça devrait lui aussi se mettre à la page !

L'impact sur les performances est-il important entre les deux méthodes ?

Aucun impact en principe. Mais quel gain en lisibilité et en pistage d'erreurs !

Je ne comprends pas très bien la requête telle que tu me l'a ré-écrite mais le serveur sql me retourne la réponse suivante à l'exécution : Unknown column 'document.nom' in 'field list'

Ce qui est bizarre, c'est que nulle part, dans la requête que j'ai postée, ne figure 'document.nom'. Il n'y a que 'documents.nom' (documents au pluriel). C'est donc peut-être toi qui a fait une erreur de recopie ?

Ceci dit, revoir ton modèle est une excellente suggestion !

Bon courage.

[kasey]

Ce qui est bizarre, c'est que nulle part, dans la requête que j'ai postée, ne figure 'document.nom'. Il n'y a que 'documents.nom' (documents au pluriel). C'est donc peut-être toi qui a fait une erreur de recopie ?

Heu oui j'ai fait une faute en recopiant le code d'erreur (merci pour les corrections grammaticales )

Ceci dit, revoir ton modèle est une excellente suggestion !

Bon courage.

Hum oui je pense que c'est ce que je vais faire.

[kasey]

Heop la reponse finale a mon probleme :-) J'ai fusionné les tables d'index et rajouté une table coefficient afin de classer de manière plus pertinente les résultats :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SELECT documents.nom,documents.titre,documents.resume,SUM(index_docs.count * coef.value) AS rank
FROM documents
    JOIN index_docs
        ON documents.id = index_docs.id_documents
    JOIN coef
        ON index_docs.type = coef.id
    JOIN mots
        ON index_docs.id_mots = mots.id
        WHERE (mots.mot = "TOTO" OR mots.mot = "TATA")
GROUP BY documents.nom
ORDER BY rank DESC, documents.titre ASC ;

Petite référence aux cours de SQLPro et la notion d'arbre de jointure ça m'a pas mal aidé.