Discussion :

[Sysko le prospecteur]

Bonjour, mon problème est relativement semble je pense, mais n'étant pas expert en sql, je préfère demander

alors le contexte est le suivant :

j'ai une table "sentences" qui contient un ensemble de phrases dans pleins de langues différentes, c'est table est ensuie lié à d'autre tables, notamment la table User, et la table "sentences_translations" qui lient une phrase à une autre phrase , selon une relation has and belong to many

sauf que la plupart du temps on accède au phrase en faisant un filtre "where sentences.lang = fre" par exemple, de plus une fonctionnalité assez utiliser et demander consiste à prendre une phrase aux hasard avec ses traductions, avec possibilité de préciser ou non la langue

le problème vient du fait que la table grossit assez vite ( 100 ajout par jour en moyenne et cela va en grandissant) et la table à déjà 350 000 lignes, de plus 3 langues se partagent 95% des lignes

est-il préférable du coup d'éclater la table sentences en fre_sentences, chi_sentences etc... pour éviter d'avoir à faire de where , sachant que 80% des requêtes si ce n'est plus font un where. et à la rigueur faire un union all lorsque l'on veut rechercher sur toutes les phrases ?

est-ce la bonne solution ? éclater en plein de tables sera-t-il plus performant ? cela va-til compliqué les autres relations ( par exemple la relation un utilisateur à 0 ou plusieurs phrases) ?

je vous remercie d'avance pour vos réponses éclairées

[CinePhil]

Quelle est la structure actuelle de tes tables ?

Pars-tu d'une langue (par exemple le français) pour traduire dans d'autres langues ? (cas 1)
Ou bien pars-tu de n'importe quelle langue vers n'importe quelle autre ? (cas 2)

Dans le cas 1, on aurait le schéma suivant :
Phrase_fr -0,n----Traduire----0,n- Langue

Ce qui donnerait les tables :
Phrase_fr (fr_id, fr_texte)
Langue (l_id, l_nom)
Traduire (tr_id_phrase_fr, tr_id_langue, tr_texte_traduit)

Dans le cas 2, on aurait le schéma suivant :
Phrase -1,1----Utiliser----0,n- Langue
et
Phrase -0,n----Traduire----0,n- Phrase

Ce qui donnerait les tables :
Langue (l_id, l_nom)
Phrase (p_id, p_id_langue, p_texte)
Traduire (tr_id_phrase1, tr_id_phrase2)

[Sysko le prospecteur]

Bonjour

