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
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
|
###################################################
# Test the performance for InnoDB FTS Phrase Search
###################################################
--source include/big_test.inc
--source include/have_nodebug.inc
--source include/not_solaris.inc
--source include/not_asan.inc
--source include/not_valgrind.inc
CREATE TABLE `t` (
`id` INT NOT NULL AUTO_INCREMENT,
`a` VARCHAR(90),
`b` INT DEFAULT 1,
PRIMARY KEY (`id`),
FULLTEXT KEY (`a`)
) ENGINE=InnoDB;
DELIMITER |;
CREATE PROCEDURE expand_data(repeat_count INT)
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < repeat_count DO
INSERT INTO t (a)
SELECT a FROM t;
SET i = i + 1;
END WHILE;
END |
DELIMITER ;|
INSERT INTO t (a) VALUES
('amet consectetur adipiscing lorem ipsum'),
('amet consectetur adipiscing ipsum lorem'),
('amet consectetur lorem adipiscing ipsum'),
('amet consectetur lorem ipsum adipiscing'),
('amet consectetur ipsum adipiscing lorem'),
('amet consectetur ipsum lorem adipiscing'),
('amet adipiscing consectetur lorem ipsum'),
('amet adipiscing consectetur ipsum lorem'),
('amet adipiscing lorem consectetur ipsum'),
('amet adipiscing lorem ipsum consectetur'),
('amet adipiscing ipsum consectetur lorem'),
('amet adipiscing ipsum lorem consectetur'),
('amet lorem consectetur adipiscing ipsum'),
('amet lorem consectetur ipsum adipiscing'),
('amet lorem adipiscing consectetur ipsum'),
('amet lorem adipiscing ipsum consectetur'),
('amet lorem ipsum consectetur adipiscing'),
('amet lorem ipsum adipiscing consectetur'),
('amet ipsum consectetur adipiscing lorem'),
('amet ipsum consectetur lorem adipiscing'),
('amet ipsum adipiscing consectetur lorem'),
('amet ipsum adipiscing lorem consectetur'),
('amet ipsum lorem consectetur adipiscing'),
('amet ipsum lorem adipiscing consectetur'),
('consectetur amet adipiscing lorem ipsum'),
('consectetur amet adipiscing ipsum lorem'),
('consectetur amet lorem adipiscing ipsum'),
('consectetur amet lorem ipsum adipiscing'),
('consectetur amet ipsum adipiscing lorem'),
('consectetur amet ipsum lorem adipiscing'),
('consectetur adipiscing amet lorem ipsum'),
('consectetur adipiscing amet ipsum lorem'),
('consectetur adipiscing lorem amet ipsum'),
('consectetur adipiscing lorem ipsum amet'),
('consectetur adipiscing ipsum amet lorem'),
('consectetur adipiscing ipsum lorem amet'),
('consectetur lorem amet adipiscing ipsum'),
('consectetur lorem amet ipsum adipiscing'),
('consectetur lorem adipiscing amet ipsum'),
('consectetur lorem adipiscing ipsum amet'),
('consectetur lorem ipsum amet adipiscing'),
('consectetur lorem ipsum adipiscing amet'),
('consectetur ipsum amet adipiscing lorem'),
('consectetur ipsum amet lorem adipiscing'),
('consectetur ipsum adipiscing amet lorem'),
('consectetur ipsum adipiscing lorem amet'),
('consectetur ipsum lorem amet adipiscing'),
('consectetur ipsum lorem adipiscing amet'),
('adipiscing amet consectetur lorem ipsum'),
('adipiscing amet consectetur ipsum lorem'),
('adipiscing amet lorem consectetur ipsum'),
('adipiscing amet lorem ipsum consectetur'),
('adipiscing amet ipsum consectetur lorem'),
('adipiscing amet ipsum lorem consectetur'),
('adipiscing consectetur amet lorem ipsum'),
('adipiscing consectetur amet ipsum lorem'),
('adipiscing consectetur lorem amet ipsum'),
('adipiscing consectetur lorem ipsum amet'),
('adipiscing consectetur ipsum amet lorem'),
('adipiscing consectetur ipsum lorem amet'),
('adipiscing lorem amet consectetur ipsum'),
('adipiscing lorem amet ipsum consectetur'),
('adipiscing lorem consectetur amet ipsum'),
('adipiscing lorem consectetur ipsum amet'),
('adipiscing lorem ipsum amet consectetur'),
('adipiscing lorem ipsum consectetur amet'),
('adipiscing ipsum amet consectetur lorem'),
('adipiscing ipsum amet lorem consectetur'),
('adipiscing ipsum consectetur amet lorem'),
('adipiscing ipsum consectetur lorem amet'),
('adipiscing ipsum lorem amet consectetur'),
('adipiscing ipsum lorem consectetur amet'),
('lorem amet consectetur adipiscing ipsum'),
('lorem amet consectetur ipsum adipiscing'),
('lorem amet adipiscing consectetur ipsum'),
('lorem amet adipiscing ipsum consectetur'),
('lorem amet ipsum consectetur adipiscing'),
('lorem amet ipsum adipiscing consectetur'),
('lorem consectetur amet adipiscing ipsum'),
('lorem consectetur amet ipsum adipiscing'),
('lorem consectetur adipiscing amet ipsum'),
('lorem consectetur adipiscing ipsum amet'),
('lorem consectetur ipsum amet adipiscing'),
('lorem consectetur ipsum adipiscing amet'),
('lorem adipiscing amet consectetur ipsum'),
('lorem adipiscing amet ipsum consectetur'),
('lorem adipiscing consectetur amet ipsum'),
('lorem adipiscing consectetur ipsum amet'),
('lorem adipiscing ipsum amet consectetur'),
('lorem adipiscing ipsum consectetur amet'),
('lorem ipsum amet consectetur adipiscing'),
('lorem ipsum amet adipiscing consectetur'),
('lorem ipsum consectetur amet adipiscing'),
('lorem ipsum consectetur adipiscing amet'),
('lorem ipsum adipiscing amet consectetur'),
('lorem ipsum adipiscing consectetur amet'),
('ipsum amet consectetur adipiscing lorem'),
('ipsum amet consectetur lorem adipiscing'),
('ipsum amet adipiscing consectetur lorem'),
('ipsum amet adipiscing lorem consectetur'),
('ipsum amet lorem consectetur adipiscing'),
('ipsum amet lorem adipiscing consectetur'),
('ipsum consectetur amet adipiscing lorem'),
('ipsum consectetur amet lorem adipiscing'),
('ipsum consectetur adipiscing amet lorem'),
('ipsum consectetur adipiscing lorem amet'),
('ipsum consectetur lorem amet adipiscing'),
('ipsum consectetur lorem adipiscing amet'),
('ipsum adipiscing amet consectetur lorem'),
('ipsum adipiscing amet lorem consectetur'),
('ipsum adipiscing consectetur amet lorem'),
('ipsum adipiscing consectetur lorem amet'),
('ipsum adipiscing lorem amet consectetur'),
('ipsum adipiscing lorem consectetur amet'),
('ipsum lorem amet consectetur adipiscing'),
('ipsum lorem amet adipiscing consectetur'),
('ipsum lorem consectetur amet adipiscing'),
('ipsum lorem consectetur adipiscing amet'),
('ipsum lorem adipiscing amet consectetur'),
('ipsum lorem adipiscing consectetur amet');
INSERT INTO t (a) VALUES
('amet consectetur adipiscing lorem'),
('amet consectetur adipiscing ipsum'),
('amet consectetur lorem adipiscing'),
('amet consectetur lorem ipsum'),
('amet consectetur ipsum adipiscing'),
('amet consectetur ipsum lorem'),
('amet adipiscing consectetur lorem'),
('amet adipiscing consectetur ipsum'),
('amet adipiscing lorem consectetur'),
('amet adipiscing lorem ipsum'),
('amet adipiscing ipsum consectetur'),
('amet adipiscing ipsum lorem'),
('amet lorem consectetur adipiscing'),
('amet lorem consectetur ipsum'),
('amet lorem adipiscing consectetur'),
('amet lorem adipiscing ipsum'),
('amet lorem ipsum consectetur'),
('amet lorem ipsum adipiscing'),
('amet ipsum consectetur adipiscing'),
('amet ipsum consectetur lorem'),
('amet ipsum adipiscing consectetur'),
('amet ipsum adipiscing lorem'),
('amet ipsum lorem consectetur'),
('amet ipsum lorem adipiscing'),
('consectetur amet adipiscing lorem'),
('consectetur amet adipiscing ipsum'),
('consectetur amet lorem adipiscing'),
('consectetur amet lorem ipsum'),
('consectetur amet ipsum adipiscing'),
('consectetur amet ipsum lorem'),
('consectetur adipiscing amet lorem'),
('consectetur adipiscing amet ipsum'),
('consectetur adipiscing lorem amet'),
('consectetur adipiscing lorem ipsum'),
('consectetur adipiscing ipsum amet'),
('consectetur adipiscing ipsum lorem'),
('consectetur lorem amet adipiscing'),
('consectetur lorem amet ipsum'),
('consectetur lorem adipiscing amet'),
('consectetur lorem adipiscing ipsum'),
('consectetur lorem ipsum amet'),
('consectetur lorem ipsum adipiscing'),
('consectetur ipsum amet adipiscing'),
('consectetur ipsum amet lorem'),
('consectetur ipsum adipiscing amet'),
('consectetur ipsum adipiscing lorem'),
('consectetur ipsum lorem amet'),
('consectetur ipsum lorem adipiscing'),
('adipiscing amet consectetur lorem'),
('adipiscing amet consectetur ipsum'),
('adipiscing amet lorem consectetur'),
('adipiscing amet lorem ipsum'),
('adipiscing amet ipsum consectetur'),
('adipiscing amet ipsum lorem'),
('adipiscing consectetur amet lorem'),
('adipiscing consectetur amet ipsum'),
('adipiscing consectetur lorem amet'),
('adipiscing consectetur lorem ipsum'),
('adipiscing consectetur ipsum amet'),
('adipiscing consectetur ipsum lorem'),
('adipiscing lorem amet consectetur'),
('adipiscing lorem amet ipsum'),
('adipiscing lorem consectetur amet'),
('adipiscing lorem consectetur ipsum'),
('adipiscing lorem ipsum amet'),
('adipiscing lorem ipsum consectetur'),
('adipiscing ipsum amet consectetur'),
('adipiscing ipsum amet lorem'),
('adipiscing ipsum consectetur amet'),
('adipiscing ipsum consectetur lorem'),
('adipiscing ipsum lorem amet'),
('adipiscing ipsum lorem consectetur'),
('lorem amet consectetur adipiscing'),
('lorem amet consectetur ipsum'),
('lorem amet adipiscing consectetur'),
('lorem amet adipiscing ipsum'),
('lorem amet ipsum consectetur'),
('lorem amet ipsum adipiscing'),
('lorem consectetur amet adipiscing'),
('lorem consectetur amet ipsum'),
('lorem consectetur adipiscing amet'),
('lorem consectetur adipiscing ipsum'),
('lorem consectetur ipsum amet'),
('lorem consectetur ipsum adipiscing'),
('lorem adipiscing amet consectetur'),
('lorem adipiscing amet ipsum'),
('lorem adipiscing consectetur amet'),
('lorem adipiscing consectetur ipsum'),
('lorem adipiscing ipsum amet'),
('lorem adipiscing ipsum consectetur'),
('lorem ipsum amet consectetur'),
('lorem ipsum amet adipiscing'),
('lorem ipsum consectetur amet'),
('lorem ipsum consectetur adipiscing'),
('lorem ipsum adipiscing amet'),
('lorem ipsum adipiscing consectetur'),
('ipsum amet consectetur adipiscing'),
('ipsum amet consectetur lorem'),
('ipsum amet adipiscing consectetur'),
('ipsum amet adipiscing lorem'),
('ipsum amet lorem consectetur'),
('ipsum amet lorem adipiscing'),
('ipsum consectetur amet adipiscing'),
('ipsum consectetur amet lorem'),
('ipsum consectetur adipiscing amet'),
('ipsum consectetur adipiscing lorem'),
('ipsum consectetur lorem amet'),
('ipsum consectetur lorem adipiscing'),
('ipsum adipiscing amet consectetur'),
('ipsum adipiscing amet lorem'),
('ipsum adipiscing consectetur amet'),
('ipsum adipiscing consectetur lorem'),
('ipsum adipiscing lorem amet'),
('ipsum adipiscing lorem consectetur');
INSERT INTO t (a) VALUES
('amet consectetur adipiscing'),
('amet consectetur lorem'),
('amet consectetur ipsum'),
('amet adipiscing consectetur'),
('amet adipiscing lorem'),
('amet adipiscing ipsum'),
('amet lorem consectetur'),
('amet lorem adipiscing'),
('amet lorem ipsum'),
('amet ipsum consectetur'),
('amet ipsum adipiscing'),
('amet ipsum lorem'),
('consectetur amet adipiscing'),
('consectetur amet lorem'),
('consectetur amet ipsum'),
('consectetur adipiscing amet'),
('consectetur adipiscing lorem'),
('consectetur adipiscing ipsum'),
('consectetur lorem amet'),
('consectetur lorem adipiscing'),
('consectetur lorem ipsum'),
('consectetur ipsum amet'),
('consectetur ipsum adipiscing'),
('consectetur ipsum lorem'),
('adipiscing amet consectetur'),
('adipiscing amet lorem'),
('adipiscing amet ipsum'),
('adipiscing consectetur amet'),
('adipiscing consectetur lorem'),
('adipiscing consectetur ipsum'),
('adipiscing lorem amet'),
('adipiscing lorem consectetur'),
('adipiscing lorem ipsum'),
('adipiscing ipsum amet'),
('adipiscing ipsum consectetur'),
('adipiscing ipsum lorem'),
('lorem amet consectetur'),
('lorem amet adipiscing'),
('lorem amet ipsum'),
('lorem consectetur amet'),
('lorem consectetur adipiscing'),
('lorem consectetur ipsum'),
('lorem adipiscing amet'),
('lorem adipiscing consectetur'),
('lorem adipiscing ipsum'),
('lorem ipsum amet'),
('lorem ipsum consectetur'),
('lorem ipsum adipiscing'),
('ipsum amet consectetur'),
('ipsum amet adipiscing'),
('ipsum amet lorem'),
('ipsum consectetur amet'),
('ipsum consectetur adipiscing'),
('ipsum consectetur lorem'),
('ipsum adipiscing amet'),
('ipsum adipiscing consectetur'),
('ipsum adipiscing lorem'),
('ipsum lorem amet'),
('ipsum lorem consectetur'),
('ipsum lorem adipiscing');
INSERT INTO t (a) VALUES
('amet consectetur'),
('amet adipiscing'),
('amet lorem'),
('amet ipsum'),
('consectetur amet'),
('consectetur adipiscing'),
('consectetur lorem'),
('consectetur ipsum'),
('adipiscing amet'),
('adipiscing consectetur'),
('adipiscing lorem'),
('adipiscing ipsum'),
('lorem amet'),
('lorem consectetur'),
('lorem adipiscing'),
('lorem ipsum'),
('ipsum amet'),
('ipsum consectetur'),
('ipsum adipiscing'),
('ipsum lorem');
INSERT INTO t (a) VALUES
('amet'),
('consectetur'),
('adipiscing'),
('lorem'),
('ipsum');
CALL expand_data(11);
SELECT COUNT(*) FROM t;
SET SESSION max_execution_time=30000;
# Below SELECT statements should complete its execution within 30 seconds individually.
SELECT * FROM t WHERE MATCH(a) AGAINST (+'"amet consectetur"' IN BOOLEAN MODE) ORDER BY id LIMIT 1;
SELECT COUNT(*) FROM t WHERE MATCH(a) AGAINST (+'"amet consectetur"' IN BOOLEAN MODE);
SELECT * FROM t WHERE MATCH(a) AGAINST ('"amet consectetur"' IN NATURAL LANGUAGE MODE) ORDER BY id LIMIT 1;
SELECT COUNT(*) FROM t WHERE MATCH(a) AGAINST ('"amet consectetur"' IN NATURAL LANGUAGE MODE);
# Cleanup
DROP PROCEDURE expand_data;
DROP TABLE t;
|
