Optimisation Schéma / Requete MySQL

Rebonjour, c'est encore moi :mouarf:

J'aimerais associer des phrases (que je divise en mots) à des membres dans une base de données.

Tout d'abord j'aimerais être sure que mon schéma est correct (j'ai déjà demandé mais j'aimerais être sure que j'ai bien suivi les conseils qu'on ma donné)

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

Table mots ----------------------- mot_id mot_mot 1 je 2 suis 3 fou     Table phrases ----------------------- phrase_id mot_id mot_ordre 1 1 1 1 2 2 1 3 3 2 1 NULL       Table membres ----------------------- membre_id membre_nom 1 moi 2 toi     Table membres_phrases ----------------------- membre_id phrase_id 1 1 1 2 2 1

---

- Ce schéma vous semble t'il correct ?

- Quelle serait la requête ou les requêtes MySQL les plus efficaces pour trouver toutes les phrases associées au compte d'un membre dont l'id est 1 par exemple ?

Quelle colonne est il préférable que je mette en index, primary keys etc ?

Encore merci pour vos conseils !

Citation:

---

Envoyé par beninsky

- Ce schéma vous semble t'il correct ?

---

A première vue oui.

Citation:

---

- Quelle serait la requête ou les requêtes MySQL les plus efficaces pour trouver toutes les phrases associées au compte d'un membre dont l'id est 1 par exemple ?

---

Code:

---

1 2 3 4 5 6

SELECT mp.phrase_id, m.mot_mot FROM mots AS m INNER JOIN phrases AS p ON m.mot_id = p.mot_id INNER JOIN membres_phrases AS mp ON p.phrase_id = mp.phrase_id WHERE mp.membre_id = 1 ORDER BY mp.phrase_id, p.mot_ordre

---

Citation:

---

Quelle colonne est il préférable que je mette en index, primary keys etc ?

---

- Les clés étrangères, donc toutes celles contenant 'id'.
- Les colonnes les plus recherchées telles que 'membre_nom'.

Les clés primaires sont automatiquement indexées.

Encore une fois, merci beaucoup pour ta réponse CinePhil.

Je vais étudier ton code d'après mon exemple en utilisant la doc (je n'arrive toujours pas à trouver d'exemples suffisamment pertinents pour que je comprenne la logique des requêtes SQL et des jointures entre tables)

Citation:

---

Envoyé par beninsky

(je n'arrive toujours pas à trouver d'exemples suffisamment pertinents pour que je comprenne la logique des requêtes SQL et des jointures entre tables)

---

Tu as étudié le cours de SQLPro ?

Bonsoir,

J'ai commencé à étudier le cours de SQLPRO mais j'avoue que j'ai encore beaucoup de mal à comprendre les requêtes SELECT compliquées.

Par exemple dans mon schéma ci-dessus j'essaie de créer une requête qui sélectionnera l'id de la phrase "je suis fou" dans la table phrases, le problème c'est qu'il peut y avoir plusieurs phrases commençant par "je suis fou", comment limiter la requête à ces 3 mots précisément et dans cette ordre la ? Est-ce qu'une telle requête est possible ?

Quelqu'un pour m'aider ?

Citation:

---

une requête qui sélectionnera l'id de la phrase "je suis fou" dans la table phrases, le problème c'est qu'il peut y avoir plusieurs phrases commençant par "je suis fou", comment limiter la requête à ces 3 mots précisément et dans cette ordre la ?

---

Tu cherches donc la phrase contenant seulement les 3 mots 'je', suis', 'fou' dans cet ordre là.

Code:

---

1 2 3 4 5 6 7 8

SELECT p.phrase_id FROM phrases AS p INNER JOIN mots AS m ON p.mot_id = m.mot_id WHERE (m.mot_mot = 'je' AND p.mot_ordre = 1) OR (m.mot_mot = 'suis' AND p.mot_ordre = 2) OR (m.mot_mot = 'fou' AND p.mot_ordre = 3) GROUP BY p.phrase_id HAVING COUNT(*) = 3

---

Avec 3 mots, ça passe encore. Mais si tu cherches des phrases plus longues, avec ton système, ça va vite compliquer la requête et peut-être nuire aux performances.

Merci pour ta réponse !

Par contre cela me retourne aussi les phrases où il y a plus de mots que ces 3 mots, quel serait la meilleure solution pour ne sélectionner que la phrase contenant ces 3 mots la ? Est-il possible de tester si "p.mot_ordre = 4" n'existe pas ?

