Discussion :

[Bobtop]

Bonjour,

Suite à la mise en place d'un petit moteur de recherche dans mon forum, je me retrouve avec quelques slow-queries.

N'étant pas très doué en MySQL je me demande où cela cloche.

Voici par exemple une requête de recherche :

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT fs.sujet_id
FROM sujets fs
INNER JOIN messages fm ON fm.sujet_id = fs.sujet_id
INNER JOIN search_word_match swm ON swm.message_id = fm.message_id 
INNER JOIN search_word_list swl ON swl.word_id = swm.word_id
WHERE swl.word_text IN ('restaurant','paris')
GROUP BY fs.sujet_id
HAVING COUNT(DISTINCT swl.word_text) >= 2;

Et un explain me donne :

Code :

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

1
2
3
4
5
6
 
id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
1 SIMPLE swl range PRIMARY,word_id PRIMARY 50 NULL 6 Using where; Using temporary; Using filesort 
1 SIMPLE swm ref message_id,word_id word_id 3 db.swl.word_id 59 Using where 
1 SIMPLE fm eq_ref PRIMARY,sujet_id PRIMARY 3 db.swm.message_id 1   
1 SIMPLE fs eq_ref PRIMARY PRIMARY 2 db.fm.sujet_id 1 Using index

La table search_word_list contient 200 000 enregistrements
La table search_word_match preque 12 millions

La requête met 15s environ à s'executer.

Je ne vois pas où il y a un probleme.

Merci pour votre aide

[Maximil ian]

Il y a un index sur word_text ?

Quel est le type et la conf du serveur ?

[Bobtop]

word_text est en clé primaire je suppose que c encore mieux que de mettre un index ?

sinon pour la machine c un Intel Céléron - 2.4 GHz
512 de RAM

Apache Release 10333100 (je sia spas ou regarder pour avoir la version)
PHP Version 4.3.10
MySQL 4.1.11

Merci pour votre aide

[Maximil ian]

word_text est en clé primaire je suppose que c encore mieux que de mettre un index ?

Oui

Quel temps donne la requête si on ne cherche que sur 1 mot-clé et qu'on enlève le GROUP BY et le HAVING ?

[Bobtop]

Ah ben c'est instantanée pratiquement, d'une requête qui prend 23s je descends à 0,0028s

[Maximil ian]

OK, donc c'est la création de table temporaire due au group by qui plombe tout.
Essaie plutôt un truc comme ça (non testé) :

Code :

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

1
2
3
4
5
6
7
8
SELECT fs.sujet_id
FROM sujets fs
INNER JOIN messages fm ON fm.sujet_id = fs.sujet_id
INNER JOIN search_word_match swm ON swm.message_id = fm.message_id 
INNER JOIN search_word_list swl ON swl.word_id = swm.word_id
INNER JOIN search_word_list swl2 ON swl2.word_id = swm.word_id
WHERE swl.word_text = 'restaurant'
AND swl2.word_text = 'paris'

[Bobtop]

Alors :

La requête ne retourne malheureusement aucun enregistrement même sur 2 mots situés dans le même message.

Par contre une recherche sur 1 seul mot fonctionne :

Code :

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

1
2
3
4
5
6
SELECT fs.sujet_id
FROM sujets fs
INNER JOIN messages fm ON fm.sujet_id = fs.sujet_id
INNER JOIN search_word_match swm ON swm.message_id = fm.message_id 
INNER JOIN search_word_list swl ON swl.word_id = swm.word_id
WHERE swl.word_text = 'restaurant'

J'ai aussi essayé une requête avec un OR et des LEFT JOIN mais j'attends toujours la réponse lol :

Code :

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

1
2
3
4
5
6
7
8
SELECT fs.sujet_id
FROM sujets fs
INNER JOIN messages fm ON fm.sujet_id = fs.sujet_id
INNER JOIN search_word_match swm ON swm.message_id = fm.message_id 
LEFT JOIN search_word_list swl ON swl.word_id = swm.word_id
LEFT JOIN search_word_list swl2 ON swl2.word_id = swm.word_id
WHERE swl.word_text = 'restaurant'
OR swl2.word_text = 'paris'

La requête que tu m'as donné me parait pourtant pas mal malheureusement ça dépasse mes compétences

