CREATE TABLE wp(
FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL DEFAULT '',
text MEDIUMTEXT NOT NULL,
dummy INTEGER,
PRIMARY KEY (FTS_DOC_ID),
UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
FULLTEXT KEY idx (title,text)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO wp (title, text) VALUES
('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database to database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
CREATE TABLE t1 (i INTEGER);
INSERT INTO t1 SELECT FTS_DOC_ID FROM wp;
SET STATEMENT use_stat_tables=never FOR
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
SET STATEMENT use_stat_tables=never FOR
ANALYZE TABLE wp;
Table	Op	Msg_type	Msg_text
test.wp	analyze	status	OK
SELECT FTS_DOC_ID, title, MATCH(title, text) AGAINST ('database') AS score1,
MATCH(title, text) AGAINST ('mysql') AS score2
FROM wp;
FTS_DOC_ID	title	score1	score2
1	MySQL Tutorial	0.22764469683170319	0.000000003771856604828372
2	How To Use MySQL Well	0	0.000000001885928302414186
3	Optimizing MySQL	0	0.000000001885928302414186
4	1001 MySQL Tricks	0	0.000000001885928302414186
5	MySQL vs. YourSQL	0.45528939366340637	0.000000001885928302414186
6	MySQL Security	0	0.000000003771856604828372
No sorting for this query
FLUSH STATUS;
SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
ORDER BY score DESC;
title	score
MySQL vs. YourSQL	0.45528939366340637
MySQL Tutorial	0.22764469683170319
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	2
Sort_scan	1
No sorting for this query even if MATCH is part of an expression
FLUSH STATUS;
SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database') > 0.1
ORDER BY score DESC;
title	score
MySQL vs. YourSQL	0.45528939366340637
MySQL Tutorial	0.22764469683170319
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	2
Sort_scan	1
No sorting even if there are several MATCH expressions as long as the
right one is used in ORDER BY
FLUSH STATUS;
SELECT title, MATCH(title, text) AGAINST ('database') AS score1,
MATCH(title, text) AGAINST ('mysql') AS score2
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
ORDER BY score1 DESC;
title	score1	score2
MySQL vs. YourSQL	0.45528939366340637	0.000000001885928302414186
MySQL Tutorial	0.22764469683170319	0.000000003771856604828372
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	2
Sort_scan	1
No Sorting since FT table is first table in query
FLUSH STATUS;
SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp, t1
WHERE MATCH(title, text) AGAINST ('database') AND FTS_DOC_ID = t1.i
ORDER BY score DESC;
title	score
MySQL vs. YourSQL	0.45528939366340637
MySQL Tutorial	0.22764469683170319
SHOW SESSION STATUS LIKE 'Sort_rows%';
Variable_name	Value
Sort_rows	2
Sorting since there is no WHERE clause
FLUSH STATUS;
SELECT MATCH(title, text) AGAINST ('database'), title AS score
FROM wp
ORDER BY score DESC;
MATCH(title, text) AGAINST ('database')	score
0	1001 MySQL Tricks
0	How To Use MySQL Well
0	MySQL Security
0	Optimizing MySQL
0.22764469683170319	MySQL Tutorial
0.45528939366340637	MySQL vs. YourSQL
SHOW SESSION STATUS LIKE 'Sort_rows%';
Variable_name	Value
Sort_rows	6
Sorting since ordering on multiple columns
FLUSH STATUS;
SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
ORDER BY score DESC, FTS_DOC_ID;
title	score
MySQL vs. YourSQL	0.45528939366340637
MySQL Tutorial	0.22764469683170319
SHOW SESSION STATUS LIKE 'Sort_rows%';
Variable_name	Value
Sort_rows	2
Sorting since ordering is not descending
FLUSH STATUS;
SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
ORDER BY score ASC;
title	score
MySQL Tutorial	0.22764469683170319
MySQL vs. YourSQL	0.45528939366340637
SHOW SESSION STATUS LIKE 'Sort_rows%';
Variable_name	Value
Sort_rows	2
Sorting because one is ordering on a different MATCH expression
FLUSH STATUS;
SELECT title, MATCH(title, text) AGAINST ('mysql') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
ORDER BY score DESC;
title	score
MySQL Tutorial	0.000000003771856604828372
MySQL vs. YourSQL	0.000000001885928302414186
SHOW SESSION STATUS LIKE 'Sort_rows%';
Variable_name	Value
Sort_rows	2
No sorting for this query
FLUSH STATUS;
SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
ORDER BY score DESC LIMIT 2;
title	score
MySQL vs. YourSQL	0.45528939366340637
MySQL Tutorial	0.22764469683170319
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	2
Sort_scan	1
Revert to table scan and sorting for this query since not
enough matching rows to satisfy LIMIT clause
FLUSH STATUS;
SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
ORDER BY score DESC LIMIT 2;
title	score
MySQL vs. YourSQL	0.45528939366340637
MySQL Tutorial	0.22764469683170319
SHOW SESSION STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	2
Handler_read_rnd_deleted	0
Handler_read_rnd_next	14
SHOW SESSION STATUS LIKE 'Sort_rows%';
Variable_name	Value
Sort_rows	2
Sorting since no LIMIT clause
FLUSH STATUS;
SELECT MATCH(title, text) AGAINST ('database') AS score, title
FROM wp
ORDER BY score DESC;
score	title
0	1001 MySQL Tricks
0	How To Use MySQL Well
0	MySQL Security
0	Optimizing MySQL
0.22764469683170319	MySQL Tutorial
0.45528939366340637	MySQL vs. YourSQL
SHOW SESSION STATUS LIKE 'Sort_rows%';
Variable_name	Value
Sort_rows	6
Sorting since there is a WHERE clause
FLUSH STATUS;
SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE dummy IS NULL
ORDER BY score DESC LIMIT 2;
title	score
MySQL vs. YourSQL	0.45528939366340637
MySQL Tutorial	0.22764469683170319
SHOW SESSION STATUS LIKE 'Sort_rows%';
Variable_name	Value
Sort_rows	2
Sorting since ordering is not on a simple MATCH expressions
FLUSH STATUS;
SELECT title, (MATCH(title, text) AGAINST ('database')) * 100 AS score
FROM wp
ORDER BY score DESC LIMIT 2;
title	score
MySQL vs. YourSQL	45.52893936634064
MySQL Tutorial	22.76446968317032
SHOW SESSION STATUS LIKE 'Sort_rows%';
Variable_name	Value
Sort_rows	2
No ordinary handler accesses when only accessing FTS_DOC_ID and MATCH
FLUSH STATUS;
SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database');
docid	score
5	0.45528939366340637
1	0.22764469683170319
SHOW SESSION STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
Still no handler accesses when adding FTS_DOC_ID to WHERE clause
FLUSH STATUS;
SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database') AND FTS_DOC_ID > 2;
docid	score
5	0.45528939366340637
SHOW SESSION STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
Still no handler accesses when ordering by MATCH expression
FLUSH STATUS;
SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
ORDER BY score;
docid	score
1	0.22764469683170319
5	0.45528939366340637
SHOW SESSION STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	2
Handler_read_rnd_deleted	0
Handler_read_rnd_next	3
Optimization is disabled when ordering on FTS_DOC_ID
FLUSH STATUS;
SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
ORDER BY 1 DESC;
docid	score
5	0.45528939366340637
1	0.22764469683170319
SHOW SESSION STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	2
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
Optimization also work with several MATCH expressions
FLUSH STATUS;
SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score1,
MATCH(title, text) AGAINST ('mysql') AS score2
FROM wp
WHERE MATCH(title, text) AGAINST ('database');
docid	score1	score2
5	0.45528939366340637	0.000000001885928302414186
1	0.22764469683170319	0.000000003771856604828372
SHOW SESSION STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
Optimization does not apply if sorting on a different MATCH expressions
from the one used to access the
FLUSH STATUS;
SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score1,
MATCH(title, text) AGAINST ('mysql') AS score2
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
ORDER BY score2 DESC;
docid	score1	score2
1	0.22764469683170319	0.000000003771856604828372
5	0.45528939366340637	0.000000001885928302414186
SHOW SESSION STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	2
Handler_read_rnd_deleted	0
Handler_read_rnd_next	3
FLUSH STATUS;
Optimization does not apply for GROUP BY
SET @save_mode = @@sql_mode;
SET sql_mode = (select replace(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
GROUP BY score;
FTS_DOC_ID	score
1	0.22764469683170319
5	0.45528939366340637
SHOW SESSION STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	2
Handler_read_rnd_deleted	0
Handler_read_rnd_next	3
SET sql_mode = @save_mode;
No sorting and no table access with LIMIT clause and only information
from FTS result
FLUSH STATUS;
SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
FROM wp
ORDER BY score DESC LIMIT 2;
docid	score
5	0.45528939366340637
1	0.22764469683170319
SHOW STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	2
Handler_read_rnd_deleted	0
Handler_read_rnd_next	14
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	2
Sort_scan	1
If count optimization applies, EXPLAIN shows
"Select tables optimized away."
EXPLAIN SELECT COUNT(*)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	wp	fulltext	idx	idx	0		1	Using where
FLUSH STATUS;
SELECT COUNT(*)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
COUNT(*)
2
Verify that there was no table access
SHOW STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
Optimization applies also to COUNT(expr) as long as expr is not nullable
EXPLAIN SELECT COUNT(title)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	wp	fulltext	idx	idx	0		1	Using where
SELECT COUNT(title)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
COUNT(title)
2
Optimization does not apply if not a single table query.
EXPLAIN SELECT count(*)
FROM wp, t1
WHERE MATCH(title, text) AGAINST ('database');
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	wp	fulltext	idx	idx	0		1	Using where
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	
SELECT count(*)
FROM wp, t1
WHERE MATCH(title, text) AGAINST ('database');
count(*)
12
Optimization does not apply if MATCH is part of an expression
EXPLAIN SELECT COUNT(title)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE) > 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	wp	fulltext	idx	idx	0		1	Using where
SELECT COUNT(title)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE) > 0;
COUNT(title)
2
Optimization does not apply if MATCH is part of an expression
EXPLAIN SELECT COUNT(title)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE) > 0;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	wp	fulltext	idx	idx	0		1	Using where
SELECT COUNT(title)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE) > 0;
COUNT(title)
2
Optimization does not apply if COUNT expression is nullable
EXPLAIN SELECT COUNT(dummy)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	wp	fulltext	idx	idx	0		1	Using where
SELECT COUNT(dummy)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
COUNT(dummy)
0
FLUSH STATUS;
SELECT MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score,
title
FROM wp
WHERE MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION)
ORDER BY score DESC;
score	title
0.000000001885928302414186	1001 MySQL Tricks
0.000000001885928302414186	How To Use MySQL Well
0.000000003771856604828372	MySQL Security
0.22764469683170319	Optimizing MySQL
1.6663280725479126	MySQL Tutorial
2.2718474864959717	MySQL vs. YourSQL
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	6
Sort_scan	1
FLUSH STATUS;
SELECT title,
MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score
FROM wp
ORDER BY score DESC LIMIT 2;
title	score
MySQL vs. YourSQL	2.2718474864959717
MySQL Tutorial	1.6663280725479126
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	2
Sort_scan	1
FLUSH STATUS;
SELECT FTS_DOC_ID docid,
MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database');
docid	score
5	2.2718474864959717
1	1.6663280725479126
SHOW SESSION STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
FLUSH STATUS;
SELECT FTS_DOC_ID docid,
MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score
FROM wp
ORDER BY score DESC LIMIT 2;
docid	score
5	2.2718474864959717
1	1.6663280725479126
SHOW STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	2
Handler_read_rnd_deleted	0
Handler_read_rnd_next	14
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	2
Sort_scan	1
EXPLAIN SELECT COUNT(*)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' WITH QUERY EXPANSION);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	wp	fulltext	idx	idx	0		1	Using where
FLUSH STATUS;
SELECT COUNT(*)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' WITH QUERY EXPANSION);
COUNT(*)
6
SHOW STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
FLUSH STATUS;
SELECT MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) score,
title
FROM wp
WHERE MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE)
ORDER BY score DESC;
score	title
0.000000001885928302414186	1001 MySQL Tricks
0.000000001885928302414186	How To Use MySQL Well
0.000000001885928302414186	Optimizing MySQL
0.000000003771856604828372	MySQL Security
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	4
Sort_scan	1
FLUSH STATUS;
SELECT MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) score,
title
FROM wp
ORDER BY score DESC;
score	title
0	MySQL Tutorial
0	MySQL vs. YourSQL
0.000000001885928302414186	1001 MySQL Tricks
0.000000001885928302414186	How To Use MySQL Well
0.000000001885928302414186	Optimizing MySQL
0.000000003771856604828372	MySQL Security
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	6
Sort_scan	1
FLUSH STATUS;
SELECT FTS_DOC_ID docid,
MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('+MySQL -database');
docid	score
5	0
1	0
6	0.000000003771856604828372
2	0.000000001885928302414186
3	0.000000001885928302414186
4	0.000000001885928302414186
SHOW SESSION STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
FLUSH STATUS;
SELECT FTS_DOC_ID docid,
MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
FROM wp
ORDER BY score DESC LIMIT 1;
docid	score
6	0.000000003771856604828372
SHOW STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	1
Handler_read_rnd_deleted	0
Handler_read_rnd_next	14
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	1
Sort_scan	1
EXPLAIN SELECT COUNT(*)
FROM wp
WHERE MATCH(title,text) AGAINST ('+MySQL -database' IN BOOLEAN MODE);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	wp	fulltext	idx	idx	0		1	Using where
FLUSH STATUS;
SELECT COUNT(*)
FROM wp
WHERE MATCH(title,text) AGAINST ('+MySQL -database' IN BOOLEAN MODE);
COUNT(*)
4
SHOW STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
FLUSH STATUS;
SELECT title,
MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE)
ORDER BY score DESC;
title	score
MySQL Tutorial	0.22764469683170319
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	1
Sort_scan	1
FLUSH STATUS;
SELECT title,
MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score
FROM wp
ORDER BY score DESC LIMIT 1;
title	score
MySQL Tutorial	0.22764469683170319
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	1
Sort_scan	1
FLUSH STATUS;
SELECT FTS_DOC_ID docid,
MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('"MySQL database"@5');
docid	score
1	0.22764469683170319
SHOW SESSION STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
FLUSH STATUS;
SELECT FTS_DOC_ID docid,
MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score
FROM wp
ORDER BY score DESC LIMIT 1;
docid	score
1	0.22764469683170319
SHOW STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	1
Handler_read_rnd_deleted	0
Handler_read_rnd_next	14
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	1
Sort_scan	1
EXPLAIN SELECT COUNT(*)
FROM wp
WHERE MATCH(title,text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	wp	fulltext	idx	idx	0		1	Using where
FLUSH STATUS;
SELECT COUNT(*)
FROM wp
WHERE MATCH(title,text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE);
COUNT(*)
1
SHOW STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
SELECT title,
MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION)
ORDER BY score DESC, title ASC;
title	score
MySQL vs. YourSQL	0.45528939366340637
MySQL Tutorial	0.22764469683170319
1001 MySQL Tricks	0
How To Use MySQL Well	0
MySQL Security	0
Optimizing MySQL	0
SELECT title,
MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('MySQL database' WITH QUERY EXPANSION)
ORDER BY score DESC, title ASC;
title	score
MySQL Security	0.000000003771856604828372
1001 MySQL Tricks	0.000000001885928302414186
How To Use MySQL Well	0.000000001885928302414186
Optimizing MySQL	0.000000001885928302414186
MySQL Tutorial	0
MySQL vs. YourSQL	0
SELECT title,
MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE)
ORDER BY score DESC, title ASC;
title	score
MySQL Tutorial	0
ALTER TABLE wp ENGINE=myisam;
FLUSH STATUS;
SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database')
ORDER BY score DESC;
title	score
MySQL vs. YourSQL	0.9562782645225525
MySQL Tutorial	0.5756555199623108
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	0
Sort_range	0
Sort_rows	2
Sort_scan	1
FLUSH STATUS;
SELECT title, MATCH(title, text) AGAINST ('database') AS score
FROM wp
ORDER BY score DESC LIMIT 2;
title	score
MySQL vs. YourSQL	0.9562782645225525
MySQL Tutorial	0.5756555199623108
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	2
Sort_scan	1
FLUSH STATUS;
SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
FROM wp
WHERE MATCH(title, text) AGAINST ('database');
docid	score
5	0.9562782645225525
1	0.5756555199623108
SHOW SESSION STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	3
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
FLUSH STATUS;
SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
FROM wp
ORDER BY score DESC LIMIT 2;
docid	score
5	0.9562782645225525
1	0.5756555199623108
SHOW STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	2
Handler_read_rnd_deleted	0
Handler_read_rnd_next	14
SHOW SESSION STATUS LIKE 'Sort%';
Variable_name	Value
Sort_merge_passes	0
Sort_priority_queue_sorts	1
Sort_range	0
Sort_rows	2
Sort_scan	1
EXPLAIN SELECT COUNT(*)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	wp	fulltext	idx	idx	0		1	Using where
FLUSH STATUS;
SELECT COUNT(*)
FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
COUNT(*)
2
SHOW STATUS LIKE 'Handler_read%';
Variable_name	Value
Handler_read_first	0
Handler_read_key	0
Handler_read_last	0
Handler_read_next	3
Handler_read_prev	0
Handler_read_retry	0
Handler_read_rnd	0
Handler_read_rnd_deleted	0
Handler_read_rnd_next	0
DROP TABLE wp, t1;
CREATE TABLE t1
(
FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(255) DEFAULT '',
text MEDIUMTEXT ,
PRIMARY KEY (FTS_DOC_ID),
UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
FULLTEXT KEY ft_idx (title,text)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO t1 (title, text) VALUES
('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL database','In the following database to database comparison ...'),
('MySQL Security','When configured properly, MySQL ...'),
('InnoDB', 'InnoDB is a transaction-safe (ACID compliant) storage engine'),
('MySQL is a database management system', 'A database is a structured collection of data...'),
('MySQL databases are relational', 'A relational database stores data in separate tables rather than putting all the data in one big storeroom...'),
('MySQL software is Open Source', 'Open Source means that it is possible for anyone to use and modify the software...'),
('The MySQL Database Server is very fast, reliable, scalable, and easy to use', 'MySQL Server can run comfortably on a desktop or laptop...'),
('MySQL Server works in client/server or embedded systems', 'The MySQL Database Software is a client/server system...'),
('MyISAM', 'MyISAM is based on the older (and no longer available) ISAM storage engine but has many useful extensions'),
('A large amount of contributed MySQL software is available', 'MySQL Server has a practical set of features developed in close cooperation with our users'),
(NULL,NULL);
ANALYZE TABLE t1;
# No ranking
EXPLAIN
SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where
SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE);
count(*)
6
EXPLAIN
SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('data*' IN BOOLEAN MODE) ORDER BY title LIMIT 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where
SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('data*' IN BOOLEAN MODE) ORDER BY title LIMIT 3;
count(*)
6
EXPLAIN
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);
FTS_DOC_ID	title
11	The MySQL Database Server is very fast, reliable, scalable, and easy to use
EXPLAIN
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION);
FTS_DOC_ID	title
11	The MySQL Database Server is very fast, reliable, scalable, and easy to use
12	MySQL Server works in client/server or embedded systems
10	MySQL software is Open Source
4	1001 MySQL Tricks
14	A large amount of contributed MySQL software is available
2	How To Use MySQL Well
13	MyISAM
5	MySQL vs. YourSQL database
8	MySQL is a database management system
1	MySQL Tutorial
9	MySQL databases are relational
6	MySQL Security
3	Optimizing MySQL
EXPLAIN
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE);
FTS_DOC_ID	title
11	The MySQL Database Server is very fast, reliable, scalable, and easy to use
EXPLAIN SELECT FTS_DOC_ID  FROM t1
WHERE MATCH(title, text) AGAINST ('+for' IN BOOLEAN MODE);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where
SELECT FTS_DOC_ID  FROM t1
WHERE MATCH(title, text) AGAINST ('+for' IN BOOLEAN MODE);
FTS_DOC_ID
# No sorting by rank
EXPLAIN SELECT FTS_DOC_ID, TITLE FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY title;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where; Using filesort
SELECT FTS_DOC_ID, TITLE FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY title;
FTS_DOC_ID	TITLE
9	MySQL databases are relational
8	MySQL is a database management system
12	MySQL Server works in client/server or embedded systems
1	MySQL Tutorial
5	MySQL vs. YourSQL database
11	The MySQL Database Server is very fast, reliable, scalable, and easy to use
EXPLAIN SELECT FTS_DOC_ID FROM t1
WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where
SELECT FTS_DOC_ID  FROM t1
WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);
FTS_DOC_ID
11
EXPLAIN
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where; Using filesort
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title;
FTS_DOC_ID	title
4	1001 MySQL Tricks
14	A large amount of contributed MySQL software is available
2	How To Use MySQL Well
13	MyISAM
9	MySQL databases are relational
8	MySQL is a database management system
6	MySQL Security
12	MySQL Server works in client/server or embedded systems
10	MySQL software is Open Source
1	MySQL Tutorial
5	MySQL vs. YourSQL database
3	Optimizing MySQL
11	The MySQL Database Server is very fast, reliable, scalable, and easy to use
EXPLAIN
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where; Using filesort
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title;
FTS_DOC_ID	title
11	The MySQL Database Server is very fast, reliable, scalable, and easy to use
# LIMIT optimization
EXPLAIN SELECT FTS_DOC_ID, TITLE FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
LIMIT 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where
SELECT FTS_DOC_ID, TITLE FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
LIMIT 3;
FTS_DOC_ID	TITLE
11	The MySQL Database Server is very fast, reliable, scalable, and easy to use
5	MySQL vs. YourSQL database
8	MySQL is a database management system
EXPLAIN SELECT FTS_DOC_ID FROM t1
WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
LIMIT 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where
SELECT FTS_DOC_ID  FROM t1
WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
LIMIT 3;
FTS_DOC_ID
11
EXPLAIN SELECT FTS_DOC_ID FROM t1
WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
ORDER BY title
LIMIT 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where; Using filesort
SELECT FTS_DOC_ID  FROM t1
WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
ORDER BY title
LIMIT 3;
FTS_DOC_ID
11
EXPLAIN
SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title LIMIT 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where; Using filesort
SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title LIMIT 1;
FTS_DOC_ID
4
EXPLAIN
SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title LIMIT 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where; Using filesort
SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title LIMIT 1;
FTS_DOC_ID
11
EXPLAIN
SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY rank, FTS_DOC_ID
LIMIT 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where; Using temporary; Using filesort
SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY rank, FTS_DOC_ID
LIMIT 3;
FTS_DOC_ID	rank
1	0.15835624933242798
9	0.15835624933242798
12	0.15835624933242798
EXPLAIN
SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY rank DESC, FTS_DOC_ID ASC
LIMIT 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where; Using temporary; Using filesort
SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY rank DESC, FTS_DOC_ID ASC
LIMIT 3;
FTS_DOC_ID	rank
11	1.5415468215942383
5	0.47506874799728394
8	0.31671249866485596
EXPLAIN SELECT FTS_DOC_ID,  MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
FROM t1
ORDER BY rank DESC
LIMIT 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	15	Using temporary; Using filesort
SELECT FTS_DOC_ID,  MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
FROM t1
ORDER BY rank DESC
LIMIT 2;
FTS_DOC_ID	rank
5	0.47506874799728394
8	0.31671249866485596
EXPLAIN SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
FROM t1
ORDER BY rank DESC
LIMIT 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	15	Using temporary; Using filesort
SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
FROM t1
ORDER BY rank DESC
LIMIT 3;
FTS_DOC_ID	rank
11	1.5415468215942383
5	0.47506874799728394
8	0.31671249866485596
EXPLAIN SELECT FTS_DOC_ID,  MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE)
ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE)  DESC,
FTS_DOC_ID ASC;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where; Using temporary; Using filesort
SELECT FTS_DOC_ID,  MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE)
ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE)  DESC,
FTS_DOC_ID ASC;
FTS_DOC_ID	rank
5	0.47506874799728394
8	0.31671249866485596
1	0.15835624933242798
9	0.15835624933242798
11	0.15835624933242798
12	0.15835624933242798
EXPLAIN SELECT FTS_DOC_ID,  MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) and FTS_DOC_ID > 1
ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE)  DESC
LIMIT 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	PRIMARY,FTS_DOC_ID_INDEX,ft_idx	ft_idx	0		1	Using where; Using temporary; Using filesort
SELECT FTS_DOC_ID,  MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) and FTS_DOC_ID > 1
ORDER BY MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE)  DESC
LIMIT 2;
FTS_DOC_ID	rank
5	0.47506874799728394
8	0.31671249866485596
EXPLAIN
SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
ORDER BY rank
LIMIT 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where; Using temporary; Using filesort
SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
ORDER BY rank
LIMIT 1;
FTS_DOC_ID	rank
3	0.009391550906002522
EXPLAIN
SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
ORDER BY rank DESC
LIMIT 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where; Using temporary; Using filesort
SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
ORDER BY rank DESC
LIMIT 1;
FTS_DOC_ID	rank
11	15.345823287963867
EXPLAIN
SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
ORDER BY  MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
LIMIT 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where; Using temporary; Using filesort
SELECT FTS_DOC_ID,MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) as rank
FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION)
ORDER BY  MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) DESC
LIMIT 1;
FTS_DOC_ID	rank
11	15.345823287963867
# WHERE optimization on MATCH > 'some_rank'
EXPLAIN SELECT FTS_DOC_ID FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) > 0.1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where
SELECT FTS_DOC_ID FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) > 0.1;
FTS_DOC_ID
11
5
8
1
9
12
# additional test for correct behaviour
EXPLAIN SELECT * FROM t1 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC LIMIT 10;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	15	Using temporary; Using filesort
SELECT FTS_DOC_ID FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) AND
MATCH (title, text) AGAINST ('mysql' IN NATURAL LANGUAGE MODE)
LIMIT 6;
FTS_DOC_ID
11
5
8
1
9
12
# test OR condition
SELECT FTS_DOC_ID
FROM t1
WHERE MATCH(title, text) AGAINST ('database')
OR MATCH(title, text) AGAINST ('mysql')
ORDER BY  MATCH(title, text) AGAINST ('database') DESC, FTS_DOC_ID ASC;
FTS_DOC_ID
5
8
1
9
11
12
2
3
4
6
10
14
EXPLAIN SELECT FTS_DOC_ID
FROM t1
WHERE MATCH(title, text) AGAINST ('database')
OR MATCH(title, text) AGAINST ('mysql')
ORDER BY  MATCH(title, text) AGAINST ('database') DESC, FTS_DOC_ID ASC;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	15	Using where; Using temporary; Using filesort
# MATCH and GROUP BY, DISTINCT
SET sql_mode = (select replace(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
EXPLAIN SELECT FTS_DOC_ID FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
GROUP BY FTS_DOC_ID
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where; Using temporary; Using filesort
SELECT FTS_DOC_ID FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
GROUP BY FTS_DOC_ID
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;
FTS_DOC_ID
11
5
8
EXPLAIN SELECT FTS_DOC_ID FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
GROUP BY title
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where; Using temporary; Using filesort
SELECT FTS_DOC_ID FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
GROUP BY title
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;
FTS_DOC_ID
11
5
8
EXPLAIN SELECT MAX(FTS_DOC_ID) FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where
SELECT MAX(FTS_DOC_ID) FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;
MAX(FTS_DOC_ID)
12
EXPLAIN SELECT DISTINCT(title) FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where; Using temporary; Using filesort
SELECT DISTINCT(title) FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;
title
The MySQL Database Server is very fast, reliable, scalable, and easy to use
MySQL vs. YourSQL database
MySQL is a database management system
EXPLAIN SELECT DISTINCT(FTS_DOC_ID) FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	ft_idx	ft_idx	0		1	Using where; Using temporary; Using filesort
SELECT DISTINCT(FTS_DOC_ID) FROM t1
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC
LIMIT 3;
FTS_DOC_ID
11
5
8
SET sql_mode = @save_mode;
# FTS index access
SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
FROM t1
ORDER BY rank DESC
LIMIT 2;
FTS_DOC_ID	rank
5	0.47506874799728394
8	0.31671249866485596
EXPLAIN SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
FROM t1
ORDER BY rank DESC
LIMIT 2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	15	Using temporary; Using filesort
SELECT a.FTS_DOC_ID, b.FTS_DOC_ID
FROM t1 a, t1 b
WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and
MATCH(b.title, b.text) AGAINST ('+mysql' IN BOOLEAN MODE) and
a.FTS_DOC_ID = b.FTS_DOC_ID;
FTS_DOC_ID	FTS_DOC_ID
5	5
8	8
1	1
9	9
11	11
12	12
EXPLAIN SELECT a.FTS_DOC_ID, b.FTS_DOC_ID
FROM t1 a, t1 b
WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and
MATCH(b.title, b.text) AGAINST ('+mysql' IN BOOLEAN MODE)  and
a.FTS_DOC_ID = b.FTS_DOC_ID;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	a	fulltext	PRIMARY,FTS_DOC_ID_INDEX,ft_idx	ft_idx	0		1	Using where
1	SIMPLE	b	fulltext	PRIMARY,FTS_DOC_ID_INDEX,ft_idx	ft_idx	0		1	Using where
SELECT a.FTS_DOC_ID, MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE),
b.FTS_DOC_ID, MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE)
FROM t1 a, t1 b
WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and
MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE);
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)
5	0.47506874799728394	5	0.47506874799728394
5	0.47506874799728394	8	0.31671249866485596
5	0.47506874799728394	1	0.15835624933242798
5	0.47506874799728394	9	0.15835624933242798
5	0.47506874799728394	11	0.15835624933242798
5	0.47506874799728394	12	0.15835624933242798
8	0.31671249866485596	5	0.47506874799728394
8	0.31671249866485596	8	0.31671249866485596
8	0.31671249866485596	1	0.15835624933242798
8	0.31671249866485596	9	0.15835624933242798
8	0.31671249866485596	11	0.15835624933242798
8	0.31671249866485596	12	0.15835624933242798
1	0.15835624933242798	5	0.47506874799728394
1	0.15835624933242798	8	0.31671249866485596
1	0.15835624933242798	1	0.15835624933242798
1	0.15835624933242798	9	0.15835624933242798
1	0.15835624933242798	11	0.15835624933242798
1	0.15835624933242798	12	0.15835624933242798
9	0.15835624933242798	5	0.47506874799728394
9	0.15835624933242798	8	0.31671249866485596
9	0.15835624933242798	1	0.15835624933242798
9	0.15835624933242798	9	0.15835624933242798
9	0.15835624933242798	11	0.15835624933242798
9	0.15835624933242798	12	0.15835624933242798
11	0.15835624933242798	5	0.47506874799728394
11	0.15835624933242798	8	0.31671249866485596
11	0.15835624933242798	1	0.15835624933242798
11	0.15835624933242798	9	0.15835624933242798
11	0.15835624933242798	11	0.15835624933242798
11	0.15835624933242798	12	0.15835624933242798
12	0.15835624933242798	5	0.47506874799728394
12	0.15835624933242798	8	0.31671249866485596
12	0.15835624933242798	1	0.15835624933242798
12	0.15835624933242798	9	0.15835624933242798
12	0.15835624933242798	11	0.15835624933242798
12	0.15835624933242798	12	0.15835624933242798
EXPLAIN SELECT a.FTS_DOC_ID, MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE),
b.FTS_DOC_ID, MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE)
FROM t1 a, t1 b
WHERE MATCH(a.title, a.text) AGAINST ('+database' IN BOOLEAN MODE) and
MATCH(b.title, b.text) AGAINST ('+database' IN BOOLEAN MODE);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	a	fulltext	ft_idx	ft_idx	0		1	Using where
1	SIMPLE	b	fulltext	ft_idx	ft_idx	0		1	Using where
EXPLAIN SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ("data*" IN BOOLEAN MODE) * 100;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	15	Using where
SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ("data*" IN BOOLEAN MODE) * 100
FROM t1 WHERE MATCH(title, text) AGAINST ("data*" IN BOOLEAN MODE) * 100;
FTS_DOC_ID	MATCH(title, text) AGAINST ("data*" IN BOOLEAN MODE) * 100
1	4.92168664932251
5	14.76505994796753
8	9.84337329864502
9	4.92168664932251
11	4.92168664932251
12	4.92168664932251
SELECT * FROM t1 WHERE title IS NULL AND text IS NULL;
FTS_DOC_ID	title	text
15	NULL	NULL
CREATE TABLE t2 SELECT FTS_DOC_ID as doc_id, title, text FROM t1;
ALTER TABLE t2 ADD PRIMARY KEY (doc_id);
ALTER TABLE t2 ADD FULLTEXT KEY ft_idx (title,text);
ANALYZE TABLE t2;
EXPLAIN SELECT DOC_ID FROM t2 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	15	Using where
SELECT DOC_ID FROM t2 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
DOC_ID
1
5
8
9
11
12
EXPLAIN SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	15	Using where
SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
FTS_DOC_ID
1
5
8
9
11
12
DROP TABLE t1, t2;
"Check hints with uft8 charset for 2 cases"
set names utf8;
CREATE TABLE t1 (
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
text TEXT
) CHARACTER SET = utf8, ENGINE=InnoDB;
INSERT INTO t1 (title, text) VALUES
('Я могу есть стекло', 'оно мне не вредит'),
('Мога да ям стъкло', 'то не ми вреди'),
('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'),
('Příliš žluťoučký kůň', 'úpěl ďábelské kódy'),
('Sævör grét', 'áðan því úlpan var ónýt'),
('うゐのおくやま','けふこえて'),
('いろはにほへど　ちりぬる','あさきゆめみじ　ゑひもせず');
CREATE FULLTEXT INDEX idx on t1 (title, text);
# No ranking
EXPLAIN
SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('вредит' IN NATURAL LANGUAGE MODE);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	idx	idx	0		1	Using where
SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('вредит' IN NATURAL LANGUAGE MODE);
count(*)
1
EXPLAIN
SELECT * FROM t1 WHERE MATCH(title, text) AGAINST ("оно" WITH QUERY EXPANSION);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	idx	idx	0		1	Using where
SELECT * FROM t1 WHERE MATCH(title, text) AGAINST ("оно" WITH QUERY EXPANSION);
FTS_DOC_ID	title	text
1	Я могу есть стекло	оно мне не вредит
# No sorting by rank
EXPLAIN SELECT FTS_DOC_ID FROM t1
WHERE MATCH(title, text) AGAINST ('+(Мога τίποτα)' IN BOOLEAN MODE);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	idx	idx	0		1	Using where
SELECT FTS_DOC_ID  FROM t1
WHERE MATCH(title, text) AGAINST ('+(Мога τίποτα)' IN BOOLEAN MODE);
FTS_DOC_ID
2
3
DROP TABLE t1;
#
# Bug #18924341 CRASH IN TEST_IF_SKIP_SORT_ORDER, GROUP BY MATCH AGAINST DESC
#
CREATE TABLE t1 (f1 CHAR(1), FULLTEXT KEY (f1));
SELECT 1 FROM t1 NATURAL JOIN t1 a GROUP BY MATCH(t1.f1) AGAINST ("1") DESC;
1
DROP TABLE t1;
#
# Bug#20261601 ASSERTION FAILED: !FIRST_QEP_TAB->TABLE()->NO_KEYREAD
#
CREATE TABLE t1(a INT PRIMARY KEY);
INSERT INTO t1 VALUES(1),(2);
SELECT (SELECT MATCH(`a`)AGAINST('1') FROM t1) FROM t1;
ERROR HY000: Can't find FULLTEXT index matching the column list
SELECT 1, a IN (SELECT a FROM t1) FROM t1;
1	a IN (SELECT a FROM t1)
1	1
1	1
DROP TABLE t1;
#
# Bug#20442572 ASSERTION `!FIRST_QEP_TAB->TABLE()->NO_KEYREAD' FAILED.
# Bug#75688 Assertion `!first_qep_tab->table()->no_keyread' failed.
#
CREATE TABLE t1(a INT,b POINT NOT NULL,KEY(a));
HANDLER t1 OPEN;
select * from t1 where MATCH a,b AGAINST('"Now sUPPort"' IN BOOLEAN MODE);
a	b
prepare stmt1 from "truncate t1";
SELECT a IN(SELECT a FROM t1)FROM t1;
a IN(SELECT a FROM t1)
deallocate prepare stmt1;
DROP TABLE t1;
#
# Bug #20685427 INVALID WRITE OF FREED MEMORY IN ITEM_FUNC_MATCH::CLEANUP
#
CREATE TABLE t1(a TEXT CHARSET LATIN1, FULLTEXT KEY(a)) ENGINE=INNODB;
SELECT MATCH(a) AGAINST ('') FROM (SELECT a FROM t1 LIMIT 1) q;
ERROR HY000: Can't find FULLTEXT index matching the column list
DROP TABLE t1;
#
# Bug#21140067 EXPLAIN .. MATCH AGAINST: ASSERTION FAILED: TO <= END
#
CREATE TABLE t1(f1 CHAR(1) CHARSET latin1, FULLTEXT(f1)) ENGINE=INNODB;
EXPLAIN SELECT 1 FROM t1 WHERE 1.238585e+308 <= MATCH(f1) AGAINST ('1' IN BOOLEAN MODE);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	fulltext	f1	f1	0		1	Using where
EXPLAIN FORMAT = JSON SELECT 1 FROM t1 WHERE 1.238585e+308 <= MATCH(f1) AGAINST ('1' IN BOOLEAN MODE);
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost": 0.00009287,
    "nested_loop": [
      {
        "table": {
          "table_name": "t1",
          "access_type": "fulltext",
          "possible_keys": ["f1"],
          "key": "f1",
          "key_length": "0",
          "used_key_parts": ["f1"],
          "loops": 1,
          "rows": 1,
          "cost": 0.00009287,
          "filtered": 100,
          "attached_condition": "1.238585e+308 <= (match t1.f1 against ('1' in boolean mode))"
        }
      }
    ]
  }
}
DROP TABLE t1;
#
# Bug#21140088 MATCH AGAINST: ASSERTION FAILED: !TABLE || (!TABLE->READ_SET || BITMAP_IS_SET
#
SET sql_mode='';
CREATE TABLE t1(a INT) ENGINE=INNODB;
CREATE TABLE t2(b TEXT CHARSET LATIN1, FULLTEXT(b), PRIMARY KEY(b(10))) ENGINE=INNODB;
INSERT INTO t2 VALUES ('a'),('b');
SELECT NOT EXISTS (SELECT MATCH(b) AGAINST ('1') FROM t1) FROM t2 GROUP BY "a";
ERROR HY000: Incorrect arguments to MATCH
DROP TABLE t1, t2;
CREATE TABLE t1(a INT) ENGINE=MyISAM;
CREATE TABLE t2(b TEXT CHARSET LATIN1, FULLTEXT(b), PRIMARY KEY(b(10))) ENGINE=MyISAM;
INSERT INTO t2 VALUES ('a'),('b');
SELECT NOT EXISTS (SELECT MATCH(b) AGAINST ('1' in BOOLEAN MODE) FROM t1) FROM t2 GROUP BY "a";
NOT EXISTS (SELECT MATCH(b) AGAINST ('1' in BOOLEAN MODE) FROM t1)
1
DROP TABLE t1, t2;
SET sql_mode=default;
#
# Bug#21140039 ASSERTION FAILED: !FIRST_QEP_TAB->TABLE()->NO_KEYREAD MATCH AGAINST.....
#
CREATE TABLE t1
(
a INT,
b INT,
c CHAR(1) CHARSET latin1,
PRIMARY KEY (b,a),
FULLTEXT KEY (c)
) ENGINE=INNODB;
SELECT "a" NOT IN(SELECT b FROM t1 WHERE MATCH(c) AGAINST ('a' IN BOOLEAN MODE));
"a" NOT IN(SELECT b FROM t1 WHERE MATCH(c) AGAINST ('a' IN BOOLEAN MODE))
1
DROP TABLE t1;
#
# Bug#21300774 ASSERT `!INIT_FTFUNCS(THD, SELECT_LEX)` IN JOIN::RESET AT SQL/SQL_SELECT.CC:874
#
CREATE TABLE t1 (f1 INT);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
CREATE TABLE t2 (ft TEXT, FULLTEXT KEY ft(ft));
INSERT INTO t2 VALUES ('abc');
INSERT INTO t2 VALUES ('def');
UPDATE t1 SET f1 =
(SELECT t1.f1 FROM t2 WHERE NOT TRUE AND
MATCH (ft) AGAINST ((SELECT 'xyz' FROM t2)));
ERROR 21000: Subquery returns more than 1 row
DROP TABLE t1, t2;
#
# Bug#22679209: FULL-TEXT QUERIES WITH ADDITIONAL SECONDARY INDEX
#               GIVES NULL OR ZERO ROWS
#
CREATE TABLE t1 (
f1 INTEGER,
title varchar(255),
body mediumtext,
KEY f1 (f1),
FULLTEXT KEY title (title),
FULLTEXT KEY body (body)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES
(1, 'Insert into table', 'insert into table select from'),
(1, 'Delete from table', 'insert into table select from'),
(1, 'Update',            'perform update'),
(2, 'Insert into table', 'insert into table select from'),
( 2, 'Delete from table', 'some body text here'),
( 2, 'Update',            'perform update'),
( 3, 'Insert into table', 'insert into table select from'),
( 3, 'Delete from table', 'some body text here');
SELECT f1 FROM t1 WHERE f1=1 AND
(MATCH (title) AGAINST ('table' IN BOOLEAN MODE) OR
MATCH (body) AGAINST ('table' IN BOOLEAN MODE));
f1
1
1
DROP TABLE t1;
# End of test for Bug#22679209