Optimisation fulltex plusieurs colonnes

**jimmo** · 26/04/2016, 12h40

Bonjour,

Ma question se porte sur les index de type fulltext lors de la recherche sur plusieurs colonnes.

Une table mail
id int(11),
nom varchar(50),
prenom varchar(50),
titre varchar(200),
message text,
....

Lors d'une recherche sur nom ou prénom, pas de soucis, la requêtes sont rapides.
Mais dès que je recherche dans nom ou prénom + un mot particulier se trouvant dans le titre ou le message de l'email, alors la requêtes mets environ 4/5 secondes.
Voici mes index:

Code :

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

1
2
3
ALTER TABLE mail ADD FULLTEXT INDEX searchFrom(nom, prenom);
ALTER TABLE mail ADD FULLTEXT INDEX searchWord (titre, message);
SELECT nom, prenom, titre, message FROM mail FORCE INDEX (searchFrom, searchWord) WHERE MATCH (nom, prenom) AGAINST(:name) AND MATCH (titre, messasge) AGAINST(:word)

Comment optimiser tout ça?
Merci

**Artemus24** · 26/04/2016, 17h22

Salut Jimmo.

Il faut faire quelques déclaratives dans "my.ini".

Code :

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

1
2
3
4
5
6
7
8
9
10
11
# -------------------------#
#     Full Text Search     #
# -------------------------#
 
ft-max-word-len  = 10
ft-min-word-len  = 1
ft-stopword-file = ""
 
innodb-ft-enable-stopword           = off
innodb-ft-max-token-size            = 10
innodb-ft-min-token-size            = 0

afin de définir comme tu vas gérer tes index FTS.

Et voici un exemple :

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `mail`
--------------
 
--------------
CREATE TABLE `mail` (
  `id`       integer unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `nom`      varchar(255)     NOT NULL,
  `prenom`   varchar(255)     NOT NULL,
  `titre`    varchar(255)     NOT NULL,
  `message`  text             NOT NULL,
  fulltext key `ft1` (`nom`,`prenom`),
  fulltext key `ft2` (`titre`,`message`)
) ENGINE=MyIsam
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `mail` (`nom`,`prenom`,`titre`,`message`) VALUES
('un',     'dix',    'chapitre', 'omega'),
('deux',   'onze',   'prologue', 'beta'),
('trois',  'douze',  'titre',    'gamma'),
('quatre', 'treize', 'verset',   'omicron')
--------------
 
--------------
select * from mail
--------------
 
+----+--------+--------+----------+---------+
| id | nom    | prenom | titre    | message |
+----+--------+--------+----------+---------+
|  1 | un     | dix    | chapitre | omega   |
|  2 | deux   | onze   | prologue | beta    |
|  3 | trois  | douze  | titre    | gamma   |
|  4 | quatre | treize | verset   | omicron |
+----+--------+--------+----------+---------+
--------------
SELECT nom, prenom, titre, message
FROM mail
WHERE MATCH (nom, prenom)     AGAINST('deux' in natural language mode)
--------------
 
+------+--------+----------+---------+
| nom  | prenom | titre    | message |
+------+--------+----------+---------+
| deux | onze   | prologue | beta    |
+------+--------+----------+---------+
--------------
SELECT nom, prenom, titre, message
FROM mail
WHERE MATCH (titre, message) AGAINST('+o*' in boolean mode)
--------------
 
+--------+--------+----------+---------+
| nom    | prenom | titre    | message |
+--------+--------+----------+---------+
| un     | dix    | chapitre | omega   |
| quatre | treize | verset   | omicron |
+--------+--------+----------+---------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

@+

**jimmo** · 26/04/2016, 18h12

Salut Artemus24

En fait ma question porte sur le fait de rechercher selon 2 conditions.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
--------------
SELECT nom, prenom, titre, message
FROM mail
WHERE MATCH (titre, message) AGAINST('+o*' in boolean mode) AND MATCH (nom, prenom) AGAINST('quatre')
--------------
 
+--------+--------+----------+---------+
| nom    | prenom | titre    | message |
+--------+--------+----------+---------+
| quatre | treize | verset   | omicron |
+--------+--------+----------+---------+

