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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
|
MariaDB [(none)]> CREATE DATABASE `base`
-> DEFAULT CHARACTER SET `latin1`
-> DEFAULT COLLATE `latin1_general_ci`;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use base;
Database changed
MariaDB [base]> CREATE TABLE `document`
-> ( `id` integer unsigned NOT NULL PRIMARY KEY,
-> `codification` text NOT NULL,
-> `intitule` text NOT NULL,
-> `commentaire` text NULL,
-> fulltext key `ft1` (`codification`),
-> fulltext key `ft2` (`intitule`),
-> fulltext key `ft3` (`commentaire`)
-> ) ENGINE=InnoDB
-> DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
-> ROW_FORMAT=COMPRESSED;
Query OK, 0 rows affected, 2 warnings (0.07 sec)
MariaDB [base]> INSERT INTO `document` (`id`,`codification`,`intitule`,`commentaire`) VALUES
-> ( 45, 'HABT-COMX-G001', 'Logo HABITATS Couleur Jpeg', NULL),
-> ( 69, 'IME0-COM G001', 'Plan d''accès IME', NULL),
-> (127, 'SAMO COM-G001', 'Plan d''accès SESSAD 1 com', NULL),
-> (128, 'SAMO COM-G002', 'Plan d''accès SESSAD 2', NULL),
-> (155, 'ASSO COM-G001', 'Plaquette', 'com'),
-> (156, 'ASSO COM-G002', 'Plan d''accès', 'comm'),
-> (157, 'ASSO COM-G003', 'Plan du site', 'xcomx'),
-> (158, 'ASSO COM-G004', 'Logo Couleur JPEG', '-COM-'),
-> (159, 'ASSO COMM-G005', 'Logo Couleur GIF', NULL),
-> (160, 'ASSO COM-G006', 'Logo Noir et Blanc JPEG communication', NULL);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
MariaDB [base]> select * from document;
+-----+----------------+---------------------------------------+-------------+
| id | codification | intitule | commentaire |
+-----+----------------+---------------------------------------+-------------+
| 45 | HABT-COMX-G001 | Logo HABITATS Couleur Jpeg | NULL |
| 69 | IME0-COM G001 | Plan d'accès IME | NULL |
| 127 | SAMO COM-G001 | Plan d'accès SESSAD 1 com | NULL |
| 128 | SAMO COM-G002 | Plan d'accès SESSAD 2 | NULL |
| 155 | ASSO COM-G001 | Plaquette | com |
| 156 | ASSO COM-G002 | Plan d'accès | comm |
| 157 | ASSO COM-G003 | Plan du site | xcomx |
| 158 | ASSO COM-G004 | Logo Couleur JPEG | -COM- |
| 159 | ASSO COMM-G005 | Logo Couleur GIF | NULL |
| 160 | ASSO COM-G006 | Logo Noir et Blanc JPEG communication | NULL |
+-----+----------------+---------------------------------------+-------------+
10 rows in set (0.00 sec)
MariaDB [base]> show variables like "%version%";
+-------------------------+--------------------------+
| Variable_name | Value |
+-------------------------+--------------------------+
| innodb_version | 5.6.41-84.1 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 10.1.37-MariaDB-0+deb9u1 |
| version_comment | Debian 9.6 |
| version_compile_machine | x86_64 |
| version_compile_os | debian-linux-gnu |
| version_malloc_library | system jemalloc |
| version_ssl_library | YaSSL 2.4.4 |
| wsrep_patch_version | wsrep_25.23 |
+-------------------------+--------------------------+
10 rows in set (0.00 sec)
MariaDB [base]> show variables like 'innodb_ft%';
+---------------------------------+------------+
| Variable_name | Value |
+---------------------------------+------------+
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | ON |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_ft_user_stopword_table | |
+---------------------------------+------------+
12 rows in set (0.00 sec)
MariaDB [base]> SELECT *,
-> MATCH(`codification`) AGAINST ('com*' IN BOOLEAN MODE) AS score_codification,
-> MATCH(`intitule`) AGAINST ('com*' IN BOOLEAN MODE) AS score_intitule,
-> MATCH(`commentaire`) AGAINST ('com*' IN BOOLEAN MODE) AS score_commentaire
-> FROM `document`;
+-----+----------------+---------------------------------------+-------------+--------------------+----------------+-------------------+
| id | codification | intitule | commentaire | score_codification | score_intitule | score_commentaire |
+-----+----------------+---------------------------------------+-------------+--------------------+----------------+-------------------+
| 45 | HABT-COMX-G001 | Logo HABITATS Couleur Jpeg | NULL | 0.4885590672492981 | 0 | 0 |
| 69 | IME0-COM G001 | Plan d'accès IME | NULL | 0 | 0 | 0 |
| 127 | SAMO COM-G001 | Plan d'accès SESSAD 1 com | NULL | 0 | 0 | 0 |
| 128 | SAMO COM-G002 | Plan d'accès SESSAD 2 | NULL | 0 | 0 | 0 |
| 155 | ASSO COM-G001 | Plaquette | com | 0 | 0 | 0 |
| 156 | ASSO COM-G002 | Plan d'accès | comm | 0 | 0 | 1 |
| 157 | ASSO COM-G003 | Plan du site | xcomx | 0 | 0 | 0 |
| 158 | ASSO COM-G004 | Logo Couleur JPEG | -COM- | 0 | 0 | 0 |
| 159 | ASSO COMM-G005 | Logo Couleur GIF | NULL | 0.4885590672492981 | 0 | 0 |
| 160 | ASSO COM-G006 | Logo Noir et Blanc JPEG communication | NULL | 0 | 1 | 0 |
+-----+----------------+---------------------------------------+-------------+--------------------+----------------+-------------------+
10 rows in set (0.00 sec) |
Score incohérent avec une recherche sur des index fulltext
Répondre avec citation
de mon aide, vous pouvez cliquer sur 
.
Envoyé par Tchupacabra
Partager