le deuxieme cas, la phrase de départ peut être dans n'importe quelle langue et idem pour la traduction, ce qui donne une structure en graphe non orienté
par exemple :
Bonjour -> ( Hello / Good Morning / Good Evening / Gute morgen
Gute morgen ( Hello / Bonjour / Good morning ) (mais pas Good evening)

la structure à l'heure actuelle, en simplifié

phrases ( id PK, user_id FK , text , lang)
phrases_traductions ( phrase_id , traduction_id , distance)

Phrase -0,n----Traduire----0,n- Phrase

En sachant que 80% des requetes consistent à récupérer des phrases aux hasard et leur traduction et les traductions des traductions , et que je n'ai pas trouvé de méthode pour récuperer une phrase au hasard, avec une répartition quasilinéaire, pour une langue donnée, ( du coup on reste sur un très contestable WHERE lang= "XX"ORDER BY RAND() LIMIT Y )

de par la meme occasion il y a un attribut distance dans la table de jointure car je pense plutot que récuperer d'abord une phrase et ses traduction et ensuite les traductions de chaque traductions , récupérer les traductions de distance < 2 ( en admettant que lors de l'ajout d'une nouvelle traduction on mette à jour cette distance), je ne sais pas si la bonne solution ( car je reprend un projet en cours de route et pour l'instant on n'utilise pas ce champ " distance" )

[CinePhil]

Je crois que tu devrais adopter plutôt ma seconde structure puisque tu es dans le cas 2.

Dans le cas 2, on aurait le schéma suivant :
Phrase -1,1----Utiliser----0,n- Langue
et
Phrase -0,n----Traduire----0,n- Phrase

Ce qui donnerait les tables :
Langue (l_id, l_nom)
Phrase (p_id, p_id_langue, p_texte)
Traduire (tr_id_phrase1, tr_id_phrase2)

Je cherche toutes les traductions de la phrase 'Bonjour' :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT p1.p_texte AS Phrase_a_traduire, 
  l1.l_nom AS Langue_a_traduire,
  l2.l_nom AS Traduction_en_langue,
  p2.p_texte AS Phrase_traduite
FROM Phrase AS p1
INNER JOIN Langue AS l1 ON p1.p_id_langue = l1.l_id
LEFT OUTER JOIN Traduire AS t ON p1.p_id = t.tr_id_phrase1
  LEFT OUTER JOIN Phrase AS p2 ON t.tr_id_phrase2 = p2.p_id
    INNER JOIN Langue AS l2 ON p2.p_id_langue = l2.l_id
WHERE p1.p_texte = 'Bonjour'
  AND l1.l_nom = 'Français'

Je pars ci-dessus d'une phrase en français vers d'autres langues mais s'il y a dans la table Traduire la référence à la phrase voulue en tant que tr_id_phrase2, la traduction ne sera pas obtenue.

Soit tu crées tous les couples de phrases (tr_id_phrase1, tr_id_phrase2) 2 fois, soit il faut changer la requête :

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
22
23
24
25
SELECT p1.p_texte AS Phrase_a_traduire, 
  l1.l_nom AS Langue_a_traduire,
  l2.l_nom AS Traduction_en_langue,
  p2.p_texte AS Phrase_traduite
FROM Phrase AS p1
INNER JOIN Langue AS l1 ON p1.p_id_langue = l1.l_id
LEFT OUTER JOIN Traduire AS t ON p1.p_id = t.tr_id_phrase1
  LEFT OUTER JOIN Phrase AS p2 ON t.tr_id_phrase2 = p2.p_id
    INNER JOIN Langue AS l2 ON p2.p_id_langue = l2.l_id
WHERE p1.p_texte = 'Bonjour'
  AND l1.l_nom = 'Français'
 
UNION
 
SELECT p1.p_texte AS Phrase_a_traduire, 
  l1.l_nom AS Langue_a_traduire,
  l2.l_nom AS Traduction_en_langue,
  p2.p_texte AS Phrase_traduite
FROM Phrase AS p1
INNER JOIN Langue AS l1 ON p1.p_id_langue = l1.l_id
LEFT OUTER JOIN Traduire AS t ON p1.p_id = t.tr_id_phrase2
  LEFT OUTER JOIN Phrase AS p2 ON t.tr_id_phrase1 = p2.p_id
    INNER JOIN Langue AS l2 ON p2.p_id_langue = l2.l_id
WHERE p1.p_texte = 'Bonjour'
  AND l1.l_nom = 'Français'

[Sysko le prospecteur]

merci beaucoup ( par contre il me semble que c'est déjà la structure que j'avais ?)

( pour la recherche par mot, vu que l'on utilise un analyseur qui recherche aussi les différentes formes d'un mot ( forme conjugée etc... ) + recherche composé (and or etc... ) , là dessus les perfs sont déjà très acceptable)

En fait le principal probleme de perf vient de la sélection aléatoire (les utilisateurs ont plébiscité la possibilité de recevoir tout les jours sur leur interface 50 phrases aléatoire dans une langue donnée avec leur traduction directe et indirecte ) qui est soit non linéaire ( et meme constante pour les langues n'ayant qu'une centaine d'enregistrement sur les 400 000) soit très très lentes ( une bonne dizaine de secondes pour 50 phrases aléatoire )

car la table phrase à des trous ( phrases supprimés) et les langues n'ont pas des ids consécutifs

pour l'instant lorsque je veux récupérer une phrase aléatoire, indépendament de la langue j'effectue la requete suivante

Code :

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

1
2
3
4
5
 
SELECT Sentence.id  FROM sentences AS Sentence
 JOIN ( SELECT (RAND() *(SELECT MAX(id) FROM sentences)) AS id) AS r2
 WHERE Sentence.id >= r2.id
 ORDER BY Sentence.id ASC LIMIT 1'

par contre je vois deux problemes à ma requete :
ça ne marche pas si je veux plusieurs phrases aléatoires, et si je précise la langue ce n'est plus linéaire
on ne récupère que l'id et pas les traductions , et encore moins les traductions indirectes

( comme on peut le remarquer je débute en SQL et je me retrouve dans le grand bain car les autres étant des linguistes, le SQL est loin d'être leur priorité )

[CinePhil]

par contre il me semble que c'est déjà la structure que j'avais ?

Effectivement, j'avais mal interprété, sans doute à cause de la colonne distance, que je trouve d'ailleurs bizarre.

Bonjour -> ( Hello / Good Morning / Good Evening / Gute morgen
Gute morgen ( Hello / Bonjour / Good morning ) (mais pas Good evening)

Je ne suis pas linguiste mais je traduirais "Good evening" par "Bonsoir" et donc pas "Bonjour" par "Good evening".

Ton LIMIT me laisse à penser que tu travailles sous MySQL.

Si tu veux sélectionner 50 phrases aléatoires, essaie plutôt ceci :

Code :

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

1
2
3
4
SELECT id
FROM sentence
ORDER BY RAND()
LIMIT 50

Mais tu sembles avoir déjà essayé :

En sachant que 80% des requetes consistent à récupérer des phrases aux hasard et leur traduction et les traductions des traductions , et que je n'ai pas trouvé de méthode pour récuperer une phrase au hasard, avec une répartition quasilinéaire, pour une langue donnée, ( du coup on reste sur un très contestable WHERE lang= "XX"ORDER BY RAND() LIMIT Y )

Une autre méthode pourrait consister à ajouter une colonne numérique qui serait alimentée de manière aléatoire à l'enregistrement de la phrase. En indexant cette colonne et en ordonnant la requête limitée à une tranche de 50 lignes prise au hasard par le logiciel, tu aurais un pseudo aléatoire satisfaisant pour tes besoins je pense.

[Sysko le prospecteur]

C'était juste pour avoir un exemple simple

Oui pour la colonne distance je viens de remarquer qu'elle était inutile ( on peut récupérer les traductions indirectes en faisant un phrase -> traduire -> traduire -> phrase , qui donne de très très bonne perf (environ 0,03 ms sur la base de donnée de test à 1 000 000 d'enregistrement et le serveur est loin d'etre un foudre de guerre )

avec tes exemples je commence à mieux comprendre les jointures et comment les utiliser, c'est parfait

pour ce qui est de l'aléatoire je vais tester ta solution qui me semble pas mal ( mais surement après les fêtes)

merci encore et bonnes fêtes toussa