[Alexandre T]

Dans le cas d'une recherche par "OU INCLUSIF", il n'y a plus d'intérêt à utiliser un synonyme de la table search_word_list

J'y verrais donc une méthode plus simple :

Code :

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

1
2
3
4
5
6
 SELECT fs.sujet_id
FROM sujets fs
INNER JOIN messages fm ON fm.sujet_id = fs.sujet_id
INNER JOIN search_word_match swm ON swm.message_id = fm.message_id 
INNER JOIN search_word_list swl ON swl.word_id = swm.word_id
WHERE swl.word_text = 'restaurant' OR swl.word_text = 'paris'

Si vous souhaitez remonter une recherche qui retourne les sujets dont au moins 1 message contient les deux mots, il faut utiliser la requête de Maximilian.

Si vous souhaitez remonter les sujets pour lesquels dans l'ensemble des messages de ce sujet se trouve les deux mots (et pas forcément les deux mots dans un même message) alors il faut une autre requête avec synonyme sur la table message.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT fs.sujet_id
FROM sujets fs
INNER JOIN messages fm1 ON fm1.sujet_id = fs.sujet_id
INNER JOIN search_word_match swm1 ON swm.message_id = fm1.message_id 
INNER JOIN search_word_list swl1 ON swl1.word_id = swm1.word_id
 
INNER JOIN messages fm2 ON fm1.sujet_id = fs.sujet_id
INNER JOIN search_word_match swm2 ON swm.message_id = fm2.message_id 
INNER JOIN search_word_list swl2 ON swl2.word_id = swm2.word_id
WHERE swl1.word_text = 'restaurant'
AND swl2.word_text = 'paris'

