mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 19:06:14 +01:00
0a5e4a0191
Updated tests: cases with bugs or which cannot be run with the cursor-protocol were excluded with "--disable_cursor_protocol"/"--enable_cursor_protocol" Fix for v.10.5
1088 lines
34 KiB
Text
1088 lines
34 KiB
Text
--source include/have_innodb.inc
|
||
|
||
#
|
||
# Tests for optimizations for InnoDB fulltext search (WL#6043)
|
||
#
|
||
|
||
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;
|
||
SET STATEMENT use_stat_tables=never FOR
|
||
ANALYZE TABLE wp;
|
||
|
||
--disable_cursor_protocol
|
||
--disable_ps2_protocol
|
||
|
||
#
|
||
# Show results of MATCH expressions for reference
|
||
#
|
||
SELECT FTS_DOC_ID, title, MATCH(title, text) AGAINST ('database') AS score1,
|
||
MATCH(title, text) AGAINST ('mysql') AS score2
|
||
FROM wp;
|
||
|
||
#
|
||
# Test that filesort is not used if ordering on same match expression
|
||
# as where clause
|
||
#
|
||
--echo 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;
|
||
|
||
SHOW SESSION STATUS LIKE 'Sort%';
|
||
|
||
--echo 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;
|
||
|
||
SHOW SESSION STATUS LIKE 'Sort%';
|
||
|
||
--echo No sorting even if there are several MATCH expressions as long as the
|
||
--echo 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;
|
||
|
||
SHOW SESSION STATUS LIKE 'Sort%';
|
||
|
||
--echo 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;
|
||
|
||
SHOW SESSION STATUS LIKE 'Sort_rows%';
|
||
|
||
--echo Sorting since there is no WHERE clause
|
||
FLUSH STATUS;
|
||
|
||
--sorted_result
|
||
SELECT MATCH(title, text) AGAINST ('database'), title AS score
|
||
FROM wp
|
||
ORDER BY score DESC;
|
||
|
||
SHOW SESSION STATUS LIKE 'Sort_rows%';
|
||
|
||
--echo 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;
|
||
|
||
SHOW SESSION STATUS LIKE 'Sort_rows%';
|
||
|
||
--echo 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;
|
||
|
||
SHOW SESSION STATUS LIKE 'Sort_rows%';
|
||
|
||
--echo 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;
|
||
|
||
SHOW SESSION STATUS LIKE 'Sort_rows%';
|
||
|
||
#
|
||
# Tests for ORDER BY/LIMIT optimzation
|
||
#
|
||
--echo No sorting for this query
|
||
FLUSH STATUS;
|
||
|
||
SELECT title, MATCH(title, text) AGAINST ('database') AS score
|
||
FROM wp
|
||
ORDER BY score DESC LIMIT 2;
|
||
|
||
SHOW SESSION STATUS LIKE 'Sort%';
|
||
|
||
--echo Revert to table scan and sorting for this query since not
|
||
--echo 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;
|
||
|
||
SHOW SESSION STATUS LIKE 'Handler_read%';
|
||
SHOW SESSION STATUS LIKE 'Sort_rows%';
|
||
|
||
--echo Sorting since no LIMIT clause
|
||
FLUSH STATUS;
|
||
|
||
--sorted_result
|
||
SELECT MATCH(title, text) AGAINST ('database') AS score, title
|
||
FROM wp
|
||
ORDER BY score DESC;
|
||
|
||
SHOW SESSION STATUS LIKE 'Sort_rows%';
|
||
|
||
--echo 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;
|
||
|
||
SHOW SESSION STATUS LIKE 'Sort_rows%';
|
||
|
||
--echo 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;
|
||
|
||
SHOW SESSION STATUS LIKE 'Sort_rows%';
|
||
|
||
#
|
||
# Test that there is no row accesses if all necessary information is
|
||
# available in FTS result
|
||
#
|
||
--echo 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');
|
||
|
||
SHOW SESSION STATUS LIKE 'Handler_read%';
|
||
|
||
--echo 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;
|
||
|
||
SHOW SESSION STATUS LIKE 'Handler_read%';
|
||
|
||
--echo 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;
|
||
|
||
SHOW SESSION STATUS LIKE 'Handler_read%';
|
||
|
||
--echo 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;
|
||
|
||
SHOW SESSION STATUS LIKE 'Handler_read%';
|
||
|
||
--echo 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');
|
||
|
||
SHOW SESSION STATUS LIKE 'Handler_read%';
|
||
|
||
--echo Optimization does not apply if sorting on a different MATCH expressions
|
||
--echo 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;
|
||
|
||
SHOW SESSION STATUS LIKE 'Handler_read%';
|
||
|
||
FLUSH STATUS;
|
||
|
||
--echo 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;
|
||
|
||
SHOW SESSION STATUS LIKE 'Handler_read%';
|
||
SET sql_mode = @save_mode;
|
||
|
||
#
|
||
# Putting all three optimizations together
|
||
#
|
||
--echo No sorting and no table access with LIMIT clause and only information
|
||
--echo 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;
|
||
|
||
SHOW STATUS LIKE 'Handler_read%';
|
||
SHOW SESSION STATUS LIKE 'Sort%';
|
||
|
||
#
|
||
# Count optimization
|
||
#
|
||
let $query =
|
||
SELECT COUNT(*)
|
||
FROM wp
|
||
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
|
||
|
||
--echo If count optimization applies, EXPLAIN shows
|
||
--echo "Select tables optimized away."
|
||
eval EXPLAIN $query;
|
||
FLUSH STATUS;
|
||
eval $query;
|
||
--echo Verify that there was no table access
|
||
SHOW STATUS LIKE 'Handler_read%';
|
||
|
||
let $query =
|
||
SELECT COUNT(title)
|
||
FROM wp
|
||
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
|
||
|
||
--echo Optimization applies also to COUNT(expr) as long as expr is not nullable
|
||
eval EXPLAIN $query;
|
||
eval $query;
|
||
|
||
let $query =
|
||
SELECT count(*)
|
||
FROM wp, t1
|
||
WHERE MATCH(title, text) AGAINST ('database');
|
||
|
||
--echo Optimization does not apply if not a single table query.
|
||
eval EXPLAIN $query;
|
||
eval $query;
|
||
|
||
let $query =
|
||
SELECT COUNT(title)
|
||
FROM wp
|
||
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE) > 0;
|
||
|
||
--echo Optimization does not apply if MATCH is part of an expression
|
||
eval EXPLAIN $query;
|
||
eval $query;
|
||
|
||
let $query =
|
||
SELECT COUNT(title)
|
||
FROM wp
|
||
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE) > 0;
|
||
|
||
--echo Optimization does not apply if MATCH is part of an expression
|
||
eval EXPLAIN $query;
|
||
eval $query;
|
||
|
||
let $query =
|
||
SELECT COUNT(dummy)
|
||
FROM wp
|
||
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
|
||
|
||
--echo Optimization does not apply if COUNT expression is nullable
|
||
eval EXPLAIN $query;
|
||
eval $query;
|
||
|
||
#
|
||
# Verify that the queries optimized for InnoDB works with QUERY EXPANSION
|
||
#
|
||
|
||
# Query will also avoid sorting when query expansion is used
|
||
FLUSH STATUS;
|
||
--sorted_result
|
||
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;
|
||
SHOW SESSION STATUS LIKE 'Sort%';
|
||
|
||
# Check ORDER BY/LIMIT query with no WHERE clause
|
||
FLUSH STATUS;
|
||
SELECT title,
|
||
MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score
|
||
FROM wp
|
||
ORDER BY score DESC LIMIT 2;
|
||
SHOW SESSION STATUS LIKE 'Sort%';
|
||
|
||
# Check query where FTS result is "covering"
|
||
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');
|
||
SHOW SESSION STATUS LIKE 'Handler_read%';
|
||
|
||
# Check the combination of all three
|
||
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;
|
||
SHOW STATUS LIKE 'Handler_read%';
|
||
SHOW SESSION STATUS LIKE 'Sort%';
|
||
|
||
# Check the count optimization
|
||
let $query =
|
||
SELECT COUNT(*)
|
||
FROM wp
|
||
WHERE MATCH(title,text) AGAINST ('database' WITH QUERY EXPANSION);
|
||
eval EXPLAIN $query;
|
||
FLUSH STATUS;
|
||
eval $query;
|
||
SHOW STATUS LIKE 'Handler_read%';
|
||
|
||
#
|
||
# Verify that the queries optimized for InnoDB works with BOOLEAN MODE
|
||
#
|
||
|
||
# Query will also avoid sorting when Boolean mode is used
|
||
FLUSH STATUS;
|
||
--sorted_result
|
||
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;
|
||
SHOW SESSION STATUS LIKE 'Sort%';
|
||
|
||
# Check ORDER BY/LIMIT query with no WHERE clause
|
||
FLUSH STATUS;
|
||
--sorted_result
|
||
SELECT MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) score,
|
||
title
|
||
FROM wp
|
||
ORDER BY score DESC;
|
||
SHOW SESSION STATUS LIKE 'Sort%';
|
||
|
||
# Check query where FTS result is "covering"
|
||
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');
|
||
SHOW SESSION STATUS LIKE 'Handler_read%';
|
||
|
||
# Check the combination of all three
|
||
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;
|
||
SHOW STATUS LIKE 'Handler_read%';
|
||
SHOW SESSION STATUS LIKE 'Sort%';
|
||
|
||
# Check the count optimization
|
||
let $query =
|
||
SELECT COUNT(*)
|
||
FROM wp
|
||
WHERE MATCH(title,text) AGAINST ('+MySQL -database' IN BOOLEAN MODE);
|
||
eval EXPLAIN $query;
|
||
FLUSH STATUS;
|
||
eval $query;
|
||
SHOW STATUS LIKE 'Handler_read%';
|
||
|
||
|
||
#
|
||
# Verify that the queries optimized for InnoDB works with
|
||
# BOOLEAN proximity search
|
||
#
|
||
|
||
# Query will also avoid sorting when Boolean mode is used
|
||
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;
|
||
SHOW SESSION STATUS LIKE 'Sort%';
|
||
|
||
# Check ORDER BY/LIMIT query with no WHERE clause
|
||
FLUSH STATUS;
|
||
SELECT title,
|
||
MATCH(title, text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE) AS score
|
||
FROM wp
|
||
ORDER BY score DESC LIMIT 1;
|
||
SHOW SESSION STATUS LIKE 'Sort%';
|
||
|
||
# Check query where FTS result is "covering"
|
||
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');
|
||
SHOW SESSION STATUS LIKE 'Handler_read%';
|
||
|
||
# Check the combination of all three
|
||
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;
|
||
SHOW STATUS LIKE 'Handler_read%';
|
||
SHOW SESSION STATUS LIKE 'Sort%';
|
||
|
||
# Check the count optimization
|
||
let $query =
|
||
SELECT COUNT(*)
|
||
FROM wp
|
||
WHERE MATCH(title,text) AGAINST ('"MySQL database"@5' IN BOOLEAN MODE);
|
||
eval EXPLAIN $query;
|
||
FLUSH STATUS;
|
||
eval $query;
|
||
SHOW STATUS LIKE 'Handler_read%';
|
||
|
||
#
|
||
# Check that nothing goes wrong when combining different modes
|
||
#
|
||
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;
|
||
|
||
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;
|
||
|
||
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;
|
||
|
||
|
||
#
|
||
# Verify that the queries optimized for InnoDB still works with MyISAM
|
||
#
|
||
ALTER TABLE wp ENGINE=myisam;
|
||
|
||
# Check avoid sorting query
|
||
FLUSH STATUS;
|
||
SELECT title, MATCH(title, text) AGAINST ('database') AS score
|
||
FROM wp
|
||
WHERE MATCH(title, text) AGAINST ('database')
|
||
ORDER BY score DESC;
|
||
SHOW SESSION STATUS LIKE 'Sort%';
|
||
|
||
# Check ORDER BY/LIMIT query with no WHERE clause
|
||
FLUSH STATUS;
|
||
SELECT title, MATCH(title, text) AGAINST ('database') AS score
|
||
FROM wp
|
||
ORDER BY score DESC LIMIT 2;
|
||
SHOW SESSION STATUS LIKE 'Sort%';
|
||
|
||
# Check query where FTS result is "covering"
|
||
FLUSH STATUS;
|
||
SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
|
||
FROM wp
|
||
WHERE MATCH(title, text) AGAINST ('database');
|
||
SHOW SESSION STATUS LIKE 'Handler_read%';
|
||
|
||
# Check the combination of all three
|
||
FLUSH STATUS;
|
||
SELECT FTS_DOC_ID docid, MATCH(title, text) AGAINST ('database') AS score
|
||
FROM wp
|
||
ORDER BY score DESC LIMIT 2;
|
||
SHOW STATUS LIKE 'Handler_read%';
|
||
SHOW SESSION STATUS LIKE 'Sort%';
|
||
|
||
# Check the count optimization
|
||
let $query =
|
||
SELECT COUNT(*)
|
||
FROM wp
|
||
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
|
||
eval EXPLAIN $query;
|
||
FLUSH STATUS;
|
||
eval $query;
|
||
SHOW STATUS LIKE 'Handler_read%';
|
||
|
||
|
||
DROP TABLE wp, t1;
|
||
|
||
--enable_ps2_protocol
|
||
--enable_cursor_protocol
|
||
|
||
# Tests for FT hints.
|
||
|
||
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);
|
||
|
||
-- disable_result_log
|
||
ANALYZE TABLE t1;
|
||
-- enable_result_log
|
||
|
||
--echo # No ranking
|
||
|
||
EXPLAIN
|
||
SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE);
|
||
SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE);
|
||
|
||
# Atm opt_sum_query does not support COUNT optimization if
|
||
# ORDER BY is present. TODO: fix it.
|
||
EXPLAIN
|
||
SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('data*' IN BOOLEAN MODE) ORDER BY title LIMIT 3;
|
||
SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('data*' IN BOOLEAN MODE) ORDER BY title LIMIT 3;
|
||
|
||
EXPLAIN
|
||
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);
|
||
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);
|
||
|
||
EXPLAIN
|
||
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION);
|
||
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION);
|
||
|
||
EXPLAIN
|
||
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE);
|
||
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE);
|
||
|
||
# check case with 'for' stopword
|
||
EXPLAIN SELECT FTS_DOC_ID FROM t1
|
||
WHERE MATCH(title, text) AGAINST ('+for' IN BOOLEAN MODE);
|
||
SELECT FTS_DOC_ID FROM t1
|
||
WHERE MATCH(title, text) AGAINST ('+for' IN BOOLEAN MODE);
|
||
|
||
|
||
--echo # 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;
|
||
SELECT FTS_DOC_ID, TITLE FROM t1
|
||
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
|
||
ORDER BY title;
|
||
|
||
EXPLAIN SELECT FTS_DOC_ID FROM t1
|
||
WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);
|
||
SELECT FTS_DOC_ID FROM t1
|
||
WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE);
|
||
|
||
EXPLAIN
|
||
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title;
|
||
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title;
|
||
|
||
EXPLAIN
|
||
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title;
|
||
SELECT FTS_DOC_ID, title FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title;
|
||
|
||
--echo # LIMIT optimization
|
||
|
||
EXPLAIN SELECT FTS_DOC_ID, TITLE FROM t1
|
||
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
|
||
LIMIT 3;
|
||
SELECT FTS_DOC_ID, TITLE FROM t1
|
||
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE)
|
||
LIMIT 3;
|
||
|
||
EXPLAIN SELECT FTS_DOC_ID FROM t1
|
||
WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
|
||
LIMIT 3;
|
||
SELECT FTS_DOC_ID FROM t1
|
||
WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
|
||
LIMIT 3;
|
||
|
||
EXPLAIN SELECT FTS_DOC_ID FROM t1
|
||
WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
|
||
ORDER BY title
|
||
LIMIT 3;
|
||
SELECT FTS_DOC_ID FROM t1
|
||
WHERE MATCH(title, text) AGAINST ('+fast +database' IN BOOLEAN MODE)
|
||
ORDER BY title
|
||
LIMIT 3;
|
||
|
||
|
||
EXPLAIN
|
||
SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title LIMIT 1;
|
||
SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+very +fast' WITH QUERY EXPANSION) ORDER BY title LIMIT 1;
|
||
|
||
EXPLAIN
|
||
SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title LIMIT 1;
|
||
SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('"very fast"@3' IN BOOLEAN MODE) ORDER BY title LIMIT 1;
|
||
|
||
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;
|
||
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;
|
||
|
||
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;
|
||
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;
|
||
|
||
EXPLAIN SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
|
||
FROM t1
|
||
ORDER BY rank DESC
|
||
LIMIT 2;
|
||
SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
|
||
FROM t1
|
||
ORDER BY rank DESC
|
||
LIMIT 2;
|
||
|
||
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;
|
||
SELECT FTS_DOC_ID, MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) as rank
|
||
FROM t1
|
||
ORDER BY rank DESC
|
||
LIMIT 3;
|
||
|
||
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;
|
||
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;
|
||
|
||
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;
|
||
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;
|
||
|
||
|
||
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;
|
||
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;
|
||
|
||
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;
|
||
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;
|
||
|
||
|
||
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;
|
||
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;
|
||
|
||
--echo # 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;
|
||
|
||
SELECT FTS_DOC_ID FROM t1
|
||
WHERE MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) > 0.1;
|
||
|
||
|
||
--echo # additional test for correct behaviour
|
||
|
||
EXPLAIN SELECT * FROM t1 ORDER BY MATCH (title, text) AGAINST ('fast database' IN NATURAL LANGUAGE MODE) DESC LIMIT 10;
|
||
|
||
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;
|
||
|
||
--echo # 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;
|
||
|
||
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;
|
||
|
||
--echo # 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;
|
||
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;
|
||
|
||
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;
|
||
|
||
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;
|
||
|
||
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;
|
||
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;
|
||
|
||
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;
|
||
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;
|
||
|
||
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;
|
||
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;
|
||
|
||
SET sql_mode = @save_mode;
|
||
|
||
--echo # 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;
|
||
EXPLAIN SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) as rank
|
||
FROM t1
|
||
ORDER BY rank DESC
|
||
LIMIT 2;
|
||
|
||
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;
|
||
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;
|
||
|
||
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);
|
||
|
||
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);
|
||
|
||
# Index only access by non-FTS index
|
||
|
||
EXPLAIN SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ("data*" IN BOOLEAN MODE) * 100;
|
||
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;
|
||
# Run query returning null record
|
||
SELECT * FROM t1 WHERE title IS NULL AND text IS NULL;
|
||
|
||
# More testing of index only access by non-FTS index
|
||
|
||
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);
|
||
|
||
-- disable_result_log
|
||
ANALYZE TABLE t2;
|
||
-- enable_result_log
|
||
|
||
# No index access
|
||
EXPLAIN SELECT DOC_ID FROM t2 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
|
||
SELECT DOC_ID FROM t2 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
|
||
# Index access
|
||
EXPLAIN SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
|
||
SELECT FTS_DOC_ID FROM t1 WHERE MATCH(title, text) AGAINST ('+database' IN BOOLEAN MODE) * 100;
|
||
|
||
DROP TABLE t1, t2;
|
||
|
||
--echo "Check hints with uft8 charset for 2 cases"
|
||
set names utf8;
|
||
EVAL 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);
|
||
|
||
--echo # No ranking
|
||
|
||
EXPLAIN
|
||
SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('вредит' IN NATURAL LANGUAGE MODE);
|
||
SELECT count(*) FROM t1 WHERE MATCH (title, text) AGAINST ('вредит' IN NATURAL LANGUAGE MODE);
|
||
|
||
EXPLAIN
|
||
SELECT * FROM t1 WHERE MATCH(title, text) AGAINST ("оно" WITH QUERY EXPANSION);
|
||
SELECT * FROM t1 WHERE MATCH(title, text) AGAINST ("оно" WITH QUERY EXPANSION);
|
||
--echo # No sorting by rank
|
||
EXPLAIN SELECT FTS_DOC_ID FROM t1
|
||
WHERE MATCH(title, text) AGAINST ('+(Мога τίποτα)' IN BOOLEAN MODE);
|
||
SELECT FTS_DOC_ID FROM t1
|
||
WHERE MATCH(title, text) AGAINST ('+(Мога τίποτα)' IN BOOLEAN MODE);
|
||
DROP TABLE t1;
|
||
|
||
|
||
--echo #
|
||
--echo # Bug #18924341 CRASH IN TEST_IF_SKIP_SORT_ORDER, GROUP BY MATCH AGAINST DESC
|
||
--echo #
|
||
|
||
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;
|
||
DROP TABLE t1;
|
||
|
||
|
||
--echo #
|
||
--echo # Bug#20261601 ASSERTION FAILED: !FIRST_QEP_TAB->TABLE()->NO_KEYREAD
|
||
--echo #
|
||
|
||
CREATE TABLE t1(a INT PRIMARY KEY);
|
||
INSERT INTO t1 VALUES(1),(2);
|
||
--error ER_FT_MATCHING_KEY_NOT_FOUND
|
||
SELECT (SELECT MATCH(`a`)AGAINST('1') FROM t1) FROM t1;
|
||
SELECT 1, a IN (SELECT a FROM t1) FROM t1;
|
||
DROP TABLE t1;
|
||
|
||
--echo #
|
||
--echo # Bug#20442572 ASSERTION `!FIRST_QEP_TAB->TABLE()->NO_KEYREAD' FAILED.
|
||
--echo # Bug#75688 Assertion `!first_qep_tab->table()->no_keyread' failed.
|
||
--echo #
|
||
|
||
CREATE TABLE t1(a INT,b POINT NOT NULL,KEY(a));
|
||
|
||
HANDLER t1 OPEN;
|
||
#--error ER_FT_MATCHING_KEY_NOT_FOUND
|
||
select * from t1 where MATCH a,b AGAINST('"Now sUPPort"' IN BOOLEAN MODE);
|
||
prepare stmt1 from "truncate t1";
|
||
SELECT a IN(SELECT a FROM t1)FROM t1;
|
||
|
||
deallocate prepare stmt1;
|
||
DROP TABLE t1;
|
||
|
||
--echo #
|
||
--echo # Bug #20685427 INVALID WRITE OF FREED MEMORY IN ITEM_FUNC_MATCH::CLEANUP
|
||
--echo #
|
||
|
||
CREATE TABLE t1(a TEXT CHARSET LATIN1, FULLTEXT KEY(a)) ENGINE=INNODB;
|
||
--error ER_FT_MATCHING_KEY_NOT_FOUND
|
||
SELECT MATCH(a) AGAINST ('') FROM (SELECT a FROM t1 LIMIT 1) q;
|
||
DROP TABLE t1;
|
||
|
||
--echo #
|
||
--echo # Bug#21140067 EXPLAIN .. MATCH AGAINST: ASSERTION FAILED: TO <= END
|
||
--echo #
|
||
|
||
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);
|
||
|
||
EXPLAIN FORMAT = JSON SELECT 1 FROM t1 WHERE 1.238585e+308 <= MATCH(f1) AGAINST ('1' IN BOOLEAN MODE);
|
||
|
||
DROP TABLE t1;
|
||
|
||
--echo #
|
||
--echo # Bug#21140088 MATCH AGAINST: ASSERTION FAILED: !TABLE || (!TABLE->READ_SET || BITMAP_IS_SET
|
||
--echo #
|
||
|
||
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');
|
||
--error ER_WRONG_ARGUMENTS
|
||
SELECT NOT EXISTS (SELECT MATCH(b) AGAINST ('1') FROM t1) FROM t2 GROUP BY "a";
|
||
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');
|
||
#--error ER_WRONG_ARGUMENTS
|
||
SELECT NOT EXISTS (SELECT MATCH(b) AGAINST ('1' in BOOLEAN MODE) FROM t1) FROM t2 GROUP BY "a";
|
||
DROP TABLE t1, t2;
|
||
|
||
SET sql_mode=default;
|
||
|
||
--echo #
|
||
--echo # Bug#21140039 ASSERTION FAILED: !FIRST_QEP_TAB->TABLE()->NO_KEYREAD MATCH AGAINST.....
|
||
--echo #
|
||
|
||
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));
|
||
DROP TABLE t1;
|
||
|
||
--echo #
|
||
--echo # Bug#21300774 ASSERT `!INIT_FTFUNCS(THD, SELECT_LEX)` IN JOIN::RESET AT SQL/SQL_SELECT.CC:874
|
||
--echo #
|
||
|
||
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');
|
||
|
||
--error ER_SUBQUERY_NO_1_ROW
|
||
UPDATE t1 SET f1 =
|
||
(SELECT t1.f1 FROM t2 WHERE NOT TRUE AND
|
||
MATCH (ft) AGAINST ((SELECT 'xyz' FROM t2)));
|
||
|
||
DROP TABLE t1, t2;
|
||
|
||
--echo #
|
||
--echo # Bug#22679209: FULL-TEXT QUERIES WITH ADDITIONAL SECONDARY INDEX
|
||
--echo # GIVES NULL OR ZERO ROWS
|
||
--echo #
|
||
|
||
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));
|
||
|
||
DROP TABLE t1;
|
||
|
||
--echo # End of test for Bug#22679209
|