En fait cela concernera surtout des recherches sur 4 à 5 mots maximum et en général moins.

EDIT :

Code:

---

1 2 3 4 5 6 7 8 9 10

SELECT p.phrase_id FROM phrases AS p INNER JOIN mots AS m ON p.mot_id = m.mot_id WHERE (m.mot_mot = 'je' AND p.mot_ordre = 1) OR (m.mot_mot = 'suis' AND p.mot_ordre = 2) OR (m.mot_mot = 'fou' AND p.mot_ordre = 3) OR (p.mot_ordre = 4) GROUP BY p.phrase_id HAVING COUNT(*) = 3

---

Ce code fonctionne sauf quand il y a un seul mot à vérifier mais je vais essayer de comprendre pourquoi au pire je ferais une autre requête.

Encore merci !

Citation:

---

Par contre cela me retourne aussi les phrases où il y a plus de mots que ces 3 mots, quel serait la meilleure solution pour ne sélectionner que la phrase contenant ces 3 mots la ? Est-il possible de tester si "p.mot_ordre = 4" n'existe pas ?

---

Pour exprimer "n'existe pas" en SQL, il y a l'instruction NOT EXISTS !
Il faut donc qu'il n'existe pas de mot_ordre = 4 dans la phrase qui répond à la condition.
Essaie ceci :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SELECT p.phrase_id FROM phrases AS p INNER JOIN mots AS m ON p.mot_id = m.mot_id WHERE ( (m.mot_mot = 'je' AND p.mot_ordre = 1) OR (m.mot_mot = 'suis' AND p.mot_ordre = 2) OR (m.mot_mot = 'fou' AND p.mot_ordre = 3) ) AND NOT EXISTS ( SELECT * FROM phrases AS p2 WHERE p2.phrase_id = p.phrase_id AND p2.mot_ordre = 4 ) GROUP BY p.phrase_id HAVING COUNT(*) = 3

---

Merci beaucoup ton système fonctionne parfaitement !

Aussi j'aimerais savoir si cela optimisera le poids de la base de données de stocker les phrases ainsi plutôt que simplement dans une même table quitte à avoir beaucoup de phrases contenant les mêmes mots ? Sachant que la base est destiné à recevoir beaucoup de phrases courtes ?

Merci beaucoup pour ton aide !

Ca dépend de l'utilisation de ces phrases.
Un tel découpage n'est utile que si on veut faire une recherche sur les mots des phrases (combien de phrases ont le mot 'fou' ?) ou sur leur position dans les phrases (combien de phrases ont le mot 'fou' en 3ème position ?).

Mais s'il s'agit de construire des phrases déjà pré-déterminées pour les afficher, autant enregistrer simplement les phrases complètes dans une seule table.

A quel besoin répond cette structuration ?

Ces phrases pourront être utilisés pour des recherches sur des moteurs de recherche donc des recherches sur les mots utilisées et leurs positions pourront s'avérer être d'une grande utilité !

Aussi, as-tu un avis quand au poids que cela pourrait prendre par rapport à une bdd avec un système "classique" ?

Citation:

---

Envoyé par beninsky

Aussi, as-tu un avis quand au poids que cela pourrait prendre par rapport à une bdd avec un système "classique" ?

---

Les mots sont stockés dans une colonne de type VARCHAR donc autant d'octets que de lettres + 1 octet pour le nombre de lettres du mot.
Le reste est stocké dans des colonnes de type entier donc 4 octets par ligne et par colonne.
Il faut ajouter les index mais je ne sais pas comment on calcule l'espace. Il s'agit d'un ensemble ordonné de pointeurs, c'est plus compliqué à calculer je pense.

Si tu ne prends qu'une table de phrases, la phrase sera stockée dans une colonne VARCHAR donc autant d'octets que de lettres + 1 octet pour le nombre de lettres de la phrase (ne pas oublier les espaces et autres signes de ponctuation éventuels).
Par contre l'indexage sera quasi inutile et la recherche moins facile, sauf à utiliser la recherche FULLTEXT mais je n'en ai jamais fait l'expérience donc je ne sais pas jusqu'à quel point c'est performant.

Merci pour toutes ces informations !

Je vais me renseigner sur la recherche type FULLTEXT mais je pense quand même garder le système avec les mots à part.

Je vais faire des tests pour le poids de la base de données j'éditerai ma réponse pour les résultats.