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
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
|
drop table if exists t1;
CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
a VARCHAR(200),
b TEXT
) ENGINE= InnoDB;
CREATE FULLTEXT INDEX idx on t1 (a,b);
Warnings:
Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
INSERT INTO t1 (a,b) VALUES
('MySQL from Tutorial','DBMS stands for DataBase ...') ,
('when To Use MySQL Well','After that you went through a ...'),
('where will Optimizing MySQL','what In this tutorial we will show ...');
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("where will");
id a b
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("when");
id a b
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("what" WITH QUERY EXPANSION);
id a b
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("whe*" IN BOOLEAN MODE);
id a b
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+what +will" IN BOOLEAN MODE);
id a b
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+from" IN BOOLEAN MODE);
id a b
SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+where +(show what)" IN BOOLEAN MODE);
id a b
SELECT * FROM t1
WHERE MATCH (a,b)
AGAINST ('"where will"@6' IN BOOLEAN MODE);
id a b
SELECT * FROM t1
WHERE MATCH (a,b)
AGAINST ('"where will"@9' IN BOOLEAN MODE);
id a b
INSERT INTO t1 (a,b) VALUES
('MySQL Tutorial','request docteam@oraclehelp.com ...') ,
('Trial version','query performace @1255 minute on 2.1Hz Memory 2GB...') ,
('when To Use MySQL Well','for free faq mail@xyz.com ...');
SELECT * FROM t1
WHERE MATCH (a,b)
AGAINST ('"request"@10' IN BOOLEAN MODE);
id a b
4 MySQL Tutorial request docteam@oraclehelp.com ...
SELECT * FROM t1
WHERE MATCH (a,b)
AGAINST ('"Trial version"@0' IN BOOLEAN MODE);
id a b
5 Trial version query performace @1255 minute on 2.1Hz Memory 2GB...
SELECT * FROM t1
WHERE MATCH (a,b)
AGAINST ('"request docteam@oraclehelp.com"@10' IN BOOLEAN MODE);
id a b
4 MySQL Tutorial request docteam@oraclehelp.com ...
SELECT * FROM t1
WHERE MATCH (a,b)
AGAINST ('"1255 minute"@1' IN BOOLEAN MODE);
id a b
SELECT * FROM t1
WHERE MATCH (a,b)
AGAINST ('"1255 minute"@2' IN BOOLEAN MODE);
id a b
5 Trial version query performace @1255 minute on 2.1Hz Memory 2GB...
SELECT * FROM t1
WHERE MATCH (a,b)
AGAINST ('"1255"@10' IN BOOLEAN MODE);
id a b
5 Trial version query performace @1255 minute on 2.1Hz Memory 2GB...
SELECT * FROM t1
WHERE MATCH (a,b)
AGAINST ('1255' WITH QUERY EXPANSION);
id a b
5 Trial version query performace @1255 minute on 2.1Hz Memory 2GB...
SELECT * FROM t1
WHERE MATCH (a,b)
AGAINST ('"request docteam"@2' IN BOOLEAN MODE);
id a b
4 MySQL Tutorial request docteam@oraclehelp.com ...
SELECT * FROM t1
WHERE MATCH (a,b)
AGAINST ('"1255 minute"' IN BOOLEAN MODE);
id a b
5 Trial version query performace @1255 minute on 2.1Hz Memory 2GB...
SELECT * FROM t1
WHERE MATCH (a,b)
AGAINST ('request docteam@oraclehelp.com');
id a b
4 MySQL Tutorial request docteam@oraclehelp.com ...
SELECT * FROM t1
WHERE MATCH (a,b)
AGAINST ('"MySQL request"@3' IN BOOLEAN MODE);
id a b
4 MySQL Tutorial request docteam@oraclehelp.com ...
SELECT * FROM t1
WHERE MATCH (a,b)
AGAINST ('"Trial memory"@10' IN BOOLEAN MODE);
id a b
5 Trial version query performace @1255 minute on 2.1Hz Memory 2GB...
SELECT * FROM t1
WHERE MATCH (a,b)
AGAINST ('"Trial memory"@9' IN BOOLEAN MODE);
id a b
DROP TABLE t1;
CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
a VARCHAR(200),
b TEXT
) CHARACTER SET = UTF8, ENGINE= InnoDB;
INSERT INTO t1 (a,b) VALUES
('MySQL from Tutorial','DBMS stands for DataBase ...') ,
('when To Use MySQL Well','After that you went through a ...'),
('where will Optimizing MySQL','what In this tutorial we will show ...');
CREATE FULLTEXT INDEX idx on t1 (a,b);
Warnings:
Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
INSERT INTO t1 (a,b) VALUES
('MySQL Tutorial','request docteam@oraclehelp.com ...') ,
('Trial version','query performace @1255 minute on 2.1Hz Memory 2GB...'),
('when To Use MySQL Well','for free faq mail@xyz.com ...');
SELECT * FROM t1
WHERE MATCH (a,b)
AGAINST ('"mysql use"@2' IN BOOLEAN MODE);
id a b
2 when To Use MySQL Well After that you went through a ...
6 when To Use MySQL Well for free faq mail@xyz.com ...
SELECT * FROM t1
WHERE MATCH (a,b)
AGAINST ('"mysql use"@1' IN BOOLEAN MODE);
id a b
INSERT INTO t1 (a,b) VALUES ('XYZ, long blob', repeat("a", 9000));
INSERT INTO t1 (a,b) VALUES (repeat("b", 9000), 'XYZ, long blob');
Warnings:
Warning 1265 Data truncated for column 'a' at row 1
SELECT count(*) FROM t1
WHERE MATCH (a,b)
AGAINST ('"xyz blob"@3' IN BOOLEAN MODE);
count(*)
2
DROP TABLE t1;
set global innodb_file_format="Barracuda";
set global innodb_file_per_table=1;
CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
a TEXT,
b TEXT,
c TEXT
) CHARACTER SET = UTF8, ROW_FORMAT=DYNAMIC, ENGINE= InnoDB;
INSERT INTO t1 (a,b,c) VALUES (repeat("b", 19000), 'XYZ, long text', 'very long blob');
INSERT INTO t1 (a,b,c) VALUES (repeat("b", 19000), 'XYZ, very little long blob very much blob', 'very long blob');
INSERT INTO t1 (a,b,c) VALUES (repeat("b", 19000),"very 租车 供 blob","new 供需分析information");
CREATE FULLTEXT INDEX idx on t1 (a,b,c);
Warnings:
Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
INSERT INTO t1 (a,b,c) VALUES (repeat("x", 19000), 'new, long text', 'very new blob');
INSERT INTO t1 (a,b,c) VALUES ('interesting, long text', repeat("x", 19000), 'very very good new blob');
SELECT count(*) FROM t1
WHERE MATCH (a,b,c)
AGAINST ('"very blob"@3' IN BOOLEAN MODE);
count(*)
3
SELECT count(*) FROM t1
WHERE MATCH (a,b,c)
AGAINST ('"very long blob"@0' IN BOOLEAN MODE);
count(*)
2
SELECT count(*) FROM t1
WHERE MATCH (a,b,c)
AGAINST ('"very blob"@4' IN BOOLEAN MODE);
count(*)
4
SELECT count(*) FROM t1
WHERE MATCH (a,b,c)
AGAINST ('"interesting blob"@9' IN BOOLEAN MODE);
count(*)
1
SELECT COUNT(*) FROM t1
WHERE MATCH (a,b,c)
AGAINST ('"interesting blob"@9 "very long blob"@0' IN BOOLEAN MODE);
COUNT(*)
3
SELECT COUNT(*) FROM t1
WHERE MATCH (a,b,c)
AGAINST ('"very blob"@4 - "interesting blob"@9' IN BOOLEAN MODE);
COUNT(*)
3
DROP TABLE t1;
CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
a VARCHAR(200),
b TEXT
) CHARACTER SET = UTF8, ENGINE= InnoDB;
INSERT INTO t1 (a,b) VALUES
('MySQL from Tutorial','DBMS stands for + DataBase ...');
CREATE FULLTEXT INDEX idx on t1 (a,b);
Warnings:
Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
SELECT * FROM t1
WHERE MATCH (a,b)
AGAINST ('"stands database"@3' IN BOOLEAN MODE);
id a b
1 MySQL from Tutorial DBMS stands for + DataBase ...
DROP TABLE t1;
CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
a TEXT,
b TEXT,
c TEXT
) CHARACTER SET = UTF8, ROW_FORMAT=DYNAMIC, ENGINE= InnoDB;
INSERT INTO t1 (a,b,c) VALUES (repeat("b", 19000), 'XYZ, long text', 'very long blob');
INSERT INTO t1 (a,b,c) VALUES ('XYZ, 租车 very little long blob very much blob', repeat("b", 19000), 'very long but smaller blob');
CREATE FULLTEXT INDEX idx on t1 (a,b,c);
Warnings:
Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
DELETE FROM t1;
INSERT INTO t1 (a,b,c) VALUES (repeat("b", 19000), 'XYZ, long text', 'very long blob');
INSERT INTO t1 (a,b,c) VALUES ('XYZ, 租车 very little long blob is a very much longer blob', repeat("b", 19000), 'this is very long but smaller blob');
SELECT count(*) FROM t1
WHERE MATCH (a,b,c)
AGAINST ('"very blob"@4' IN BOOLEAN MODE);
count(*)
2
SELECT count(*) FROM t1
WHERE MATCH (a,b,c)
AGAINST ('"very blob"@3' IN BOOLEAN MODE);
count(*)
1
DROP TABLE t1;
SET GLOBAL innodb_file_format=Antelope;
SET GLOBAL innodb_file_per_table=1;
|
