From: Oystein Grovlen Date: March 30 2012 10:42am Subject: bzr push into mysql-trunk branch (oystein.grovlen:3698 to 3699) WL#6043 List-Archive: http://lists.mysql.com/commits/143376 Message-Id: <201203301042.q2UAgnSf001200@khepri24.no.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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).