Est ce la meilleure façon de faire?
Car en faisant cette requête sur une table de 5 millions de lignes, j'en ai pour 4 à 5 secondes

**Artemus24** · 26/04/2016, 20h24

Salut Jimmo.

Si tu n'as pas d'index de type "Full Text Search", tu ne peux pas faire de recherche.
Donc l'index FTS est indispensable.

Concernant l'optimisation, cela dépend de beaucoup de choses.
Taille de tes colonnes, du nombre de lignes, du nombre d'occurrences d'un mot, ...

C'est un sujet que je ne maîtrise pas trop, vu que je ne m'en sers pas.

Envoyé par Jimmo

Est ce la meilleure façon de faire?

De faire quoi ?
Une recherche sur des colonnes très larges (genre bigtext) avec de très nombreuses lignes, je pense qui OUI !
Mais je te rappelle que c'est du MySql, et dès que la volumétrie est importante, tu n'as pas la performance associée.

As-tu introduit le paramétrage dans le fichier My.Ini ?

Admettons que la recherche ne dépasse jamais plus de cinq caractères.
Et bien, elle sera plus performante que si tu traites dix caractères.

Donc il te faudra faire des tests pour trouver le bon compromis.

Optimiser une base de données ne consiste pas écrire trois requêtes et à mettre des index un peu par tout.
Il faut faire beaucoup de tests, en jouant sur la taille des buffers de MySql, sur l'écriture des requêtes, ...
Enfin un vaste programme, qui permet parfois de passer de 5 à moins de 1 seconde.
Et parfois aucun changement n'est possible.

@+

**jimmo** · 26/04/2016, 20h37

Tout d'abord merci,
J'ai des index de type fulltext.

Code :

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

1
2
ALTER TABLE mail ADD FULLTEXT INDEX searchFrom(nom, prenom);
ALTER TABLE mail ADD FULLTEXT INDEX searchWord (titre, message);

Y a t il une différence avec des index fts?

Code :

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

1
2
3
4
5
6
7
ft-max-word-len  = 10
ft-min-word-len  = 1
ft-stopword-file = ""
 
innodb-ft-enable-stopword           = off
innodb-ft-max-token-size            = 10
innodb-ft-min-token-size            = 0

Je ne trouve aucune des lignes dans my.ini. Ou dois je les rajouter? Qu'est ce que c'est?
Suis un peu perdu la

**SQLpro** · 27/04/2016, 11h56

Envoyé par Artemus24

Concernant l'optimisation, cela dépend de beaucoup de choses.

L'une des principale chose étant la limitation de l'optimiseur de MySQL qui est l'un des plus mauvais de tous les SGBDR... En particulier il est incapable d'utiliser plusieurs index d'une même table pour optimiser une requête !

Amusez vous à faire la comparaison avec SQL Server, même en version gratuite et vous verrez ce qu'est un vrai SGBD Relationnel.
Pour info : http://blog.developpez.com/sqlpro/p9...text_search_no

A +

**Artemus24** · 27/04/2016, 12h58

Salut à tous.

Envoyé par Jimmo

Y-a-t-il une différence avec des index fts?

Aucune différence, puisque c'est la même chose.
Sauf que toi, tu les as mis à l'extérieur du create table :

Code :

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

1
2
ALTER TABLE mail ADD FULLTEXT INDEX searchFrom(nom, prenom);
ALTER TABLE mail ADD FULLTEXT INDEX searchWord (titre, message);

et que moi, je les ai mis dans le create table :

Code :

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

1
2
fulltext key `ft1` (`nom`,`prenom`),
fulltext key `ft2` (`titre`,`message`)

L'index de type FTS indique les colonnes sur lesquelles tu vas faire tes recherches.

Ou alors, je ne comprends pas ta question.

Envoyé par Jimmo

Je ne trouve aucune des lignes dans my.ini.

Si je te demande de les mettre dans le fichier My.Ini, c'est qu'ils n'y sont pas !

Envoyé par Jimmo

Ou dois je les rajouter?