PS : en MySQL il existe les index de type FULL TEXT qui marche très bien.
(Je les utilise sur ce site si vous voulez tester : http//www.clublegendes.com )

[Bobtop]

La requête de Maximilian ne retourne aucun sujet même les sujets dont 1 message contient les 2 mots recherchés.

Si l'internaute fait une recherche sur 5 mots ça risque de faire une sacrée requête ?

Ma première requête est faite en suivant le tutoriel d'indexation textuelle sur ce site, pourquoi ma requête est lente alors j'ai pourtant repris à la lettre le tuto ?

Merci encore pour votre aide

[Alexandre T]

Je suis surpris qu'elle ne retourne rien.

Je pense que les 12 millions de lignes ne sont pas étrangers aux lenteurs que vous rencontrés.

Pouvez vous nous transmettre la structure de toutes vos tables ? Plus précisément le retour de

Code :

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

1
2
3
4
5
 
SHOW CREATE TABLE sujets;
SHOW CREATE TABLE messages ;
SHOW CREATE TABLE search_word_match;
SHOW CREATE TABLE word_text;

(C'est pour obtenir la liste des index que je vous demande cela et pour le recréer rapidement sur ma base de test)

Pouvez-vous vérifier "manuellement" les id de restaurant et de paris ?

Code :

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

1
2
3
4
 
SELECT word_id,word_text 
    from search_word_list 
  where word_text in ('restaurant','paris')

Puis le retour de cette requête en remplaçant valeur1 et valeur2 par les résultats de la requête précédente

Code :

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

1
2
3
4
5
 
SELECT word_id,message_id 
  FROM search_word_match 
 WHERE word_id IN (valeur1,valeur2) 
  order by message_id

Le but de ces deux requêtes est de vérifié qque lors de l'insertion des messages, il n'y a pas eu un bug qui a fait que vos données n'ont pas été insérés dans les tables d'index.

[gregory.broissard]

12 millions de lignes sans partitionnement, effectivement, c'est la clé du problème...

Ta table devrait être partitionnée !

Ceci dit, Mysql ne gérant le partitionnement apparemmetn qu'en 5.1...

Migrer vers un autre SGBD??? ou attendre la 5.1?

[Bobtop]

Je reviens sur ce problème (j'ai eu qq autres soucis à régler).

Alexandre voici les points concernés par cette 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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
CREATE TABLE `sujets`(
`sujet_id` smallint(5) unsigned NOT NULL auto_increment,
`forum_id` tinyint(2) unsigned NOT NULL default '0',
`membre_id` mediumint(8) unsigned NOT NULL default '0',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`sujet` varchar(50) NOT NULL default '',
PRIMARY KEY  (`sujet_id`),
KEY `sujet` (`sujet`),
KEY `membre_id` (`membre_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
CREATE TABLE `messages` (
`message_id` mediumint(8) unsigned NOT NULL auto_increment,
`sujet_id` smallint(5) unsigned NOT NULL default '0',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`sujet` varchar(50) NOT NULL default '',
`message` text NOT NULL,
`membre_id` mediumint(8) unsigned NOT NULL default '0',
PRIMARY KEY  (`message_id`),
KEY `sujet_id` (`sujet_id`),
KEY `membre_id` (`membre_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
CREATE TABLE `search_word_match` (
`message_id` mediumint(8) unsigned NOT NULL default '0',
`word_id` mediumint(8) unsigned NOT NULL default '0',
`word_nb` tinyint(3) unsigned NOT NULL default '0',
`title_match` tinyint(1) NOT NULL default '0',
KEY `message_id` (`message_id`),
KEY `word_id` (`word_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
CREATE TABLE `search_word_list` (
`word_text` varchar(50) NOT NULL default '',
`word_id` mediumint(9) NOT NULL auto_increment,
PRIMARY KEY  (`word_text`),
KEY `word_id` (`word_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Avec un peu recule, les ralentissements semblent assez aléatoires certaines requêtes de recherches sur 4 ou 5 mots peuvent être rapide (moins de 1s) et d'autres sur autant de mots peuvent mettre 15s ou 20s mais je n'arrive pas à voir pourquoi certaines requêtes sont plus longues que d'autres.

Concernant les 12 millions de lignes, il s'agit donc d'une table faisant l'association entre la table contenant la liste des mots et les messages comportant ces mots.

Le forum contient un peu plus de 500 000 messages et cela augmentent de plus en plus vite, (j'aurais mieux fait de prendre un script tout prêt développé peut etre mais j'avais envie d'apprendre à faire un forum ). Donc la table d'association risque d'augmenter aussi rapidement.
Il me semble avoir utiliser le même principe que phpBB or je suppose qu'il existe de nombreux forums avec plus de 500 000 messages utilisant phpBB, comment cela se passe t il dans ce cas la ? La table d'association doit être bcp plus grosse (a moins qu'il y est un pb dans mon script d'indexation mais d'après plusieurs vérifications manuelles cela me semble correcte).
Existe t il un moyen de contourner cette grosseur de table ?

Merci à tous pour votre précieuse aide

PS : J'ai aussi testé les index FULLTEXT mais les temps de réponses sont encore plus longs !

[Bobtop]

Le problème est toujours présent, dès qu'il y a plusieurs recherches en même temps ça fait planté le serveur

J'arrive pas à trouver de solution

[Maximil ian]

sinon pour la machine c un Intel Céléron - 2.4 GHz
512 de RAM

Le souci ne viendrait pas de là ?
Quel est l'état des ressources système lorsque ces problèmes se produisent ?

Au niveau des index essaie d'en créer un sur (word_text, fs.sujet_id) pour éviter la création d'une table temporaire lors du GROUP BY.

Il pourrait aussi être utile de regarder du côté des différents caches système de MySQL.

[Bobtop]

Pour la machine, aucune idée, c'est la RAM ou le proc qui est trop faible ?

Sinon pour l'état du serveur et bien MySQL utilise toutes les ressources possibles et donc fait tout planté.

Je vais regarder les caches MySQL mais il me semble que ca marche parce que lorsque j'execute 2 slow-queries d'affilé, la 2eme est tres rapide.

[Bobtop]

Sinon concernant les index que tu proposes, les champs sont déjà en clé primaire, est ce utile de créer un index sur ces champs ?

[Maximil ian]

Pour la machine, aucune idée, c'est la RAM ou le proc qui est trop faible ?

Je dirais les deux mais surtout la RAM. Et il ne faut pas oublier que tu as aussi un serveur web sur la même machine... ça peut être just si tu manipules de grosses masses de données.

Pour l'index essaie quand même, c'est pour voir si un index double sur (word_text, fs.sujet_id) peut améliorer la situation.