mirror of
https://github.com/MariaDB/server.git
synced 2025-02-07 06:12:18 +01:00
1657 lines
55 KiB
Text
1657 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 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
|