Dans la section "[wampmysqld]" si tu es sous wampserver.
Cette section correspond au nom de ton service MySql.
Si tu ignores son nom, tu dois consulter les services en sélectionnant "services.msc" sous windows.

Envoyé par Jimmo

Qu'est-ce que c'est?

Le premier bloc concerne le moteur MyIsam :

Code :

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

1
2
3
ft-max-word-len  = 10
ft-min-word-len  = 1
ft-stopword-file = ""

et le second bloc concerne le moteur InnoDB :

Code :

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

1
2
3
innodb-ft-enable-stopword = off
innodb-ft-max-token-size  = 10
innodb-ft-min-token-size  = 0

Cela concerne la création de tes index FTS.
On définie le minimum et le maximum de ce qui sera stocké dans l'index.
--> http://dev.mysql.com/doc/refman/5.7/...xt-search.html

Envoyé par Jimmo

Suis un peu perdu là

Je veux bien te croire.
Peut-on savoir pourquoi as-tu choisi le full search text (FTS) pour faire tes recherches ?

@ SQLPRO : nous le savons que MySql n'est pas à la hauteur de MS SQL SERVER.

@+

**jimmo** · 27/04/2016, 16h25

J'ai ajouté les lignes dans le fichier my.ini mais cela ne change rien.
Dois je recréer mes index après la modification du fichier?

Envoyé par Artemus24

Peut-on savoir pourquoi as-tu choisi le full search text (FTS) pour faire tes recherches ?

C'est ce qu'il m'a semblé le plus pertinent. Mais je suis tout à fait ouvert à d'autres propositions.
J'ai aussi essayé avec LIKE '%mot_a_trouver%' mais la vitesse d'exécution est plus ou moins identique.

Il doit bien exister quelque chose pour faire une recherche sur une table de 5 millions de lignes avec 2 conditions sans que ça prenne 5 secondes en local non?
J'ai essayé de faire des jointures sur la même table, j'ai essayé avec UNION...
le meilleur résultat c'est 5 secondes. pfffff

**Artemus24** · 27/04/2016, 19h29

Salut Jimmo.

Envoyé par Jimmo

J'ai ajouté les lignes dans le fichier my.ini mais cela ne change rien. Dois je recréer mes index après la modification du fichier?

Après les modifications de ton fichier My.Ini, il faut relancer WampServer !
Et oui, il faut recréer les index FTS de tes tables MySql.

Envoyé par Jimmo

Il doit bien exister quelque chose pour faire une recherche sur une table de 5 millions de lignes avec 2 conditions sans que ça prenne 5 secondes en local non?

Oui, cela se nomme l'optimisation ! C'est assez difficile à faire, sans avoir la base de données sous les yeux.

Envoyé par Jimmo

J'ai essayé de faire des jointures sur la même table, j'ai essayé avec UNION...

Ne pourrais-tu pas restreindre ta recherche en faisant un "limit N" ?
Il faudrait que tu détailles un peu plus ce que tu essayes de faire.

@+

**jimmo** · 27/04/2016, 21h44

