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