List:Commits« Previous MessageNext Message »
From:Oystein Grovlen Date:March 30 2012 10:42am
Subject:bzr push into mysql-trunk branch (oystein.grovlen:3698 to 3699) WL#6043
View as plain text  
 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#6043Oystein Grovlen31 Mar