3699 Oystein Grovlen 2012-03-30
WL#6043 - Optimizations for InnoDB's FTS
Additonal tests with queries with other modes than default
NATURAL LANGUAGE MODE.
@ mysql-test/suite/innodb_fts/r/innodb_fts_opt.result
More test cases
@ mysql-test/suite/innodb_fts/t/innodb_fts_opt.test
More test cases
modified:
mysql-test/suite/innodb_fts/r/innodb_fts_opt.result
mysql-test/suite/innodb_fts/t/innodb_fts_opt.test
3698 Oystein Grovlen 2012-03-29
WL#6043 - Optimizations for InnoDB's FTS
Fix handler flag "collision" after merge.
@ sql/handler.h
Fix handler flag "collision" after merge.
modified:
sql/handler.h
=== modified file 'mysql-test/suite/innodb_fts/r/innodb_fts_opt.result'
--- a/mysql-test/suite/innodb_fts/r/innodb_fts_opt.result 2012-03-13 08:33:54 +0000
+++ b/mysql-test/suite/innodb_fts/r/innodb_fts_opt.result 2012-03-30 10:42:11 +0000
@@ -438,6 +438,304 @@ FROM wp
WHERE MATCH(title,text) AGAINST ('database' IN NATURAL LANGUAGE MODE);
COUNT(dummy)
0
+FLUSH STATUS;
+SELECT title,
+MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score
+FROM wp
+WHERE MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION)
+ORDER BY score DESC;
+title score
+MySQL vs. YourSQL 2.2718474864959717
+MySQL Tutorial 1.6663280725479126
+Optimizing MySQL 0.22764469683170319
+MySQL Security 0.000000003771856604828372
+How To Use MySQL Well 0.000000001885928302414186
+1001 MySQL Tricks 0.000000001885928302414186
+SHOW SESSION STATUS LIKE 'Sort%';
+Variable_name Value
+Sort_merge_passes 0
+Sort_range 0
+Sort_rows 0
+Sort_scan 0
+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_range 0
+Sort_rows 0
+Sort_scan 0
+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_rnd 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_rnd 0
+Handler_read_rnd_next 0
+SHOW SESSION STATUS LIKE 'Sort%';
+Variable_name Value
+Sort_merge_passes 0
+Sort_range 0
+Sort_rows 0
+Sort_scan 0
+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 NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+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_rnd 0
+Handler_read_rnd_next 0
+FLUSH STATUS;
+SELECT title,
+MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
+FROM wp
+WHERE MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE)
+ORDER BY score DESC;
+title score
+MySQL Security 0.000000003771856604828372
+How To Use MySQL Well 0.000000001885928302414186
+Optimizing MySQL 0.000000001885928302414186
+1001 MySQL Tricks 0.000000001885928302414186
+SHOW SESSION STATUS LIKE 'Sort%';
+Variable_name Value
+Sort_merge_passes 0
+Sort_range 0
+Sort_rows 0
+Sort_scan 0
+FLUSH STATUS;
+SELECT title,
+MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
+FROM wp
+ORDER BY score DESC LIMIT 2;
+title score
+MySQL Security 0.000000003771856604828372
+How To Use MySQL Well 0.000000001885928302414186
+SHOW SESSION STATUS LIKE 'Sort%';
+Variable_name Value
+Sort_merge_passes 0
+Sort_range 0
+Sort_rows 0
+Sort_scan 0
+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
+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_rnd 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 2;
+docid score
+6 0.000000003771856604828372
+2 0.000000001885928302414186
+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_rnd 0
+Handler_read_rnd_next 0
+SHOW SESSION STATUS LIKE 'Sort%';
+Variable_name Value
+Sort_merge_passes 0
+Sort_range 0
+Sort_rows 0
+Sort_scan 0
+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 NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+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_rnd 0
+Handler_read_rnd_next 0
+FLUSH STATUS;
+SELECT title,
+MATCH(title, text) AGAINST ('"MySQL database"@10' IN BOOLEAN MODE) AS score
+FROM wp
+WHERE MATCH(title, text) AGAINST ('"MySQL database"@10' 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_range 0
+Sort_rows 0
+Sort_scan 0
+FLUSH STATUS;
+SELECT title,
+MATCH(title, text) AGAINST ('"MySQL database"@10' 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_range 0
+Sort_rows 0
+Sort_scan 0
+FLUSH STATUS;
+SELECT FTS_DOC_ID docid,
+MATCH(title, text) AGAINST ('"MySQL database"@10' IN BOOLEAN MODE) AS score
+FROM wp
+WHERE MATCH(title, text) AGAINST ('"MySQL database"@10');
+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_rnd 0
+Handler_read_rnd_next 0
+FLUSH STATUS;
+SELECT FTS_DOC_ID docid,
+MATCH(title, text) AGAINST ('"MySQL database"@10' 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_rnd 0
+Handler_read_rnd_next 0
+SHOW SESSION STATUS LIKE 'Sort%';
+Variable_name Value
+Sort_merge_passes 0
+Sort_range 0
+Sort_rows 0
+Sort_scan 0
+EXPLAIN SELECT COUNT(*)
+FROM wp
+WHERE MATCH(title,text) AGAINST ('"MySQL database"@10' IN BOOLEAN MODE);
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+FLUSH STATUS;
+SELECT COUNT(*)
+FROM wp
+WHERE MATCH(title,text) AGAINST ('"MySQL database"@10' 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_rnd 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 score
+MySQL vs. YourSQL 0.45528939366340637
+MySQL Tutorial 0.22764469683170319
+How To Use MySQL Well 0
+Optimizing MySQL 0
+1001 MySQL Tricks 0
+MySQL Security 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 score
+MySQL Security 0.000000003771856604828372
+How To Use MySQL Well 0.000000001885928302414186
+Optimizing MySQL 0.000000001885928302414186
+1001 MySQL Tricks 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"@10' IN BOOLEAN MODE)
+ORDER BY score DESC;
+title score
+MySQL Tutorial 0
ALTER TABLE wp ENGINE=myisam;
FLUSH STATUS;
SELECT title, MATCH(title, text) AGAINST ('database') AS score
@@ -515,7 +813,6 @@ 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
=== modified file 'mysql-test/suite/innodb_fts/t/innodb_fts_opt.test'
--- a/mysql-test/suite/innodb_fts/t/innodb_fts_opt.test 2012-02-28 15:06:31 +0000
+++ b/mysql-test/suite/innodb_fts/t/innodb_fts_opt.test 2012-03-30 10:42:11 +0000
@@ -316,6 +316,174 @@ 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;
+SELECT title,
+ MATCH(title, text) AGAINST ('database' WITH QUERY EXPANSION) AS score
+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;
+SELECT title,
+ MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
+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;
+SELECT title,
+ MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) 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 ('+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 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 ('+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"@10' IN BOOLEAN MODE) AS score
+FROM wp
+WHERE MATCH(title, text) AGAINST ('"MySQL database"@10' 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"@10' 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"@10' IN BOOLEAN MODE) AS score
+FROM wp
+WHERE MATCH(title, text) AGAINST ('"MySQL database"@10');
+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"@10' 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"@10' 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;
+
+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;
+
+SELECT title,
+ MATCH(title, text) AGAINST ('+MySQL -database' IN BOOLEAN MODE) AS score
+FROM wp
+WHERE MATCH(title, text) AGAINST ('"MySQL database"@10' IN BOOLEAN MODE)
+ORDER BY score DESC;
+
+
+#
# Verify that the queries optimized for InnoDB still works with MyISAM
#
ALTER TABLE wp ENGINE=myisam;
@@ -358,9 +526,9 @@ WHERE MATCH(title,text) AGAINST ('databa
eval EXPLAIN $query;
FLUSH STATUS;
eval $query;
---echo Verify that there was no table access
SHOW STATUS LIKE 'Handler_read%';
+
DROP TABLE wp, t1;
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (oystein.grovlen:3698 to 3699) WL#6043 | Oystein Grovlen | 31 Mar |
