mirror of
https://github.com/MariaDB/server.git
synced 2025-02-10 23:45:34 +01:00
228b7e4db5
This imports and adapts a number of MySQL 5.7 test cases that are applicable to MariaDB. Some tests for old bug fixes are not that relevant because the code has been refactored since then (especially starting with MariaDB Server 10.6), and the tests would not reproduce the original bug if the fix was reverted. In the test innodb_fts.opt, there are many duplicate MATCH ranks, which would make the results nondeterministic. The test was stabilized by changing some LIMIT clauses or by adding sorted_result in those cases where the purpose of a test was to show that no sorting took place in the server. In the test innodb_fts.phrase, MySQL 5.7 would generate FTS_DOC_ID that are 1 larger than in MariaDB. In innodb_fts.index_table the difference is 2. This is because in MariaDB, fts_get_next_doc_id() post-increments cache->next_doc_id, while MySQL 5.7 pre-increments it. Reviewed by: Thirunarayanan Balathandayuthapani
1650 lines
55 KiB
Text
1650 lines
55 KiB
Text
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 eq_ref PRIMARY,FTS_DOC_ID_INDEX,ft_idx PRIMARY 8 test.a.FTS_DOC_ID 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,
|
||
"table": {
|
||
"table_name": "t1",
|
||
"access_type": "fulltext",
|
||
"possible_keys": ["f1"],
|
||
"key": "f1",
|
||
"key_length": "0",
|
||
"used_key_parts": ["f1"],
|
||
"rows": 1,
|
||
"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
|