Je vais tenter d’être plus précis.
Ma base de données

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
CREATE TABLE IF NOT EXISTS `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `titre` varchar(100) NOT NULL,
  `resume` varchar(400) NOT NULL,
  `mot_cle` varchar(200) NOT NULL,
  `nb_pages` int(5) NOT NULL,
  `id_auteur` int(6) NOT NULL,
  `annee` int(4) NOT NULL,
  `type_` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ind_annee` (`annee`),
  KEY `ind_type` (`type_`),
  FULLTEXT KEY `toSearch` (`titre`,`mot_cle`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 
INSERT INTO book(titre, resume, mot_cle, nb_pages, id_auteur, annee, type_) VALUES
('titre1 blabla', 'resume1', 'motCle1, motCle2', '100', '4', '1999', 'policier'),
('titre2', 'resume2', 'motCle10, motCle20, blabla', '80', '7', '2008', 'roman'),
('titre3', 'resume3', 'motCle10, motCle2', '252', '11', '2015', 'aventure'),
('titre4', 'resume4', 'blabla, motCle4, motCle40', '120', '44', '2015', 'policier')

Je recherche les livres contenant le mot "blabla" dans le titre ou dans les mots cles et qui sont de type "policier"
resultat: la 1ere et derniere ligne
Ma base de données contient environ 5 millions de lignes et a 19 colonnes.
La requete que j'utilise pour le moment:

Code :

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

SELECT titre, resume, mot_cle, nb_pages, id_auteur, annee, type_ FROM book WHERE type_='policier' AND MATCH (titre, mot_cle) AGAINST('blabla') LIMIT 5;

Cette requête sur 5 million de lignes prend 5 6 secondes.
J'ai également modifié

Code :

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

1
2
innodb-ft-max-token-size            = 20
innodb-ft-min-token-size            = 4

Il m'a semblé que c’était un tout petit peu mieux.
Encore que je ne suis pas sur car en fait, j'affiche le résultat sous forme json qui est le plus rapide. Sous wamp j'en ai pour 30 à 40 secondes et en ligne de commande, j'en ai pour 10 secondes.
De plus en plus compliqué j'ai l'impression.

EDIT:
J'avais nb_pages et annee de type varchar au début.
Apres avoir lu à droite et à gauche que l’indexation sous forme integer était plus rapide et moins gourmande que varchar, je les ai passé en integer (voir la base ci dessus)
Mais dans wamp, le poids des données a diminué de 80Mo, mais par contre, le poids des index a pris environ 500Mo de plus (avec varchar j'étais à environ 100Mo d'index et maintenant suis à presque 600Mo => qui représente presque 50% du poids de la table totale)

**Artemus24** · 28/04/2016, 11h02

Salut Jimmo.

Est-ce des mots entiers que vous recherchez dans votre table ?
Dans ce cas, il vaut mieux préciser dans la clause where ceci :

Code :

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

WHERE MATCH (nom, prenom)     AGAINST('deux' in natural language mode)

En ce qui concerne la largeur de tes mots, cela risque d'avoir une quelconque influence sur la volumétrie de tes index.

Je te conseille de lire la documentation MySql : http://dev.mysql.com/doc/refman/5.7/...xt-search.html

Après, je ne sais pas comment t'aider car comme je l'ai dis précédemment, je ne l'utilise pas ce "full search text" !

@+

**jimmo** · 29/04/2016, 14h35

Toujours le même problème.
Je vais revoir la conception de la BDD. Un peu long mais je ne vois que ça.
Et dans ce contexte, j'ai une question
La bdd sert à faire de l'archivage journalier. Donc à part l'insertion de quelques centaines de lignes via une tache CRON vers 3h du matin, la bdd ne sera que consultée via SELECT.

Sachant cela:

Ma bdd:
table A => 5 colonnes et env. 2 millions de lignes
table B => 15 colonnes et env. 3 millions de lignes
table C => 20 colonnes et env 4 millions de lignes

Les données de la table B et C sont en relation directe avec la table A via un id unique.
Vaut il mieux avoir la table A et une table D(table B+C) de 7 millions de lignes et 20 colonnes
Ou conserver le format Table B et Table C?
Une table plus grosse et moins de jointures ou 2 tables plus petites et jointure supplémentaire?

Merci de votre aide.

**SQLpro** · 29/04/2016, 15h23

Moins une table à de colonnes et mieux c'est. Le plus performant d'un point de vu théorique pour toutes les possibilités de recherches et les mises à jour reste la table ne comportant qu'une seule colonne en sus de la colonne clef....

À me lire : http://blog.developpez.com/sqlpro/p1...mances_petites

A +

**jimmo** · 29/04/2016, 15h48

Très intéressant ton article.
Et pour des recherches poussées, effectivement il est intéressant de découper des numéros de tel ou autre.
Mais pour des recherches très simples, comme dans mon cas ou il s'agit uniquement de recherche via l'id et c'est tout.
En reprenant l'exemple des tables ci dessus, voici a quoi ressemble ma requête

Code :

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

SELECT a.col1, a.col2,  b.col1, b.col2, c.col1, ... c.col15 FROM A AS a LEFT JOIN B AS b USING(id)  LEFT JOIN C AS c USING(id) WHERE a.id=12

Donc tu me conseille de rester sur 2 tables différentes (1 de 15 colonnes et 1 de 20) ?

**Artemus24** · 29/04/2016, 17h08

Salut à tous.

Comme le dit SQLPRO :

Envoyé par SQLPRO

Moins une table à de colonnes et mieux c'est.

J'ai appris, il y a fort longtemps, que l'on devait éviter d'avoir trop de varchar dans une ligne et de faire en sorte de n'en avoir qu'un seul et de préférence en fin de ligne.

Envoyé par SQLPRO

Le plus performant d'un point de vu théorique pour toutes les possibilités de recherches et les mises à jour reste la table ne comportant qu'une seule colonne en sus de la colonne clef....

Oui, enfin disons trouver un juste milieu entre un minimum et un maximum de colonnes dans une table.
Je pense que cela dépend aussi du type de ces colonnes, mais surtout de la requête qui va consulter la ou les tables.

Dans le cas d'une recherche basée sur une date, le plus intéressant est de créer des tables partitionnées avec comme critère cette date.
Genre, une partition par mois donné, avec une rotation sur treize mois.
Cela dépend aussi de la volumétrie que l'on a, dans la recherche que l'on veut faire.
La recherche sera d'autant plus performante que la volumétrie sera d'autant plus faible.

Il est difficile de donner une solution passe partout sans bien connaitre les contraintes de départ.

Envoyé par Jimmo

Toujours le même problème.

Cela ne me surprend pas du tout car MySql ne sait pas bien gérer la grosse volumétrie.

Le problème concerne aussi la finesse de la recherche que vous désirez faire.
Si la recherche sur les mots pertinents ne donne au plus que quelques occurrences, cela devrait être extrêmement rapide.
Inversement, si un mot donne plusieurs milliers d'occurrences, question performance, ce sera plutôt long.

J'ai oubli de préciser que le FTS fonctionne mieux avec le moteur MyIsam qu'avec le moteur InnoDB, sous MySql.

Le FTS permet aussi de supprimer, dans l'index, les mots qui n'ont pas de significations particulière dans vos recherches, comme "le, la, les, un, une, des ...".
Cela se nomme "stopwords.
--> http://dev.mysql.com/doc/refman/5.7/...stopwords.html

Une possible optimisation est de faire ceci, sous InnoDB :

Code :

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

1
2
set GLOBAL innodb_optimize_fulltext_only=ON;
OPTIMIZE TABLE votre_table;

@+

**jimmo** · 30/04/2016, 17h24

Salut,

Envoyé par Artemus24

Il est difficile de donner une solution passe partout sans bien connaitre les contraintes de départ.

Mes contraintes sont peu existantes il me semble.
Uniquement du stockage de données. Certes entre 15 et 20 colonnes mais des recherches qui ne se font exclusivement que sur l'id.

Code :

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

SELECT col1, col2, col3,.....col2 FROM table WHERE id=12

Envoyé par Artemus24

J'ai oubli de préciser que le FTS fonctionne mieux avec le moteur MyIsam qu'avec le moteur InnoDB, sous MySql.

A quel point il fonctionne mieux? Une vraie différence qui vaut le coup d'en changer? Je ne connais pas trop les différences entre ces 2 moteurs. Pourquoi plus l'un que l'autre?

Envoyé par Artemus24

Le FTS permet aussi de supprimer, dans l'index, les mots qui n'ont pas de significations particulière dans vos recherches, comme "le, la, les, un, une, des ...".
Cela se nomme "stopwords.

Je me suis renseigné la dessus mais je n'ai pas trouvé comment alimenter cette liste avec des mots précis.
Merci de votre aide et patience

**jimmo** · 30/04/2016, 19h00

Une fois de plus, merci Artemus24.
J'ai essayé le moteur MyIsam et effectivement, il est plus optimisé. Je suis passé d'environ 5 secondes à 0.02 secondes. Un vrai régal.
Encore merci merci

Optimisation fulltex plusieurs colonnes

Administration MySQL

Discussions similaires

Partager

Partager