select (fetch) lent sur grosse table 600Mo (pb RAM ?)

[nosapiens]

Salut !
Je me décide finalement à vous soumettre mon problème qui malgré mes recherches aussi bien en français qu'en anglais reste à ce jour insoluble.
J'ai trouvé beaucoup de topic approchant et je pense que c'est un problème archi-classique mais malgré cela je sèche.

Voici ma base simple et suffisamment explicite je pense, qui met en relation des mots et les phrases dans lesquelles ils apparaissent (un corpus):
on peut la télécharger ici.

=== les 3 Tables MYISAM

table MOTS
id_mot INT --> clé avec index
mot VARCHAR

table PHRASE (3M d'enregistrement, environ 650Mo)
id_phrase INT --> clé avec index
phrase TEXT --> index FULLTEXT

table de jointure MOT-PHRASE (3Go)
id_mot INT --> avec index
id_phrase INT --> avec index

=== ma config:
RAM: 1Go Proc 1,72GHz

=== ma requete:

Code :

1
2
3
4
SELECT phrase
FROM MOT-PHRASE
JOIN PHRASE ON MOT-PHRASE.id_phrase = PHRASE.id_phrase
WHERE id_mot = 1547 # (ce mot apparait dans environ 3000 phrases)

Lorsque il y a plus de 1000 phrases correspondant au mot recherché, la requête dure plus de 15 secondes (c'est le "FETCH" qui est lent).

Par contre si je demande juste de m'afficher l'id des phrases, ( en remplaçant "SELECT phrase..." par "SELECT id_phrase..." la requête dure moins de 2 sec !

Est-ce que la table est tout simplement trop grosse pour ma mémoire vive ou alors il existe un moyen de contourner le problème qui m'aurait échappé ? (splitté la table,changer le design de la base, utilisé INNODB,... ?)

Peut-être utiliser un autre format de donnée pour les phrases: remplacer TEXT par VARCHAR ?

Merci de vos suggestions


------------------------------------------------------------------
"En essayant continuellement, on finit par réussir. Donc : plus ça rate, plus on a de chances que ça marche" (devise Shadock)

[ericd69]

salut,

soit tu fais un index fulltext soit tu utilises ton émulation du fulltext mais les 2 en même temps...

[nosapiens]

soit tu fais un index fulltext soit tu utilises ton émulation du fulltext mais les 2 en même temps...

Merci de ta réponse pertinente éric ! J'avais même pas réalisé l'équivalence entre les 2 !
En fait je sais pas pourquoi il est là cet index fulltext ! Je l'ai viré et la requete est un peu plus rapide mais c'est pas encore le top, environ 12s.

[ericd69]

y a du progrès 20% lol

ensuite il dit quoi l'explain de ta requête?

parce que vu que tu n'indexes pas la colonne mot j'ai l'impression qu'il doit faire un fullscan
non?

[nosapiens]

???
j'en doute vu que la table MOT n'intervient pas dans ma requête !!

[ericd69]

pour info, tu peux faire (c'est à ça que je pensais en te faisant la réflexion):

Code sql :

1
2
3
4
SELECT p.phrase
FROM phrase p
INNER JOIN `mot-phrase` mp ON mp.id_phrase=p.id_phrase
INNER JOIN mots m ON m.id_mot=mp.id_mot AND m.mot='expression'

pour chercher directement un mot

au passage, tu dois encadrer mot-phrase à cause du - et adopter un nommage cohérent au niveau du singulier ou du pluriel pour les tables

ensuite faut régler les buffers d'index et de données si tu as accès à ces réglages... c'est pas trop le genre de chose à mettre en place sur un hébergement mutualisé par exemple vu que tu ne peux pas les faire...

[nosapiens]

Salut éric !
merci d'avoir veiller aussi tard pour me répondre

Tout d'abord (désolé, j'aurais peut-être dû le préciser au départ), il s'agit d'une version simplifiée de ma base. (Dans la mienne, l'incohérence de nommage est encore pire LOL !!).

La solution avec l'index FULL TEXT ne m'intéresse pas car en réalité les phrases ne sont pas liées à des mots mais à ce que l'on appelle en Lexicologie des lexies, càd l'association d'une forme et d'un sens.
Par exemple le mot (on dit "vocable" en lexicologie) "souris" est composé de (au moins) 2 lexies: l'une ayant le sens "accessoire informatique" et l'autre le sens "petit rongeur".

Donc ma table de jointure comporte un 3è champs id_sens.
Mon but est donc de proposer des exemples de phrases spécifiques à chaque sens d'un mot.

Bon je fais ça pour le fun, je suis pas informaticien et mon projet est carrément sur-ambitieux, donc je suis à des années lumières de pouvoir la mettre à disposition mais je rêve d'une base de données lexicales multilingue ultra-complète et collaborative façon wikipedia

J'ai joint un schéma (temporaire) de ce à quoi ça pourrait ressembler si ça t'intéresse mais je pense que c'est un peu nébuleux... (Rmq: c'est pas non plus le schéma actuel de ma base !!)

Bref pour l'instant je bidouille qu'en locale et donc je peux configurer mon serveur comme je veux. Les buffers auxquels tu fais allusion sont-ils table_open_cache et key_buffer_size ?

Merci en tout cas pour ton aide

[ericd69]

tu peux rajouter une contrainte avec les langues qui n'ont pas de séparateurs entres les mots (langues japonaises anciennes, chinois, etc...)

key_buffer et data_buffer un truc dans le genre... mais attention tu as un gain jusqu'à environ 2/3 de leur réglage maxi après ça commence à rebaisser...

[CinePhil]

Tu peux nous montrer le résultat du vrai EXPLAIN de la vraie requête ?

Et tant qu'à faire la vraie structure des vraies tables ?

[nosapiens]

Salut Phil !
Ma base est la même que celle présentée mais avec des noms de champs et de table un peu différents.
De toute façons je crois que le problème vient essentiellement de la consultation de la table contenant les phrases:
En effet, j'ai effectué la requete suivante:

Code :

1
2
3
 
SELECT * FROM sentences
WHERE s_id IN (1000,2000,3000.....) # 3000 valeur d'ID séparée chacune par 1000 enregistrements

verdict MySQL workbench: duration:3.375 fetch: 34,407sec !!

EXPLAIN:
select , table, table_type, type, possible keys, key, key_lenght, ref, rows, extra
SIMPLE, sentences, range, PRIMARY, PRIMARY, 4, NULL, 2994, Using where

D'autres infos concernant la config de mon serveur Mysql:
Join buffer size: 131072
Key cache block size :1024
preload_buffer_size: 32768
read_buffer_size: 256K
key buffer size: 80388608

RMQ: j'ai maintenant 2GO de RAM donc peut-être que je peux changer certaines valeurs de buffer mais lesquels ?

[ericd69]

c'est normal qu'il soit à la ramasse avec ce genre de requête...

in est pas vraiment pensé pour passer 3000 valeurs ou plus même si c'est possible...

ici, tu dois créer une table temporaire avec engine=memory comme moteur la remplir avec tes valeurs et ensuite faire un inner join entre elle et ta table cible... là, ça devrait considérable réduire le temps d'exécution...

[nosapiens]

oui en fait moi non plus j'ai pas besoin des 3000 résultats simultanément.
Le problème c'est que j'utilise open office base pour créer les formulaires avec lesquelles je peux confortablement gérer les enregistrements de la base.
Comme la connexion avec mysql se fait par ODBC, je peux pas utiliser LIMIT pour limiter le nombre de résultats.

Concernant ta solution éric je suis pas sûr d'avoir bien compris:

En fait, il faudrait que je crée une "copie" en mémoire de ma table PHRASES et que je travaille ensuite avec celle là plutôt qu'avec l'originale pour limiter l'accès au disque, c'est bien ça?
Dans ta phrase:

créer une table temporaire avec engine=memory comme moteur la remplir avec tes valeurs et ensuite faire un inner join entre elle et ta table cible

qu'appelles tu "tes valeurs" et "table cible" ?

merci

[ericd69]

sentences...

pour ce genre de trucs openoffice est pas franchement le mieux surtout vu la façon à gérer les bd dans le même style qu'office... en tout cas ça rame autan et tu peux guère dépasser quelques milliers de lignes de traitement en terme de performance, c'est bien pour des tout petits traitement et une facilité d'insertion dans différents documents mais c'est pas pensé pour faire des méga traitements....

pour ce genre de trucs soit tu ferais mieux de passer par phpmyadmin soit par php et mysql... au moins le temps de mettre au point tes requêtes

en plus faut que tu extraies tes mots des phrases... sans compter leur trouver là encore passer par openoffice comme moyen de saisie est pas forcément le plus simple...

non je t'expliquais qu'utiliser l'opérateur in en lui passant des milliers de résultats c'est pas forcément la meilleurs des solutions pour des performances...