Debian Codegraph @

mysql-test/suite/innodb_fts/r/opt.result

raw
1 CREATE TABLE wp(
2 FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
3 title VARCHAR(255) NOT NULL DEFAULT '',
4 text MEDIUMTEXT NOT NULL,
5 dummy INTEGER,
6 PRIMARY KEY (FTS_DOC_ID),
7 UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
8 FULLTEXT KEY idx (title,text)
9 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
10 Warnings:
11 Warning 1681 Integer display width is deprecated and will be removed in a future release.
12 INSERT INTO wp (title, text) VALUES
13 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
14 ('How To Use MySQL Well','After you went through a ...'),
15 ('Optimizing MySQL','In this tutorial we will show ...'),
16 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
17 ('MySQL vs. YourSQL','In the following database to database comparison ...'),
18 ('MySQL Security','When configured properly, MySQL ...');
19 CREATE TABLE t1 (i INTEGER);
20 INSERT INTO t1 SELECT FTS_DOC_ID FROM wp;
21 ANALYZE TABLE t1;
22 ANALYZE TABLE wp;
23 SELECT FTS_DOC_ID, title, MATCH(title, text) AGAINST ('database') AS score1,
24 MATCH(title, text) AGAINST ('mysql') AS score2
25 FROM wp;
26 FTS_DOC_ID title score1 score2
27 1 MySQL Tutorial 0.22764469683170319 0.000000003771856604828372
28 2 How To Use MySQL Well 0 0.000000001885928302414186
29 3 Optimizing MySQL 0 0.000000001885928302414186
30 4 1001 MySQL Tricks 0 0.000000001885928302414186
31 5 MySQL vs. YourSQL 0.45528939366340637 0.000000001885928302414186
32 6 MySQL Security 0 0.000000003771856604828372
33 No sorting for this query
34 FLUSH STATUS;
35 SELECT MATCH(title, text) AGAINST ('database') AS score, title
36 FROM wp
37 WHERE MATCH(title, text) AGAINST ('database')
38 ORDER BY score DESC;
39 score title
40 0.45528939366340637 MySQL vs. YourSQL
41 0.22764469683170319 MySQL Tutorial
42 SHOW SESSION STATUS LIKE 'Sort%';
43 Variable_name Value
44 Sort_merge_passes 0
45 Sort_range 0
46 Sort_rows 0
47 Sort_scan 0
48 No sorting for this query even if MATCH is part of an expression
49 FLUSH STATUS;
50 SELECT MATCH(title, text) AGAINST ('database') AS score, title
51 FROM wp
52 WHERE MATCH(title, text) AGAINST ('database') > 0.1
53 ORDER BY score DESC;
54 score title
55 0.45528939366340637 MySQL vs. YourSQL
56 0.22764469683170319 MySQL Tutorial
57 SHOW SESSION STATUS LIKE 'Sort%';
58 Variable_name Value
59 Sort_merge_passes 0
60 Sort_range 0
61 Sort_rows 0
62 Sort_scan 0
63 No sorting even if there are several MATCH expressions as long as the
64 right one is used in ORDER BY
65 FLUSH STATUS;
66 SELECT MATCH(title, text) AGAINST ('database') AS score1,
67 MATCH(title, text) AGAINST ('mysql') AS score2,
68 title
69 FROM wp
70 WHERE MATCH(title, text) AGAINST ('database')
71 ORDER BY score1 DESC;
72 score1 score2 title
73 0.45528939366340637 0.000000001885928302414186 MySQL vs. YourSQL
74 0.22764469683170319 0.000000003771856604828372 MySQL Tutorial
75 SHOW SESSION STATUS LIKE 'Sort%';
76 Variable_name Value
77 Sort_merge_passes 0
78 Sort_range 0
79 Sort_rows 0
80 Sort_scan 0
81 No Sorting since FT table is first table in query
82 FLUSH STATUS;
83 SELECT MATCH(title, text) AGAINST ('database') AS score, title
84 FROM wp, t1
85 WHERE MATCH(title, text) AGAINST ('database') AND FTS_DOC_ID = t1.i
86 ORDER BY score DESC;
87 score title
88 0.45528939366340637 MySQL vs. YourSQL
89 0.22764469683170319 MySQL Tutorial
90 SHOW SESSION STATUS LIKE 'Sort_rows%';
91 Variable_name Value
92 Sort_rows 0
93 Sorting since there is no WHERE clause
94 FLUSH STATUS;
95 SELECT MATCH(title, text) AGAINST ('database') AS score, title
96 FROM wp
97 ORDER BY score DESC;
98 score title
99 0.45528939366340637 MySQL vs. YourSQL
100 0.22764469683170319 MySQL Tutorial
101 0 1001 MySQL Tricks
102 0 How To Use MySQL Well
103 0 MySQL Security
104 0 Optimizing MySQL
105 SHOW SESSION STATUS LIKE 'Sort_rows%';
106 Variable_name Value
107 Sort_rows 6
108 Sorting since ordering on multiple columns
109 FLUSH STATUS;
110 SELECT title, MATCH(title, text) AGAINST ('database') AS score
111 FROM wp
112 WHERE MATCH(title, text) AGAINST ('database')
113 ORDER BY score DESC, FTS_DOC_ID;
114 title score
115 MySQL vs. YourSQL 0.45528939366340637
116 MySQL Tutorial 0.22764469683170319
117 SHOW SESSION STATUS LIKE 'Sort_rows%';
118 Variable_name Value
119 Sort_rows 2
120 Sorting since ordering is not descending
121 FLUSH STATUS;
122 SELECT title, MATCH(title, text) AGAINST ('database') AS score
123 FROM wp
124 WHERE MATCH(title, text) AGAINST ('database')
125 ORDER BY score ASC;
126 title score
127 MySQL Tutorial 0.22764469683170319
128 MySQL vs. YourSQL 0.45528939366340637
129 SHOW SESSION STATUS LIKE 'Sort_rows%';
130 Variable_name Value
131 Sort_rows 2
132 Sorting because one is ordering on a different MATCH expression
133 FLUSH STATUS;
134 SELECT MATCH(title, text) AGAINST ('mysql') AS score, title
135 FROM wp
136 WHERE MATCH(title, text) AGAINST ('database')
137 ORDER BY score DESC;
138 score title
139 0.000000003771856604828372 MySQL Tutorial
140 0.000000001885928302414186 MySQL vs. YourSQL
141 SHOW SESSION STATUS LIKE 'Sort_rows%';
142 Variable_name Value
143 Sort_rows 2
144 No sorting for this query
145 FLUSH STATUS;
146 SELECT title, MATCH(title, text) AGAINST ('database') AS score
147 FROM wp
148 ORDER BY score DESC LIMIT 2;
149 title score
150 MySQL vs. YourSQL 0.45528939366340637
151 MySQL Tutorial 0.22764469683170319
152 SHOW SESSION STATUS LIKE 'Sort%';
153 Variable_name Value
154 Sort_merge_passes 0
155 Sort_range 0
156 Sort_rows 0
157 Sort_scan 0
158 Revert to table scan and sorting for this query since not
159 enough matching rows to satisfy LIMIT clause
160 FLUSH STATUS;
161 SELECT title, MATCH(title, text) AGAINST ('database') AS score
162 FROM wp
163 ORDER BY score DESC LIMIT 3;
164 title score
165 MySQL vs. YourSQL 0.45528939366340637
166 MySQL Tutorial 0.22764469683170319
167 How To Use MySQL Well 0
168 SHOW SESSION STATUS LIKE 'Handler_read%';
169 Variable_name Value
170 Handler_read_first 1
171 Handler_read_key 4
172 Handler_read_last 0
173 Handler_read_next 0
174 Handler_read_prev 0
175 Handler_read_rnd 3
176 Handler_read_rnd_next 7
177 SHOW SESSION STATUS LIKE 'Sort_rows%';
178 Variable_name Value
179 Sort_rows 3
180 Sorting since no LIMIT clause
181 FLUSH STATUS;
182 SELECT title, MATCH(title, text) AGAINST ('database') AS score
183 FROM wp
184 ORDER BY score DESC;
185 title score
186 MySQL vs. YourSQL 0.45528939366340637
187 MySQL Tutorial 0.22764469683170319
188 How To Use MySQL Well 0
189 Optimizing MySQL 0
190 1001 MySQL Tricks 0
191 MySQL Security 0
192 SHOW SESSION STATUS LIKE 'Sort_rows%';
193 Variable_name Value
194 Sort_rows 6
195 Sorting since there is a WHERE clause
196 FLUSH STATUS;
197 SELECT title, MATCH(title, text) AGAINST ('database') AS score
198 FROM wp
199 WHERE dummy IS NULL
200 ORDER BY score DESC LIMIT 2;
201 title score
202 MySQL vs. YourSQL 0.45528939366340637
203 MySQL Tutorial 0.22764469683170319
204 SHOW SESSION STATUS LIKE 'Sort_rows%';
205 Variable_name Value
206 Sort_rows 2
207 Sorting since ordering is not on a simple MATCH expressions
208 FLUSH STATUS;
209 SELECT title, (MATCH(title, text) AGAINST ('database')) * 100 AS score
210 FROM wp
211 ORDER BY score DESC LIMIT 2;
212 title score
213 MySQL vs. YourSQL 45.52893936634064
214 MySQL Tutorial 22.76446968317032
215 SHOW SESSION STATUS LIKE 'Sort_rows%';
216 Variable_name Value
217 Sort_rows 2
218 No ordinary handler accesses when only accessing FTS_DOC_ID and MATCH
219 FLUSH STATUS;
220 SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
221 FROM wp
222 WHERE MATCH(title, text) AGAINST ('database');
223 docid score
224 1 0.22764469683170319
225 5 0.45528939366340637
226 SHOW SESSION STATUS LIKE 'Handler_read%';
227 Variable_name Value
228 Handler_read_first 0
229 Handler_read_key 0
230 Handler_read_last 0
231 Handler_read_next 0
232 Handler_read_prev 0
233 Handler_read_rnd 0
234 Handler_read_rnd_next 0
235 Still no handler accesses when adding FTS_DOC_ID to WHERE clause
236 FLUSH STATUS;
237 SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
238 FROM wp
239 WHERE MATCH(title, text) AGAINST ('database') AND FTS_DOC_ID > 2;
240 docid score
241 5 0.45528939366340637
242 SHOW SESSION STATUS LIKE 'Handler_read%';
243 Variable_name Value
244 Handler_read_first 0
245 Handler_read_key 0
246 Handler_read_last 0
247 Handler_read_next 0
248 Handler_read_prev 0
249 Handler_read_rnd 0
250 Handler_read_rnd_next 0
251 Still no handler accesses when ordering by MATCH expression
252 FLUSH STATUS;
253 SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
254 FROM wp
255 WHERE MATCH(title, text) AGAINST ('database')
256 ORDER BY score;
257 docid score
258 1 0.22764469683170319
259 5 0.45528939366340637
260 SHOW SESSION STATUS LIKE 'Handler_read%';
261 Variable_name Value
262 Handler_read_first 0
263 Handler_read_key 2
264 Handler_read_last 0
265 Handler_read_next 0
266 Handler_read_prev 0
267 Handler_read_rnd 2
268 Handler_read_rnd_next 0
269 Optimization is disabled when ordering on FTS_DOC_ID
270 FLUSH STATUS;
271 SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
272 FROM wp
273 WHERE MATCH(title, text) AGAINST ('database')
274 ORDER BY 1 DESC;
275 docid score
276 5 0.45528939366340637
277 1 0.22764469683170319
278 SHOW SESSION STATUS LIKE 'Handler_read%';
279 Variable_name Value
280 Handler_read_first 0
281 Handler_read_key 2
282 Handler_read_last 0
283 Handler_read_next 0
284 Handler_read_prev 0
285 Handler_read_rnd 2
286 Handler_read_rnd_next 0
287 Optimization also work with several MATCH expressions
288 FLUSH STATUS;
289 SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score1,
290 MATCH(title, text) AGAINST ('mysql') AS score2
291 FROM wp
292 WHERE MATCH(title, text) AGAINST ('database');
293 docid score1 score2
294 1 0.22764469683170319 0.000000003771856604828372
295 5 0.45528939366340637 0.000000001885928302414186
296 SHOW SESSION STATUS LIKE 'Handler_read%';
297 Variable_name Value
298 Handler_read_first 0
299 Handler_read_key 0
300 Handler_read_last 0
301 Handler_read_next 0
302 Handler_read_prev 0
303 Handler_read_rnd 0
304 Handler_read_rnd_next 0
305 Optimization does not apply if sorting on a different MATCH expressions
306 from the one used to access the
307 FLUSH STATUS;
308 SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score1,
309 MATCH(title, text) AGAINST ('mysql') AS score2
310 FROM wp
311 WHERE MATCH(title, text) AGAINST ('database')
312 ORDER BY score2 DESC;
313 docid score1 score2
314 1 0.22764469683170319 0.000000003771856604828372
315 5 0.45528939366340637 0.000000001885928302414186
316 SHOW SESSION STATUS LIKE 'Handler_read%';
317 Variable_name Value
318 Handler_read_first 0
319 Handler_read_key 2
320 Handler_read_last 0
321 Handler_read_next 0
322 Handler_read_prev 0
323 Handler_read_rnd 2
324 Handler_read_rnd_next 0
325 FLUSH STATUS;
326 Optimization does not apply for GROUP BY
327 SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('database') AS score
328 FROM wp
329 WHERE MATCH(title, text) AGAINST ('database')
330 GROUP BY score;
331 FTS_DOC_ID score
332 1 0.22764469683170319
333 5 0.45528939366340637
334 SHOW SESSION STATUS LIKE 'Handler_read%';
335 Variable_name Value
336 Handler_read_first 0
337 Handler_read_key 0
338 Handler_read_last 0
339 Handler_read_next 0
340 Handler_read_prev 0
341 Handler_read_rnd 0
342 Handler_read_rnd_next 3
343 No sorting and no table access with LIMIT clause and only information
344 from FTS result
345 FLUSH STATUS;
346 SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
347 FROM wp
348 ORDER BY score DESC LIMIT 2;
349 docid score
350 5 0.45528939366340637
351 1 0.22764469683170319
352 SHOW STATUS LIKE 'Handler_read%';
353 Variable_name Value
354 Handler_read_first 0
355 Handler_read_key 0
356 Handler_read_last 0
357 Handler_read_next 0
358 Handler_read_prev 0
359 Handler_read_rnd 0
360 Handler_read_rnd_next 0
361 SHOW SESSION STATUS LIKE 'Sort%';
362 Variable_name Value
363 Sort_merge_passes 0
364 Sort_range 0
365 Sort_rows 0
366 Sort_scan 0
367 If count optimization applies, EXPLAIN shows
368 "Rows fetched before execution."
369 EXPLAIN FORMAT=TREE SELECT COUNT(*)
370 FROM wp
371 WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
372 EXPLAIN
373 -> Rows fetched before execution (cost=0..0 rows=1)
374
375 FLUSH STATUS;
376 SELECT COUNT(*)
377 FROM wp
378 WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
379 COUNT(*)
380 2
381 Verify that there was no table access
382 SHOW STATUS LIKE 'Handler_read%';
383 Variable_name Value
384 Handler_read_first 0
385 Handler_read_key 0
386 Handler_read_last 0
387 Handler_read_next 0
388 Handler_read_prev 0
389 Handler_read_rnd 0
390 Handler_read_rnd_next 0
391 Optimization applies also to COUNT(expr) as long as expr is not nullable
392 EXPLAIN FORMAT=TREE SELECT COUNT(title)
393 FROM wp
394 WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
395 EXPLAIN
396 -> Rows fetched before execution (cost=0..0 rows=1)
397
398 SELECT COUNT(title)
399 FROM wp
400 WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
401 COUNT(title)
402 2
403 Optimization does not apply if not a single table query.
404 Pattern "-> Aggregate" found
405 SELECT count(*)
406 FROM wp, t1
407 WHERE MATCH(title, text) AGAINST ('database');
408 count(*)
409 12
410 Optimization does not apply if MATCH is part of an expression
411 Pattern "-> Aggregate" found
412 SELECT COUNT(title)
413 FROM wp
414 WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE) > 0;
415 COUNT(title)
416 2
417 Optimization does not apply if MATCH is part of an expression
418 Pattern "-> Aggregate" found
419 SELECT COUNT(title)
420 FROM wp
421 WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE) > 0;
422 COUNT(title)
423 2
424 Optimization does not apply if COUNT expression is nullable
425 Pattern "-> Aggregate" found
426 SELECT COUNT(dummy)
427 FROM wp
428 WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
429 COUNT(dummy)
430 0
431 FLUSH STATUS;
432 SELECT title,
433 MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score
434 FROM wp
435 WHERE MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION)
436 ORDER BY score DESC;
437 title score
438 MySQL vs. YourSQL 2.2718474864959717
439 MySQL Tutorial 1.6663280725479126
440 Optimizing MySQL 0.22764469683170319
441 MySQL Security 0.000000003771856604828372
442 How To Use MySQL Well 0.000000001885928302414186
443 1001 MySQL Tricks 0.000000001885928302414186
444 SHOW SESSION STATUS LIKE 'Sort%';
445 Variable_name Value
446 Sort_merge_passes 0
447 Sort_range 0
448 Sort_rows 0
449 Sort_scan 0
450 FLUSH STATUS;
451 SELECT title,
452 MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score
453 FROM wp
454 ORDER BY score DESC LIMIT 2;
455 title score
456 MySQL vs. YourSQL 2.2718474864959717
457 MySQL Tutorial 1.6663280725479126
458 SHOW SESSION STATUS LIKE 'Sort%';
459 Variable_name Value
460 Sort_merge_passes 0
461 Sort_range 0
462 Sort_rows 0
463 Sort_scan 0
464 FLUSH STATUS;
465 SELECT FTS_DOC_ID docid,
466 MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score
467 FROM wp
468 WHERE MATCH(title, text) AGAINST ('database');
469 docid score
470 1 1.6663280725479126
471 5 2.2718474864959717
472 SHOW SESSION STATUS LIKE 'Handler_read%';
473 Variable_name Value
474 Handler_read_first 0
475 Handler_read_key 0
476 Handler_read_last 0
477 Handler_read_next 0
478 Handler_read_prev 0
479 Handler_read_rnd 0
480 Handler_read_rnd_next 0
481 FLUSH STATUS;
482 SELECT FTS_DOC_ID docid,
483 MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score
484 FROM wp
485 ORDER BY score DESC LIMIT 2;
486 docid score
487 5 2.2718474864959717
488 1 1.6663280725479126
489 SHOW STATUS LIKE 'Handler_read%';
490 Variable_name Value
491 Handler_read_first 0
492 Handler_read_key 0
493 Handler_read_last 0
494 Handler_read_next 0
495 Handler_read_prev 0
496 Handler_read_rnd 0
497 Handler_read_rnd_next 0
498 SHOW SESSION STATUS LIKE 'Sort%';
499 Variable_name Value
500 Sort_merge_passes 0
501 Sort_range 0
502 Sort_rows 0
503 Sort_scan 0
504 EXPLAIN SELECT COUNT(*)
505 FROM wp
506 WHERE MATCH(title,text) AGAINST ('database' WITH QUERY EXPANSION);
507 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
508 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
509 Warnings:
510 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`wp` where (match `test`.`wp`.`title`,`test`.`wp`.`text` against ('database' with query expansion))
511 FLUSH STATUS;
512 SELECT COUNT(*)
513 FROM wp
514 WHERE MATCH(title,text) AGAINST ('database' WITH QUERY EXPANSION);
515 COUNT(*)
516 6
517 SHOW STATUS LIKE 'Handler_read%';
518 Variable_name Value
519 Handler_read_first 0
520 Handler_read_key 0
521 Handler_read_last 0
522 Handler_read_next 0
523 Handler_read_prev 0
524 Handler_read_rnd 0
525 Handler_read_rnd_next 0
526 FLUSH STATUS;
527 SELECT title,
528 MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
529 FROM wp
530 WHERE MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE)
531 ORDER BY score DESC;
532 title score
533 MySQL Security 0.000000003771856604828372
534 How To Use MySQL Well 0.000000001885928302414186
535 Optimizing MySQL 0.000000001885928302414186
536 1001 MySQL Tricks 0.000000001885928302414186
537 SHOW SESSION STATUS LIKE 'Sort%';
538 Variable_name Value
539 Sort_merge_passes 0
540 Sort_range 0
541 Sort_rows 0
542 Sort_scan 0
543 FLUSH STATUS;
544 SELECT title,
545 MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
546 FROM wp
547 ORDER BY score DESC LIMIT 2;
548 title score
549 MySQL Security 0.000000003771856604828372
550 How To Use MySQL Well 0.000000001885928302414186
551 SHOW SESSION STATUS LIKE 'Sort%';
552 Variable_name Value
553 Sort_merge_passes 0
554 Sort_range 0
555 Sort_rows 0
556 Sort_scan 0
557 FLUSH STATUS;
558 SELECT FTS_DOC_ID docid,
559 MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
560 FROM wp
561 WHERE MATCH(title, text) AGAINST ('+MySQL -database');
562 docid score
563 1 0
564 2 0.000000001885928302414186
565 3 0.000000001885928302414186
566 4 0.000000001885928302414186
567 5 0
568 6 0.000000003771856604828372
569 SHOW SESSION STATUS LIKE 'Handler_read%';
570 Variable_name Value
571 Handler_read_first 0
572 Handler_read_key 0
573 Handler_read_last 0
574 Handler_read_next 0
575 Handler_read_prev 0
576 Handler_read_rnd 0
577 Handler_read_rnd_next 0
578 FLUSH STATUS;
579 SELECT FTS_DOC_ID docid,
580 MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
581 FROM wp
582 ORDER BY score DESC LIMIT 2;
583 docid score
584 6 0.000000003771856604828372
585 2 0.000000001885928302414186
586 SHOW STATUS LIKE 'Handler_read%';
587 Variable_name Value
588 Handler_read_first 0
589 Handler_read_key 0
590 Handler_read_last 0
591 Handler_read_next 0
592 Handler_read_prev 0
593 Handler_read_rnd 0
594 Handler_read_rnd_next 0
595 SHOW SESSION STATUS LIKE 'Sort%';
596 Variable_name Value
597 Sort_merge_passes 0
598 Sort_range 0
599 Sort_rows 0
600 Sort_scan 0
601 EXPLAIN SELECT COUNT(*)
602 FROM wp
603 WHERE MATCH(title,text) AGAINST ('+MySQL -database' IN BOOLEAN MODE);
604 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
605 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
606 Warnings:
607 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`wp` where (match `test`.`wp`.`title`,`test`.`wp`.`text` against ('+MySQL -database' in boolean mode))
608 FLUSH STATUS;
609 SELECT COUNT(*)
610 FROM wp
611 WHERE MATCH(title,text) AGAINST ('+MySQL -database' IN BOOLEAN MODE);
612 COUNT(*)
613 4
614 SHOW STATUS LIKE 'Handler_read%';
615 Variable_name Value
616 Handler_read_first 0
617 Handler_read_key 0
618 Handler_read_last 0
619 Handler_read_next 0
620 Handler_read_prev 0
621 Handler_read_rnd 0
622 Handler_read_rnd_next 0
623 FLUSH STATUS;
624 SELECT title,
625 MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score
626 FROM wp
627 WHERE MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE)
628 ORDER BY score DESC;
629 title score
630 MySQL Tutorial 0.22764469683170319
631 SHOW SESSION STATUS LIKE 'Sort%';
632 Variable_name Value
633 Sort_merge_passes 0
634 Sort_range 0
635 Sort_rows 0
636 Sort_scan 0
637 FLUSH STATUS;
638 SELECT title,
639 MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score
640 FROM wp
641 ORDER BY score DESC LIMIT 1;
642 title score
643 MySQL Tutorial 0.22764469683170319
644 SHOW SESSION STATUS LIKE 'Sort%';
645 Variable_name Value
646 Sort_merge_passes 0
647 Sort_range 0
648 Sort_rows 0
649 Sort_scan 0
650 FLUSH STATUS;
651 SELECT FTS_DOC_ID docid,
652 MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score
653 FROM wp
654 WHERE MATCH(title, text) AGAINST ('"MySQL database"@5');
655 docid score
656 1 0.22764469683170319
657 SHOW SESSION STATUS LIKE 'Handler_read%';
658 Variable_name Value
659 Handler_read_first 0
660 Handler_read_key 0
661 Handler_read_last 0
662 Handler_read_next 0
663 Handler_read_prev 0
664 Handler_read_rnd 0
665 Handler_read_rnd_next 0
666 FLUSH STATUS;
667 SELECT FTS_DOC_ID docid,
668 MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score
669 FROM wp
670 ORDER BY score DESC LIMIT 1;
671 docid score
672 1 0.22764469683170319
673 SHOW STATUS LIKE 'Handler_read%';
674 Variable_name Value
675 Handler_read_first 0
676 Handler_read_key 0
677 Handler_read_last 0
678 Handler_read_next 0
679 Handler_read_prev 0
680 Handler_read_rnd 0
681 Handler_read_rnd_next 0
682 SHOW SESSION STATUS LIKE 'Sort%';
683 Variable_name Value
684 Sort_merge_passes 0
685 Sort_range 0
686 Sort_rows 0
687 Sort_scan 0
688 EXPLAIN SELECT COUNT(*)
689 FROM wp
690 WHERE MATCH(title,text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE);
691 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
692 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
693 Warnings:
694 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`wp` where (match `test`.`wp`.`title`,`test`.`wp`.`text` against ('"MySQL database"@5' in boolean mode))
695 FLUSH STATUS;
696 SELECT COUNT(*)
697 FROM wp
698 WHERE MATCH(title,text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE);
699 COUNT(*)
700 1
701 SHOW STATUS LIKE 'Handler_read%';
702 Variable_name Value
703 Handler_read_first 0
704 Handler_read_key 0
705 Handler_read_last 0
706 Handler_read_next 0
707 Handler_read_prev 0
708 Handler_read_rnd 0
709 Handler_read_rnd_next 0
710 SELECT MATCH(title, text) AGAINST ('database') AS score, title
711 FROM wp
712 WHERE MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION)
713 ORDER BY score DESC;
714 score title
715 0.45528939366340637 MySQL vs. YourSQL
716 0.22764469683170319 MySQL Tutorial
717 0 1001 MySQL Tricks
718 0 How To Use MySQL Well
719 0 MySQL Security
720 0 Optimizing MySQL
721 SELECT MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score, title
722 FROM wp
723 WHERE MATCH(title, text) AGAINST ('MySQL database' WITH QUERY EXPANSION)
724 ORDER BY score DESC;
725 score title
726 0.000000003771856604828372 MySQL Security
727 0.000000001885928302414186 1001 MySQL Tricks
728 0.000000001885928302414186 How To Use MySQL Well
729 0.000000001885928302414186 Optimizing MySQL
730 0 MySQL Tutorial
731 0 MySQL vs. YourSQL
732 SELECT MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score, title
733 FROM wp
734 WHERE MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE)
735 ORDER BY score DESC;
736 score title
737 0 MySQL Tutorial
738 ALTER TABLE wp ENGINE=myisam;
739 FLUSH STATUS;
740 SELECT title, MATCH(title, text) AGAINST ('database') AS score
741 FROM wp
742 WHERE MATCH(title, text) AGAINST ('database')
743 ORDER BY score DESC;
744 title score
745 MySQL vs. YourSQL 0.9562782645225525
746 MySQL Tutorial 0.5756555199623108
747 SHOW SESSION STATUS LIKE 'Sort%';
748 Variable_name Value
749 Sort_merge_passes 0
750 Sort_range 0
751 Sort_rows 0
752 Sort_scan 0
753 FLUSH STATUS;
754 SELECT title, MATCH(title, text) AGAINST ('database') AS score
755 FROM wp
756 ORDER BY score DESC LIMIT 2;
757 title score
758 MySQL vs. YourSQL 0.9562782645225525
759 MySQL Tutorial 0.5756555199623108
760 SHOW SESSION STATUS LIKE 'Sort%';
761 Variable_name Value
762 Sort_merge_passes 0
763 Sort_range 0
764 Sort_rows 2
765 Sort_scan 1
766 FLUSH STATUS;
767 SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
768 FROM wp
769 WHERE MATCH(title, text) AGAINST ('database');
770 docid score
771 1 0.5756555199623108
772 5 0.9562782645225525
773 SHOW SESSION STATUS LIKE 'Handler_read%';
774 Variable_name Value
775 Handler_read_first 0
776 Handler_read_key 0
777 Handler_read_last 0
778 Handler_read_next 3
779 Handler_read_prev 0
780 Handler_read_rnd 0
781 Handler_read_rnd_next 0
782 FLUSH STATUS;
783 SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
784 FROM wp
785 ORDER BY score DESC LIMIT 2;
786 docid score
787 5 0.9562782645225525
788 1 0.5756555199623108
789 SHOW STATUS LIKE 'Handler_read%';
790 Variable_name Value
791 Handler_read_first 0
792 Handler_read_key 0
793 Handler_read_last 0
794 Handler_read_next 0
795 Handler_read_prev 0
796 Handler_read_rnd 2
797 Handler_read_rnd_next 7
798 SHOW SESSION STATUS LIKE 'Sort%';
799 Variable_name Value
800 Sort_merge_passes 0
801 Sort_range 0
802 Sort_rows 2
803 Sort_scan 1
804 EXPLAIN SELECT COUNT(*)
805 FROM wp
806 WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
807 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
808 1 SIMPLE wp NULL fulltext idx idx 0 const 1 100.00 Using where
809 Warnings:
810 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`wp` where (match `test`.`wp`.`title`,`test`.`wp`.`text` against ('database'))
811 FLUSH STATUS;
812 SELECT COUNT(*)
813 FROM wp
814 WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
815 COUNT(*)
816 2
817 SHOW STATUS LIKE 'Handler_read%';
818 Variable_name Value
819 Handler_read_first 0
820 Handler_read_key 0
821 Handler_read_last 0
822 Handler_read_next 3
823 Handler_read_prev 0
824 Handler_read_rnd 0
825 Handler_read_rnd_next 0
826 DROP TABLE wp, t1;
827 CREATE TABLE t1
828 (
829 FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
830 title VARCHAR(255) DEFAULT '',
831 text MEDIUMTEXT ,
832 PRIMARY KEY (FTS_DOC_ID),
833 UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
834 FULLTEXT KEY ft_idx (title,text)
835 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
836 Warnings:
837 Warning 1681 Integer display width is deprecated and will be removed in a future release.
838 INSERT INTO t1 (title, text) VALUES
839 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
840 ('How To Use MySQL Well','After you went through a ...'),
841 ('Optimizing MySQL','In this tutorial we will show ...'),
842 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
843 ('MySQL vs. YourSQL database','In the following database to database comparison ...'),
844 ('MySQL Security','When configured properly, MySQL ...'),
845 ('InnoDB', 'InnoDB is a transaction-safe (ACID compliant) storage engine'),
846 ('MySQL is a database management system', 'A database is a structured collection of data...'),
847 ('MySQL databases are relational', 'A relational database stores data in separate tables rather than putting all the data in one big storeroom...'),
848 ('MySQL software is Open Source', 'Open Source means that it is possible for anyone to use and modify the software...'),
849 ('The MySQL Database Server is very fast, reliable, scalable, and easy to use', 'MySQL Server can run comfortably on a desktop or laptop...'),
850 ('MySQL Server works in client/server enviornment ......', 'The MySQL Database Software is a client/server system...'),
851 ('MyISAM', 'MyISAM is based on the older (and no longer available) ISAM storage engine but has many useful extensions'),
852 ('A large amount of contributed MySQL software is available', 'MySQL Server has a practical set of features developed in close cooperation with our users'),
853 (NULL,NULL);
854 ANALYZE TABLE t1;
855 # No ranking
856 EXPLAIN
857 SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE);
858 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
859 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
860 Warnings:
861 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database'))
862 SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE);
863 count(*)
864 6
865 EXPLAIN
866 SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('data*' IN BOOLEAN MODE) ORDER BY title LIMIT 3;
867 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
868 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
869 Warnings:
870 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('data*' in boolean mode)) limit 3
871 SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('data*' IN BOOLEAN MODE) ORDER BY title LIMIT 3;
872 count(*)
873 6
874 EXPLAIN
875 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);
876 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
877 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: no_ranking
878 Warnings:
879 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,`test`.`t1`.`title` AS `title` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+fast +database' in boolean mode))
880 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);
881 FTS_DOC_ID title
882 11 The MySQL Database Server is very fast, reliable, scalable, and easy to use
883 EXPLAIN
884 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION);
885 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
886 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: sorted
887 Warnings:
888 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,`test`.`t1`.`title` AS `title` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+very +fast' with query expansion))
889 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION);
890 FTS_DOC_ID title
891 1 MySQL Tutorial
892 10 MySQL software is Open Source
893 11 The MySQL Database Server is very fast, reliable, scalable, and easy to use
894 12 MySQL Server works in client/server enviornment ......
895 13 MyISAM
896 14 A large amount of contributed MySQL software is available
897 2 How To Use MySQL Well
898 3 Optimizing MySQL
899 4 1001 MySQL Tricks
900 5 MySQL vs. YourSQL database
901 6 MySQL Security
902 8 MySQL is a database management system
903 9 MySQL databases are relational
904 EXPLAIN
905 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE);
906 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
907 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: no_ranking
908 Warnings:
909 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,`test`.`t1`.`title` AS `title` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('"very fast"@3' in boolean mode))
910 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE);
911 FTS_DOC_ID title
912 11 The MySQL Database Server is very fast, reliable, scalable, and easy to use
913 EXPLAIN SELECT FTS_DOC_ID FROM t1
914 WHERE MATCH(title, text) AGAINST ('+for' IN BOOLEAN MODE);
915 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
916 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: no_ranking; Using index
917 Warnings:
918 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+for' in boolean mode))
919 Pattern "Full-text covering index search on t1 using ft_idx" found
920 SELECT FTS_DOC_ID FROM t1
921 WHERE MATCH(title, text) AGAINST ('+for' IN BOOLEAN MODE);
922 FTS_DOC_ID
923 # No sorting by rank
924 EXPLAIN SELECT FTS_DOC_ID, TITLE FROM t1
925 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
926 ORDER BY title;
927 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
928 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: no_ranking; Using filesort
929 Warnings:
930 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,`test`.`t1`.`title` AS `TITLE` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database')) order by `test`.`t1`.`title`
931 SELECT FTS_DOC_ID, TITLE FROM t1
932 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
933 ORDER BY title;
934 FTS_DOC_ID TITLE
935 9 MySQL databases are relational
936 8 MySQL is a database management system
937 12 MySQL Server works in client/server enviornment ......
938 1 MySQL Tutorial
939 5 MySQL vs. YourSQL database
940 11 The MySQL Database Server is very fast, reliable, scalable, and easy to use
941 EXPLAIN SELECT FTS_DOC_ID FROM t1
942 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);
943 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
944 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: no_ranking; Using index
945 Warnings:
946 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+fast +database' in boolean mode))
947 SELECT FTS_DOC_ID FROM t1
948 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);
949 FTS_DOC_ID
950 11
951 EXPLAIN
952 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title;
953 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
954 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: no_ranking; Using filesort
955 Warnings:
956 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,`test`.`t1`.`title` AS `title` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+very +fast' with query expansion)) order by `test`.`t1`.`title`
957 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title;
958 FTS_DOC_ID title
959 4 1001 MySQL Tricks
960 14 A large amount of contributed MySQL software is available
961 2 How To Use MySQL Well
962 13 MyISAM
963 9 MySQL databases are relational
964 8 MySQL is a database management system
965 6 MySQL Security
966 12 MySQL Server works in client/server enviornment ......
967 10 MySQL software is Open Source
968 1 MySQL Tutorial
969 5 MySQL vs. YourSQL database
970 3 Optimizing MySQL
971 11 The MySQL Database Server is very fast, reliable, scalable, and easy to use
972 EXPLAIN
973 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title;
974 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
975 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: no_ranking; Using filesort
976 Warnings:
977 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,`test`.`t1`.`title` AS `title` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('"very fast"@3' in boolean mode)) order by `test`.`t1`.`title`
978 SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title;
979 FTS_DOC_ID title
980 11 The MySQL Database Server is very fast, reliable, scalable, and easy to use
981 # LIMIT optimization
982 EXPLAIN SELECT FTS_DOC_ID, TITLE FROM t1
983 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
984 LIMIT 3;
985 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
986 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: sorted, limit = 3
987 Warnings:
988 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,`test`.`t1`.`title` AS `TITLE` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database')) limit 3
989 SELECT FTS_DOC_ID, TITLE FROM t1
990 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
991 LIMIT 3;
992 FTS_DOC_ID TITLE
993 11 The MySQL Database Server is very fast, reliable, scalable, and easy to use
994 5 MySQL vs. YourSQL database
995 8 MySQL is a database management system
996 EXPLAIN SELECT FTS_DOC_ID FROM t1
997 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
998 LIMIT 3;
999 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1000 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: no_ranking, limit = 3; Using index
1001 Warnings:
1002 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+fast +database' in boolean mode)) limit 3
1003 SELECT FTS_DOC_ID FROM t1
1004 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
1005 LIMIT 3;
1006 FTS_DOC_ID
1007 11
1008 EXPLAIN SELECT FTS_DOC_ID FROM t1
1009 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
1010 ORDER BY title
1011 LIMIT 3;
1012 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1013 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: no_ranking; Using filesort
1014 Warnings:
1015 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+fast +database' in boolean mode)) order by `test`.`t1`.`title` limit 3
1016 SELECT FTS_DOC_ID FROM t1
1017 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
1018 ORDER BY title
1019 LIMIT 3;
1020 FTS_DOC_ID
1021 11
1022 EXPLAIN
1023 SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title LIMIT 1;
1024 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1025 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: no_ranking; Using filesort
1026 Warnings:
1027 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+very +fast' with query expansion)) order by `test`.`t1`.`title` limit 1
1028 SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title LIMIT 1;
1029 FTS_DOC_ID
1030 4
1031 EXPLAIN
1032 SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title LIMIT 1;
1033 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1034 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: no_ranking; Using filesort
1035 Warnings:
1036 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('"very fast"@3' in boolean mode)) order by `test`.`t1`.`title` limit 1
1037 SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title LIMIT 1;
1038 FTS_DOC_ID
1039 11
1040 EXPLAIN
1041 SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as `rank`
1042 FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
1043 ORDER BY `rank`
1044 LIMIT 3;
1045 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1046 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Using filesort
1047 Warnings:
1048 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,(match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database')) AS `rank` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database')) order by `rank` limit 3
1049 SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as `rank`
1050 FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
1051 ORDER BY `rank`
1052 LIMIT 3;
1053 FTS_DOC_ID rank
1054 1 0.15835624933242798
1055 12 0.15835624933242798
1056 9 0.15835624933242798
1057 EXPLAIN
1058 SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as `rank`
1059 FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
1060 ORDER BY `rank` DESC, FTS_DOC_ID
1061 LIMIT 3;
1062 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1063 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Using filesort
1064 Warnings:
1065 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,(match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database')) AS `rank` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database')) order by `rank` desc,`test`.`t1`.`FTS_DOC_ID` limit 3
1066 SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as `rank`
1067 FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
1068 ORDER BY `rank` DESC, FTS_DOC_ID
1069 LIMIT 3;
1070 FTS_DOC_ID rank
1071 11 1.5415468215942383
1072 5 0.47506874799728394
1073 8 0.31671249866485596
1074 EXPLAIN SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as `rank`
1075 FROM t1
1076 ORDER BY `rank` DESC, FTS_DOC_ID
1077 LIMIT 3;
1078 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1079 1 SIMPLE t1 NULL index NULL FTS_DOC_ID_INDEX 8 NULL 15 100.00 Using index; Using filesort
1080 Warnings:
1081 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,(match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+database' in boolean mode)) AS `rank` from `test`.`t1` order by `rank` desc,`test`.`t1`.`FTS_DOC_ID` limit 3
1082 SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as `rank`
1083 FROM t1
1084 ORDER BY `rank` DESC, FTS_DOC_ID
1085 LIMIT 3;
1086 FTS_DOC_ID rank
1087 5 0.47506874799728394
1088 8 0.31671249866485596
1089 1 0.15835624933242798
1090 EXPLAIN SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as `rank`
1091 FROM t1
1092 ORDER BY `rank` DESC, FTS_DOC_ID
1093 LIMIT 3;
1094 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1095 1 SIMPLE t1 NULL index NULL FTS_DOC_ID_INDEX 8 NULL 15 100.00 Using index; Using filesort
1096 Warnings:
1097 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,(match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database')) AS `rank` from `test`.`t1` order by `rank` desc,`test`.`t1`.`FTS_DOC_ID` limit 3
1098 SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as `rank`
1099 FROM t1
1100 ORDER BY `rank` DESC, FTS_DOC_ID
1101 LIMIT 3;
1102 FTS_DOC_ID rank
1103 11 1.5415468215942383
1104 5 0.47506874799728394
1105 8 0.31671249866485596
1106 EXPLAIN SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as `rank`
1107 FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE)
1108 ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) DESC
1109 LIMIT 3;
1110 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1111 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: sorted, limit = 3; Using index
1112 Warnings:
1113 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,(match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+database' in boolean mode)) AS `rank` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+database' in boolean mode)) order by (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+database' in boolean mode)) desc limit 3
1114 Pattern "Full-text covering index search on t1 using ft_idx" found
1115 SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as `rank`
1116 FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE)
1117 ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) DESC
1118 LIMIT 3;
1119 FTS_DOC_ID rank
1120 5 0.47506874799728394
1121 8 0.31671249866485596
1122 1 0.15835624933242798
1123 EXPLAIN SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as `rank`
1124 FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) and FTS_DOC_ID > 1
1125 ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) DESC
1126 LIMIT 3;
1127 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1128 1 SIMPLE t1 NULL fulltext PRIMARY,FTS_DOC_ID_INDEX,ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: sorted; Using index
1129 Warnings:
1130 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,(match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+database' in boolean mode)) AS `rank` from `test`.`t1` where ((match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+database' in boolean mode)) and (`test`.`t1`.`FTS_DOC_ID` > 1)) order by (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+database' in boolean mode)) desc limit 3
1131 EXPLAIN FORMAT=TREE
1132 SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as `rank`
1133 FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) and FTS_DOC_ID > 1
1134 ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) DESC
1135 LIMIT 3;
1136 EXPLAIN
1137 -> Limit: 3 row(s)
1138 -> Filter:
1139 -> Full-text covering index search on t1 using ft_idx (title='+database')
1140
1141 SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as `rank`
1142 FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) and FTS_DOC_ID > 1
1143 ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) DESC
1144 LIMIT 3;
1145 FTS_DOC_ID rank
1146 5 0.47506874799728394
1147 8 0.31671249866485596
1148 9 0.15835624933242798
1149 EXPLAIN
1150 SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as `rank`
1151 FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
1152 ORDER BY `rank`
1153 LIMIT 1;
1154 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1155 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Using filesort
1156 Warnings:
1157 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,(match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+very +fast' with query expansion)) AS `rank` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+very +fast' with query expansion)) order by `rank` limit 1
1158 SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as `rank`
1159 FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
1160 ORDER BY `rank`
1161 LIMIT 1;
1162 FTS_DOC_ID rank
1163 3 0.009391550906002522
1164 EXPLAIN
1165 SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as `rank`
1166 FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
1167 ORDER BY `rank` DESC
1168 LIMIT 1;
1169 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1170 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: sorted, limit = 1; Using index
1171 Warnings:
1172 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,(match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+very +fast' with query expansion)) AS `rank` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+very +fast' with query expansion)) order by `rank` desc limit 1
1173 SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as `rank`
1174 FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
1175 ORDER BY `rank` DESC
1176 LIMIT 1;
1177 FTS_DOC_ID rank
1178 11 15.345823287963867
1179 EXPLAIN
1180 SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as `rank`
1181 FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
1182 ORDER BY MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
1183 LIMIT 1;
1184 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1185 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Using filesort
1186 Warnings:
1187 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,(match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+very +fast' with query expansion)) AS `rank` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+very +fast' with query expansion)) order by (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+very +fast' with query expansion)) limit 1
1188 SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as `rank`
1189 FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
1190 ORDER BY MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) DESC
1191 LIMIT 1;
1192 FTS_DOC_ID rank
1193 11 15.345823287963867
1194 # WHERE optimization on MATCH > 'some_rank'
1195 EXPLAIN SELECT FTS_DOC_ID FROM t1
1196 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) > 0.1;
1197 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1198 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: sorted, rank > 0.1; Using index
1199 Warnings:
1200 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID` from `test`.`t1` where ((match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database')) > 0.1)
1201 SELECT FTS_DOC_ID FROM t1
1202 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) > 0.1;
1203 FTS_DOC_ID
1204 1
1205 11
1206 12
1207 5
1208 8
1209 9
1210 # additional test for correct behaviour
1211 EXPLAIN SELECT * FROM t1 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC LIMIT 10;
1212 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1213 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 15 100.00 Using filesort
1214 Warnings:
1215 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,`test`.`t1`.`title` AS `title`,`test`.`t1`.`text` AS `text` from `test`.`t1` order by (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database')) desc limit 10
1216 SELECT FTS_DOC_ID FROM t1
1217 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) AND
1218 MATCH (title, text) AGAINST ('mysql' IN NATURAL LANGUAGE MODE)
1219 LIMIT 6;
1220 FTS_DOC_ID
1221 1
1222 11
1223 12
1224 5
1225 8
1226 9
1227 # test OR condition
1228 SELECT FTS_DOC_ID
1229 FROM t1
1230 WHERE MATCH(title, text) AGAINST ('database')
1231 OR MATCH(title, text) AGAINST ('mysql')
1232 ORDER BY MATCH(title, text) AGAINST ('database') DESC;
1233 FTS_DOC_ID
1234 5
1235 8
1236 1
1237 9
1238 11
1239 12
1240 2
1241 3
1242 4
1243 6
1244 10
1245 14
1246 EXPLAIN SELECT FTS_DOC_ID
1247 FROM t1
1248 WHERE MATCH(title, text) AGAINST ('database')
1249 OR MATCH(title, text) AGAINST ('mysql')
1250 ORDER BY MATCH(title, text) AGAINST ('database') DESC;
1251 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1252 1 SIMPLE t1 NULL index NULL FTS_DOC_ID_INDEX 8 NULL 15 20.99 Using where; Using index; Using filesort
1253 Warnings:
1254 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID` from `test`.`t1` where ((match `test`.`t1`.`title`,`test`.`t1`.`text` against ('database')) or (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('mysql'))) order by (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('database')) desc
1255 # MATCH and GROUP BY, DISTINCT
1256 EXPLAIN SELECT FTS_DOC_ID FROM t1
1257 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
1258 GROUP BY FTS_DOC_ID
1259 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
1260 LIMIT 3;
1261 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1262 1 SIMPLE t1 NULL fulltext PRIMARY,FTS_DOC_ID_INDEX,ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: sorted, limit = 3; Using index
1263 Warnings:
1264 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database')) group by `test`.`t1`.`FTS_DOC_ID` order by (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database')) desc limit 3
1265 SELECT FTS_DOC_ID FROM t1
1266 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
1267 GROUP BY FTS_DOC_ID
1268 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
1269 LIMIT 3;
1270 FTS_DOC_ID
1271 11
1272 5
1273 8
1274 EXPLAIN SELECT FTS_DOC_ID FROM t1
1275 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
1276 GROUP BY title
1277 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
1278 LIMIT 3;
1279 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1280 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Using temporary; Using filesort
1281 Warnings:
1282 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database')) group by `test`.`t1`.`title` order by (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database')) desc limit 3
1283 SELECT FTS_DOC_ID FROM t1
1284 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
1285 GROUP BY title
1286 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
1287 LIMIT 3;
1288 FTS_DOC_ID
1289 11
1290 5
1291 8
1292 EXPLAIN SELECT MAX(FTS_DOC_ID) FROM t1
1293 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
1294 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
1295 LIMIT 3;
1296 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1297 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Using index
1298 Warnings:
1299 Note 1003 /* select#1 */ select max(`test`.`t1`.`FTS_DOC_ID`) AS `MAX(FTS_DOC_ID)` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database')) limit 3
1300 SELECT MAX(FTS_DOC_ID) FROM t1
1301 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
1302 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
1303 LIMIT 3;
1304 MAX(FTS_DOC_ID)
1305 12
1306 EXPLAIN SELECT DISTINCT(title) FROM t1
1307 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
1308 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
1309 LIMIT 3;
1310 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1311 1 SIMPLE t1 NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Using temporary; Using filesort
1312 Warnings:
1313 Note 1003 /* select#1 */ select distinct `test`.`t1`.`title` AS `title` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database')) order by (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database')) desc limit 3
1314 SELECT DISTINCT(title) FROM t1
1315 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
1316 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
1317 LIMIT 3;
1318 title
1319 The MySQL Database Server is very fast, reliable, scalable, and easy to use
1320 MySQL vs. YourSQL database
1321 MySQL is a database management system
1322 EXPLAIN SELECT DISTINCT(FTS_DOC_ID) FROM t1
1323 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
1324 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
1325 LIMIT 3;
1326 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1327 1 SIMPLE t1 NULL fulltext PRIMARY,FTS_DOC_ID_INDEX,ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: sorted, limit = 3; Using index
1328 Warnings:
1329 Note 1003 /* select#1 */ select distinct `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database')) order by (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('fast database')) desc limit 3
1330 SELECT DISTINCT(FTS_DOC_ID) FROM t1
1331 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
1332 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
1333 LIMIT 3;
1334 FTS_DOC_ID
1335 11
1336 5
1337 8
1338 # FTS index access
1339 SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as `rank`
1340 FROM t1
1341 ORDER BY `rank` DESC
1342 LIMIT 3;
1343 FTS_DOC_ID rank
1344 5 0.47506874799728394
1345 8 0.31671249866485596
1346 1 0.15835624933242798
1347 EXPLAIN SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as `rank`
1348 FROM t1
1349 ORDER BY `rank` DESC
1350 LIMIT 3;
1351 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1352 1 SIMPLE t1 NULL fulltext NULL ft_idx 262 NULL 15 100.00 Ft_hints: sorted, limit = 3; Using index
1353 Warnings:
1354 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,(match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+database' in boolean mode)) AS `rank` from `test`.`t1` order by `rank` desc limit 3
1355 SELECT a.FTS_DOC_ID, b.FTS_DOC_ID
1356 FROM t1 a, t1 b
1357 WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and
1358 MATCH(b.title, b.text) AGAINST ('+mysql' IN BOOLEAN MODE) and
1359 a.FTS_DOC_ID = b.FTS_DOC_ID;
1360 FTS_DOC_ID FTS_DOC_ID
1361 1 1
1362 11 11
1363 12 12
1364 5 5
1365 8 8
1366 9 9
1367 EXPLAIN SELECT a.FTS_DOC_ID, b.FTS_DOC_ID
1368 FROM t1 a, t1 b
1369 WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and
1370 MATCH(b.title, b.text) AGAINST ('+mysql' IN BOOLEAN MODE) and
1371 a.FTS_DOC_ID = b.FTS_DOC_ID;
1372 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1373 1 SIMPLE a NULL fulltext PRIMARY,FTS_DOC_ID_INDEX,ft_idx ft_idx 0 const 1 100.00 Using where; Ft_hints: no_ranking; Using index
1374 1 SIMPLE b NULL eq_ref PRIMARY,FTS_DOC_ID_INDEX,ft_idx PRIMARY 8 test.a.FTS_DOC_ID 1 11.11 Using where; Using index
1375 Warnings:
1376 Note 1003 /* select#1 */ select `test`.`a`.`FTS_DOC_ID` AS `FTS_DOC_ID`,`test`.`b`.`FTS_DOC_ID` AS `FTS_DOC_ID` from `test`.`t1` `a` join `test`.`t1` `b` where ((`test`.`b`.`FTS_DOC_ID` = `test`.`a`.`FTS_DOC_ID`) and (match `test`.`a`.`title`,`test`.`a`.`text` against ('+database' in boolean mode)) and (match `test`.`b`.`title`,`test`.`b`.`text` against ('+mysql' in boolean mode)))
1377 SELECT a.FTS_DOC_ID, MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE),
1378 b.FTS_DOC_ID, MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE)
1379 FROM t1 a, t1 b
1380 WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and
1381 MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE);
1382 FTS_DOC_ID MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) FTS_DOC_ID MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE)
1383 1 0.15835624933242798 1 0.15835624933242798
1384 1 0.15835624933242798 11 0.15835624933242798
1385 1 0.15835624933242798 12 0.15835624933242798
1386 1 0.15835624933242798 5 0.47506874799728394
1387 1 0.15835624933242798 8 0.31671249866485596
1388 1 0.15835624933242798 9 0.15835624933242798
1389 11 0.15835624933242798 1 0.15835624933242798
1390 11 0.15835624933242798 11 0.15835624933242798
1391 11 0.15835624933242798 12 0.15835624933242798
1392 11 0.15835624933242798 5 0.47506874799728394
1393 11 0.15835624933242798 8 0.31671249866485596
1394 11 0.15835624933242798 9 0.15835624933242798
1395 12 0.15835624933242798 1 0.15835624933242798
1396 12 0.15835624933242798 11 0.15835624933242798
1397 12 0.15835624933242798 12 0.15835624933242798
1398 12 0.15835624933242798 5 0.47506874799728394
1399 12 0.15835624933242798 8 0.31671249866485596
1400 12 0.15835624933242798 9 0.15835624933242798
1401 5 0.47506874799728394 1 0.15835624933242798
1402 5 0.47506874799728394 11 0.15835624933242798
1403 5 0.47506874799728394 12 0.15835624933242798
1404 5 0.47506874799728394 5 0.47506874799728394
1405 5 0.47506874799728394 8 0.31671249866485596
1406 5 0.47506874799728394 9 0.15835624933242798
1407 8 0.31671249866485596 1 0.15835624933242798
1408 8 0.31671249866485596 11 0.15835624933242798
1409 8 0.31671249866485596 12 0.15835624933242798
1410 8 0.31671249866485596 5 0.47506874799728394
1411 8 0.31671249866485596 8 0.31671249866485596
1412 8 0.31671249866485596 9 0.15835624933242798
1413 9 0.15835624933242798 1 0.15835624933242798
1414 9 0.15835624933242798 11 0.15835624933242798
1415 9 0.15835624933242798 12 0.15835624933242798
1416 9 0.15835624933242798 5 0.47506874799728394
1417 9 0.15835624933242798 8 0.31671249866485596
1418 9 0.15835624933242798 9 0.15835624933242798
1419 EXPLAIN SELECT a.FTS_DOC_ID, MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE),
1420 b.FTS_DOC_ID, MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE)
1421 FROM t1 a, t1 b
1422 WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and
1423 MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE);
1424 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1425 1 SIMPLE a NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Using index
1426 1 SIMPLE b NULL fulltext ft_idx ft_idx 0 const 1 100.00 Using where; Using index
1427 Warnings:
1428 Note 1003 /* select#1 */ select `test`.`a`.`FTS_DOC_ID` AS `FTS_DOC_ID`,(match `test`.`a`.`title`,`test`.`a`.`text` against ('+database' in boolean mode)) AS `MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE)`,`test`.`b`.`FTS_DOC_ID` AS `FTS_DOC_ID`,(match `test`.`b`.`title`,`test`.`b`.`text` against ('+database' in boolean mode)) AS `MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE)` from `test`.`t1` `a` join `test`.`t1` `b` where ((match `test`.`a`.`title`,`test`.`a`.`text` against ('+database' in boolean mode)) and (match `test`.`b`.`title`,`test`.`b`.`text` against ('+database' in boolean mode)))
1429 EXPLAIN SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ("data*" IN BOOLEAN MODE) * 100;
1430 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1431 1 SIMPLE t1 NULL index NULL FTS_DOC_ID_INDEX 8 NULL 15 100.00 Using where; Using index
1432 Warnings:
1433 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID` from `test`.`t1` where (0 <> ((match `test`.`t1`.`title`,`test`.`t1`.`text` against ('data*' in boolean mode)) * 100))
1434 SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ("data*" IN BOOLEAN MODE) * 100
1435 FROM t1 WHERE MATCH(title, text) AGAINST ("data*" IN BOOLEAN MODE) * 100;
1436 FTS_DOC_ID MATCH(title, text) AGAINST ("data*" IN BOOLEAN MODE) * 100
1437 1 4.92168664932251
1438 5 14.76505994796753
1439 8 9.84337329864502
1440 9 4.92168664932251
1441 11 4.92168664932251
1442 12 4.92168664932251
1443 SELECT * FROM t1 WHERE title IS NULL AND text IS NULL;
1444 FTS_DOC_ID title text
1445 15 NULL NULL
1446 CREATE TABLE t2 SELECT FTS_DOC_ID as doc_id, title, text FROM t1;
1447 ALTER TABLE t2 ADD PRIMARY KEY (doc_id);
1448 ALTER TABLE t2 ADD FULLTEXT KEY ft_idx (title,text);
1449 Warnings:
1450 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
1451 ANALYZE TABLE t2;
1452 EXPLAIN SELECT DOC_ID FROM t2 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
1453 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1454 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 15 100.00 Using where
1455 Warnings:
1456 Note 1003 /* select#1 */ select `test`.`t2`.`doc_id` AS `DOC_ID` from `test`.`t2` where (0 <> ((match `test`.`t2`.`title`,`test`.`t2`.`text` against ('+database' in boolean mode)) * 100))
1457 SELECT DOC_ID FROM t2 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
1458 DOC_ID
1459 1
1460 5
1461 8
1462 9
1463 11
1464 12
1465 EXPLAIN SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
1466 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1467 1 SIMPLE t1 NULL index NULL FTS_DOC_ID_INDEX 8 NULL 15 100.00 Using where; Using index
1468 Warnings:
1469 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID` from `test`.`t1` where (0 <> ((match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+database' in boolean mode)) * 100))
1470 SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
1471 FTS_DOC_ID
1472 1
1473 5
1474 8
1475 9
1476 11
1477 12
1478 DROP TABLE t1, t2;
1479 "Check hints with uft8 charset for 2 cases"
1480 SET NAMES utf8mb4;
1481 CREATE TABLE t1 (
1482 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
1483 title VARCHAR(200),
1484 text TEXT
1485 ) CHARACTER SET = utf8, ENGINE=InnoDB;
1486 Warnings:
1487 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
1488 INSERT INTO t1 (title, text) VALUES
1489 ('Я могу есть стекло', 'оно мне не вредит'),
1490 ('Мога да ям стъкло', 'то не ми вреди'),
1491 ('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'),
1492 ('Příliš žluťoučký kůň', 'úpěl ďábelské kódy'),
1493 ('Sævör grét', 'áðan því úlpan var ónýt'),
1494 ('うゐのおくやま','けふこえて'),
1495 ('いろはにほへど ちりぬる','あさきゆめみじ ゑひもせず');
1496 CREATE FULLTEXT INDEX idx on t1 (title, text);
1497 # No ranking
1498 EXPLAIN
1499 SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('вредит' IN NATURAL LANGUAGE MODE);
1500 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1501 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
1502 Warnings:
1503 Note 1003 /* select#1 */ select count(0) AS `count(*)` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('вредит'))
1504 SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('вредит' IN NATURAL LANGUAGE MODE);
1505 count(*)
1506 1
1507 EXPLAIN
1508 SELECT * FROM t1 WHERE MATCH(title, text) AGAINST ("оно" WITH QUERY EXPANSION);
1509 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1510 1 SIMPLE t1 NULL fulltext idx idx 0 const 1 100.00 Using where; Ft_hints: sorted
1511 Warnings:
1512 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID`,`test`.`t1`.`title` AS `title`,`test`.`t1`.`text` AS `text` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('оно' with query expansion))
1513 SELECT * FROM t1 WHERE MATCH(title, text) AGAINST ("оно" WITH QUERY EXPANSION);
1514 FTS_DOC_ID title text
1515 1 Я могу есть стекло оно мне не вредит
1516 # No sorting by rank
1517 EXPLAIN SELECT FTS_DOC_ID FROM t1
1518 WHERE MATCH(title, text) AGAINST ('+(Мога τίποτα)' IN BOOLEAN MODE);
1519 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1520 1 SIMPLE t1 NULL fulltext idx idx 0 const 1 100.00 Using where; Ft_hints: no_ranking; Using index
1521 Warnings:
1522 Note 1003 /* select#1 */ select `test`.`t1`.`FTS_DOC_ID` AS `FTS_DOC_ID` from `test`.`t1` where (match `test`.`t1`.`title`,`test`.`t1`.`text` against ('+(Мога τίποτα)' in boolean mode))
1523 SELECT FTS_DOC_ID FROM t1
1524 WHERE MATCH(title, text) AGAINST ('+(Мога τίποτα)' IN BOOLEAN MODE);
1525 FTS_DOC_ID
1526 2
1527 3
1528 DROP TABLE t1;
1529 #
1530 # Bug #18924341 CRASH IN TEST_IF_SKIP_SORT_ORDER, GROUP BY MATCH AGAINST DESC
1531 #
1532 CREATE TABLE t1 (f1 CHAR(1), FULLTEXT KEY (f1));
1533 SELECT 1 FROM t1 NATURAL JOIN t1 a GROUP BY MATCH(t1.f1) AGAINST ("1")
1534 ORDER BY MATCH(t1.f1) AGAINST ("1") DESC;
1535 1
1536 DROP TABLE t1;
1537 #
1538 # Bug#20261601 ASSERTION FAILED: !FIRST_QEP_TAB->TABLE()->NO_KEYREAD
1539 #
1540 CREATE TABLE t1(a INT PRIMARY KEY);
1541 INSERT INTO t1 VALUES(1),(2);
1542 SELECT (SELECT MATCH(`a`)AGAINST('1') FROM t1) FROM t1;
1543 ERROR HY000: Can't find FULLTEXT index matching the column list
1544 SELECT 1, a IN (SELECT a FROM t1) FROM t1;
1545 1 a IN (SELECT a FROM t1)
1546 1 1
1547 1 1
1548 DROP TABLE t1;
1549 #
1550 # Bug#20442572 ASSERTION `!FIRST_QEP_TAB->TABLE()->NO_KEYREAD' FAILED.
1551 # Bug#75688 Assertion `!first_qep_tab->table()->no_keyread' failed.
1552 #
1553 CREATE TABLE t1(a INT,b POINT NOT NULL,KEY(a));
1554 HANDLER t1 OPEN;
1555 select * from t1 where MATCH a,b AGAINST('"Now sUPPort"' IN BOOLEAN MODE);
1556 ERROR HY000: Can't find FULLTEXT index matching the column list
1557 prepare stmt1 from "truncate t1";
1558 SELECT a IN(SELECT a FROM t1)FROM t1;
1559 a IN(SELECT a FROM t1)
1560 deallocate prepare stmt1;
1561 DROP TABLE t1;
1562 #
1563 # Bug #20685427 INVALID WRITE OF FREED MEMORY IN ITEM_FUNC_MATCH::CLEANUP
1564 #
1565 CREATE TABLE t1(a TEXT CHARSET LATIN1, FULLTEXT KEY(a)) ENGINE=INNODB;
1566 SELECT MATCH(a) AGAINST ('') FROM (SELECT a FROM t1 LIMIT 1) q;
1567 ERROR HY000: The used table type doesn't support FULLTEXT indexes
1568 DROP TABLE t1;
1569 #
1570 # Bug#21140067 EXPLAIN .. MATCH AGAINST: ASSERTION FAILED: TO <= END
1571 #
1572 CREATE TABLE t1(f1 CHAR(1) CHARSET latin1, FULLTEXT(f1)) ENGINE=INNODB;
1573 EXPLAIN SELECT 1 FROM t1 WHERE 1.238585e+308 <= MATCH(f1) AGAINST ('1' IN BOOLEAN MODE);
1574 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1575 1 SIMPLE t1 NULL fulltext f1 f1 0 const 1 100.00 Using where; Ft_hints: rank >= 1e+308
1576 Warnings:
1577 Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where (1.238585e+308 <= (match `test`.`t1`.`f1` against ('1' in boolean mode)))
1578 EXPLAIN FORMAT = JSON SELECT 1 FROM t1 WHERE 1.238585e+308 <= MATCH(f1) AGAINST ('1' IN BOOLEAN MODE);
1579 EXPLAIN
1580 {
1581 "query_block": {
1582 "select_id": 1,
1583 "cost_info": {
1584 "query_cost": "0.35"
1585 },
1586 "table": {
1587 "table_name": "t1",
1588 "access_type": "fulltext",
1589 "possible_keys": [
1590 "f1"
1591 ],
1592 "key": "f1",
1593 "used_key_parts": [
1594 "f1"
1595 ],
1596 "key_length": "0",
1597 "ref": [
1598 "const"
1599 ],
1600 "rows_examined_per_scan": 1,
1601 "rows_produced_per_join": 1,
1602 "filtered": "100.00",
1603 "ft_hints": "rank >= 1e+308",
1604 "cost_info": {
1605 "read_cost": "0.25",
1606 "eval_cost": "0.10",
1607 "prefix_cost": "0.35",
1608 "data_read_per_join": "8"
1609 },
1610 "used_columns": [
1611 "f1"
1612 ],
1613 "attached_condition": "(1.238585e+308 <= (match `test`.`t1`.`f1` against ('1' in boolean mode)))"
1614 }
1615 }
1616 }
1617 Warnings:
1618 Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t1` where (1.238585e+308 <= (match `test`.`t1`.`f1` against ('1' in boolean mode)))
1619 DROP TABLE t1;
1620 #
1621 # Bug#21140088 MATCH AGAINST: ASSERTION FAILED: !TABLE || (!TABLE->READ_SET || BITMAP_IS_SET
1622 #
1623 SET sql_mode='';
1624 CREATE TABLE t1(a INT) ENGINE=INNODB;
1625 CREATE TABLE t2(b TEXT CHARSET LATIN1, FULLTEXT(b), PRIMARY KEY(b(10))) ENGINE=INNODB;
1626 INSERT INTO t2 VALUES ('a'),('b');
1627 SELECT NOT EXISTS (SELECT MATCH(b) AGAINST ('1') FROM t1) FROM t2 GROUP BY "a";
1628 ERROR HY000: Incorrect arguments to MATCH
1629 DROP TABLE t1, t2;
1630 CREATE TABLE t1(a INT) ENGINE=MyISAM;
1631 CREATE TABLE t2(b TEXT CHARSET LATIN1, FULLTEXT(b), PRIMARY KEY(b(10))) ENGINE=MyISAM;
1632 INSERT INTO t2 VALUES ('a'),('b');
1633 SELECT NOT EXISTS (SELECT MATCH(b) AGAINST ('1' in BOOLEAN MODE) FROM t1) FROM t2 GROUP BY "a";
1634 ERROR HY000: Incorrect arguments to MATCH
1635 DROP TABLE t1, t2;
1636 SET sql_mode=default;
1637 #
1638 # Bug#21140039 ASSERTION FAILED: !FIRST_QEP_TAB->TABLE()->NO_KEYREAD MATCH AGAINST.....
1639 #
1640 CREATE TABLE t1
1641 (
1642 a INT,
1643 b INT,
1644 c CHAR(1) CHARSET latin1,
1645 PRIMARY KEY (b,a),
1646 FULLTEXT KEY (c)
1647 ) ENGINE=INNODB;
1648 SELECT "a" NOT IN(SELECT b FROM t1 WHERE MATCH(c) AGAINST ('a' IN BOOLEAN MODE));
1649 "a" NOT IN(SELECT b FROM t1 WHERE MATCH(c) AGAINST ('a' IN BOOLEAN MODE))
1650 1
1651 DROP TABLE t1;
1652 #
1653 # Bug#21300774 ASSERT `!INIT_FTFUNCS(THD, SELECT_LEX)` IN JOIN::RESET AT SQL/SQL_SELECT.CC:874
1654 #
1655 CREATE TABLE t1 (f1 INT);
1656 INSERT INTO t1 VALUES (1);
1657 INSERT INTO t1 VALUES (2);
1658 CREATE TABLE t2 (ft TEXT, FULLTEXT KEY ft(ft));
1659 INSERT INTO t2 VALUES ('abc');
1660 INSERT INTO t2 VALUES ('def');
1661 UPDATE t1 SET f1 =
1662 (SELECT t1.f1 FROM t2 WHERE NOT TRUE AND
1663 MATCH (ft) AGAINST ((SELECT 'xyz' FROM t2)));
1664 ERROR 21000: Subquery returns more than 1 row
1665 DROP TABLE t1, t2;
1666 #
1667 # Bug#22679209: FULL-TEXT QUERIES WITH ADDITIONAL SECONDARY INDEX
1668 # GIVES NULL OR ZERO ROWS
1669 #
1670 CREATE TABLE t1 (
1671 f1 INTEGER,
1672 title varchar(255),
1673 body mediumtext,
1674 KEY f1 (f1),
1675 FULLTEXT KEY title (title),
1676 FULLTEXT KEY body (body)
1677 ) ENGINE=InnoDB;
1678 INSERT INTO t1 VALUES
1679 (1, 'Insert into table', 'insert into table select from'),
1680 (1, 'Delete from table', 'insert into table select from'),
1681 (1, 'Update', 'perform update'),
1682 (2, 'Insert into table', 'insert into table select from'),
1683 ( 2, 'Delete from table', 'some body text here'),
1684 ( 2, 'Update', 'perform update'),
1685 ( 3, 'Insert into table', 'insert into table select from'),
1686 ( 3, 'Delete from table', 'some body text here');
1687 SELECT f1 FROM t1 WHERE f1=1 AND
1688 (MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR
1689 MATCH (body) AGAINST ('table' IN BOOLEAN MODE));
1690 f1
1691 1
1692 1
1693 DROP TABLE t1;
1694 # End of test for Bug#22679209
1695 #
1696 # Bug#32685616: WRONG RESULTS WITH FTS COVERING INDEX SCAN
1697 #
1698 CREATE TABLE t(FTS_DOC_ID BIGINT UNSIGNED PRIMARY KEY,
1699 x VARCHAR(10),
1700 FULLTEXT KEY (x));
1701 INSERT INTO t VALUES (1, 'abc'), (2, 'abc xyz'), (3, 'xyz abc'), (4, 'xyz');
1702 ANALYZE TABLE t;
1703 Table Op Msg_type Msg_text
1704 test.t analyze status OK
1705 SELECT FTS_DOC_ID FROM t WHERE
1706 IF(MATCH(x) AGAINST('abc'), TRUE, FALSE) AND MATCH(x) AGAINST('xyz');
1707 FTS_DOC_ID
1708 2
1709 3
1710 SELECT 1 FROM t WHERE
1711 IF(MATCH(x) AGAINST('abc'), TRUE, FALSE) AND MATCH(x) AGAINST('xyz');
1712 1
1713 1
1714 1
1715 SELECT COUNT(*) FROM t WHERE
1716 IF(MATCH(x) AGAINST('abc'), TRUE, FALSE) AND MATCH(x) AGAINST('xyz');
1717 COUNT(*)
1718 2
1719 SELECT FTS_DOC_ID FROM t WHERE
1720 MATCH(x) AGAINST('xyz') AND IF(MATCH(x) AGAINST('abc'), TRUE, FALSE);
1721 FTS_DOC_ID
1722 2
1723 3
1724 SELECT 1 FROM t WHERE
1725 MATCH(x) AGAINST('xyz') AND IF(MATCH(x) AGAINST('abc'), TRUE, FALSE);
1726 1
1727 1
1728 1
1729 SELECT COUNT(*) FROM t WHERE
1730 MATCH(x) AGAINST('xyz') AND IF(MATCH(x) AGAINST('abc'), TRUE, FALSE);
1731 COUNT(*)
1732 2
1733 DROP TABLE t;