List:Commits« Previous MessageNext Message »
From:Oystein Grovlen Date:April 20 2012 6:49am
Subject:bzr push into mysql-trunk branch (oystein.grovlen:3702) WL#6043
View as plain text  
 3702 Oystein Grovlen	2012-04-20 [merge]
      Final merge mysql-trunk => mysql-wl#6043.
      No issues.

    added:
      mysql-test/r/bug12427262.result
      mysql-test/r/myisam_row_rpl.result
      mysql-test/t/bug12427262.test
      mysql-test/t/myisam_row_rpl-master.opt
      mysql-test/t/myisam_row_rpl-slave.opt
      mysql-test/t/myisam_row_rpl.test
    modified:
      CMakeLists.txt
      README
      VERSION
      client/mysql.cc
      client/mysql_upgrade.c
      client/mysqladmin.cc
      client/mysqlbinlog.cc
      client/mysqlcheck.c
      client/mysqldump.c
      client/mysqlimport.c
      client/mysqlshow.c
      client/mysqlslap.c
      client/mysqltest.cc
      cmake/os/Windows.cmake
      config.h.cmake
      extra/innochecksum.cc
      extra/perror.c
      include/welcome_copyright_notice.h
      mysql-test/include/assert_command_output.inc
      mysql-test/lib/My/CoreDump.pm
      mysql-test/r/log_tables.result
      mysql-test/r/rewrite_general_log.result
      mysql-test/suite/binlog/r/binlog_grant.result
      mysql-test/suite/binlog/r/binlog_multi_engine.result
      mysql-test/suite/binlog/t/binlog_grant.test
      mysql-test/suite/innodb/r/innodb-index-online.result
      mysql-test/suite/innodb/t/innodb-alter-discard.test
      mysql-test/suite/innodb/t/innodb-index-online.test
      mysql-test/suite/innodb_fts/r/innodb_fts_misc.result
      mysql-test/suite/innodb_fts/t/innodb_fts_misc.test
      mysql-test/suite/rpl/r/rpl_corruption.result
      mysql-test/suite/rpl/r/rpl_gtid_mode.result
      mysql-test/suite/rpl/t/rpl_corruption.test
      mysql-test/suite/rpl/t/rpl_gtid_mode.test
      mysql-test/suite/rpl/t/rpl_parallel_change_master.test
      mysql-test/t/rewrite_general_log.test
      packaging/WiX/custom_ui.wxs
      sql/gen_lex_hash.cc
      sql/ha_ndbcluster_binlog.cc
      sql/item.cc
      sql/item.h
      sql/item_func.cc
      sql/item_func.h
      sql/log_event.cc
      sql/log_event.h
      sql/mysqld.cc
      sql/rpl_info_file.cc
      sql/rpl_mi.cc
      sql/set_var.cc
      sql/sql_executor.cc
      sql/sql_parse.cc
      sql/sql_select.cc
      sql/sql_show.cc
      sql/sql_show.h
      sql/sql_string.h
      storage/innobase/pars/lexyy.cc
      storage/innobase/pars/pars0lex.l
      storage/myisam/ha_myisam.cc
      storage/perfschema/gen_pfs_lex_token.cc
=== modified file 'include/ft_global.h'
--- a/include/ft_global.h	2011-09-07 10:08:09 +0000
+++ b/include/ft_global.h	2012-03-14 09:25:40 +0000
@@ -40,11 +40,32 @@ struct _ft_vft
   void      (*reinit_search)(FT_INFO *);
 };
 
+typedef struct st_ft_info_ext FT_INFO_EXT;
+struct _ft_vft_ext
+{
+  uint      (*get_version)();        // Extended API version
+  ulonglong (*get_flags)();
+  ulonglong (*get_docid)(FT_INFO_EXT *);
+  ulonglong (*count_matches)(FT_INFO_EXT *);
+};
+
+/* Flags for extended FT API */
+#define FTS_ORDERED_RESULT                (LL(1) << 1)
+#define FTS_DOCID_IN_RESULT               (LL(1) << 2)
+
+#define FTS_DOC_ID_COL_NAME "FTS_DOC_ID"
+
 #ifndef FT_CORE
 struct st_ft_info
 {
   struct _ft_vft *please; /* INTERCAL style :-) */
 };
+
+struct st_ft_info_ext
+{
+  struct _ft_vft     *please; /* INTERCAL style :-) */
+  struct _ft_vft_ext *could_you;
+};
 #endif
 
 extern const char *ft_stopword_file;

=== added file 'mysql-test/suite/innodb_fts/r/innodb_fts_opt.result'
--- a/mysql-test/suite/innodb_fts/r/innodb_fts_opt.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb_fts/r/innodb_fts_opt.result	2012-03-30 10:42:11 +0000
@@ -0,0 +1,825 @@
+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;
+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_range	0
+Sort_rows	0
+Sort_scan	0
+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_range	0
+Sort_rows	0
+Sort_scan	0
+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_range	0
+Sort_rows	0
+Sort_scan	0
+Sorting since it is not a single table 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 title, MATCH(title, text) AGAINST ('database') AS score 
+FROM wp 
+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
+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_range	0
+Sort_rows	0
+Sort_scan	0
+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 3;
+title	score
+MySQL vs. YourSQL	0.45528939366340637
+MySQL Tutorial	0.22764469683170319
+How To Use MySQL Well	0
+SHOW SESSION STATUS LIKE 'Handler_read%';
+Variable_name	Value
+Handler_read_first	1
+Handler_read_key	4
+Handler_read_last	0
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	3
+Handler_read_rnd_next	7
+SHOW SESSION STATUS LIKE 'Sort_rows%';
+Variable_name	Value
+Sort_rows	3
+Sorting since no LIMIT clause
+FLUSH STATUS;
+SELECT title, MATCH(title, text) AGAINST ('database') AS score 
+FROM wp 
+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
+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_rnd	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_rnd	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	2
+Handler_read_last	0
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	2
+Handler_read_rnd_next	0
+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	2
+Handler_read_last	0
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	2
+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_rnd	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	2
+Handler_read_last	0
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	2
+Handler_read_rnd_next	0
+FLUSH STATUS;
+Optimization does not apply for 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_rnd	2
+Handler_read_rnd_next	3
+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_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
+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	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+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_rnd	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	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+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	NULL	1	Using where
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	NULL
+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	NULL	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	NULL	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	NULL	1	Using where
+SELECT COUNT(dummy) 
+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 
+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_range	1
+Sort_rows	2
+Sort_scan	0
+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_range	1
+Sort_rows	2
+Sort_scan	0
+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_rnd	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	3
+Handler_read_prev	0
+Handler_read_rnd	2
+Handler_read_rnd_next	0
+SHOW SESSION STATUS LIKE 'Sort%';
+Variable_name	Value
+Sort_merge_passes	0
+Sort_range	1
+Sort_rows	2
+Sort_scan	0
+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	NULL	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_rnd	0
+Handler_read_rnd_next	0
+DROP TABLE wp, t1;

=== added file 'mysql-test/suite/innodb_fts/t/innodb_fts_opt.test'
--- a/mysql-test/suite/innodb_fts/t/innodb_fts_opt.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/innodb_fts/t/innodb_fts_opt.test	2012-03-30 10:42:11 +0000
@@ -0,0 +1,534 @@
+#  
+# 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;
+
+#
+#  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 Sorting since it is not a single table 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;
+
+SELECT title, MATCH(title, text) AGAINST ('database') 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 3;
+
+SHOW SESSION STATUS LIKE 'Handler_read%';
+SHOW SESSION STATUS LIKE 'Sort_rows%';
+
+--echo Sorting since no LIMIT clause
+FLUSH STATUS;
+
+SELECT title, MATCH(title, text) AGAINST ('database') AS score 
+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
+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%';
+
+#
+#  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;
+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;
+
+# 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;
+
+

=== modified file 'sql/handler.h'
--- a/sql/handler.h	2012-04-18 13:06:39 +0000
+++ b/sql/handler.h	2012-04-20 06:48:15 +0000
@@ -189,6 +189,11 @@ enum enum_alter_inplace_result {
 */
 #define HA_READ_BEFORE_WRITE_REMOVAL  (LL(1) << 38)
 
+/*
+  Engine supports extended fulltext API
+ */
+#define HA_CAN_FULLTEXT_EXT              (LL(1) << 39)
+
 /* bits in index_flags(index_number) for what you can do with index */
 #define HA_READ_NEXT            1       /* TODO really use this flag */
 #define HA_READ_PREV            2       /* supports ::index_prev */

=== modified file 'sql/item.cc'
--- a/sql/item.cc	2012-04-19 10:49:27 +0000
+++ b/sql/item.cc	2012-04-20 06:48:15 +0000
@@ -6787,6 +6787,25 @@ Item* Item::cache_const_expr_transformer
 }
 
 
+bool Item_field::item_field_by_name_analyzer(uchar **arg)
+{
+  const char *name= reinterpret_cast<char*>(*arg);
+  
+  if (strcmp(field_name, name) == 0)
+    return true;
+  else
+    return false;
+}
+
+
+Item* Item_field::item_field_by_name_transformer(uchar *arg)
+{
+  Item *item= reinterpret_cast<Item*>(arg);
+  item->item_name= item_name;
+  return item;
+}
+
+
 bool Item_field::send(Protocol *protocol, String *buffer)
 {
   return protocol->store(result_field);

=== modified file 'sql/item.h'
--- a/sql/item.h	2012-04-19 11:30:44 +0000
+++ b/sql/item.h	2012-04-20 06:48:15 +0000
@@ -1385,6 +1385,28 @@ public:
 
   virtual bool cache_const_expr_analyzer(uchar **arg);
   virtual Item* cache_const_expr_transformer(uchar *arg);
+
+  /**
+     Analyzer for finding Item_field by name
+     
+     @param arg  Field name to search for
+     
+     @return TRUE Go deeper in item tree.  (Found Item or not an Item_field)
+     @return FALSE Don't go deeper in item tree. (Item_field with other name)
+  */
+  virtual bool item_field_by_name_analyzer(uchar **arg) { return true; };
+
+  /**
+     Simple transformer that returns the argument if this is an Item_field.
+     The new item will inherit it's name to maintain aliases.
+
+     @param arg Item to replace Item_field
+
+     @return argument if this is an Item_field
+     @return this otherwise.
+  */
+  virtual Item* item_field_by_name_transformer(uchar *arg) { return this; };
+  
   /*
     Check if a partition function is allowed
     SYNOPSIS
@@ -2174,6 +2196,8 @@ public:
   Item *safe_charset_converter(const CHARSET_INFO *tocs);
   int fix_outer_field(THD *thd, Field **field, Item **reference);
   virtual Item *update_value_transformer(uchar *select_arg);
+  virtual bool item_field_by_name_analyzer(uchar **arg);
+  virtual Item* item_field_by_name_transformer(uchar *arg);
   virtual void print(String *str, enum_query_type query_type);
   bool is_outer_field() const
   {

=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc	2012-04-19 10:49:27 +0000
+++ b/sql/item_func.cc	2012-04-20 06:48:15 +0000
@@ -6323,9 +6323,11 @@ err:
 
 bool Item_func_match::eq(const Item *item, bool binary_cmp) const
 {
+  /* We ignore FT_SORTED flag when checking for equality since result is
+     equvialent regardless of sorting */
   if (item->type() != FUNC_ITEM ||
       ((Item_func*)item)->functype() != FT_FUNC ||
-      flags != ((Item_func_match*)item)->flags)
+      (flags | FT_SORTED) != (((Item_func_match*)item)->flags | FT_SORTED))
     return 0;
 
   Item_func_match *ifm=(Item_func_match*) item;

=== modified file 'sql/item_func.h'
--- a/sql/item_func.h	2012-04-18 18:38:45 +0000
+++ b/sql/item_func.h	2012-04-20 06:48:15 +0000
@@ -1855,8 +1855,91 @@ public:
 
   bool fix_index();
   void init_search(bool no_order);
+
+  /**
+     Get number of matching rows from FT handler.
+
+     @note Requires that FT handler supports the extended API
+
+     @return Number of matching rows in result 
+   */
+  ulonglong get_count()
+  {
+    DBUG_ASSERT(ft_handler);
+    DBUG_ASSERT(table->file->ha_table_flags() & HA_CAN_FULLTEXT_EXT);
+
+    return ((FT_INFO_EXT *)ft_handler)->could_you->
+      count_matches((FT_INFO_EXT *)ft_handler);
+  }
+
+  /**
+     Check whether FT result is ordered on rank
+
+     @return true if result is ordered
+     @return false otherwise
+   */
+  bool ordered_result()
+  {
+    if (flags & FT_SORTED)
+      return true;
+
+    if ((table->file->ha_table_flags() & HA_CAN_FULLTEXT_EXT) == 0)
+      return false;
+
+    DBUG_ASSERT(ft_handler);
+    return ((FT_INFO_EXT *)ft_handler)->could_you->get_flags() & 
+      FTS_ORDERED_RESULT;
+  }
+
+  /**
+     Check whether FT result contains the document ID
+
+     @return true if document ID is available
+     @return false otherwise
+   */
+  bool docid_in_result()
+  {
+    DBUG_ASSERT(ft_handler);
+
+    if ((table->file->ha_table_flags() & HA_CAN_FULLTEXT_EXT) == 0)
+      return false;
+
+    return ((FT_INFO_EXT *)ft_handler)->could_you->get_flags() & 
+      FTS_DOCID_IN_RESULT;
+  }
 };
 
+/**
+   Item_func class used to fetch document ID from FTS result.  This
+   class is used to replace Item_field objects in order to fetch
+   document ID from FTS result instead of table.
+ */
+class Item_func_docid : public Item_int_func
+{
+  FT_INFO_EXT *ft_handler;
+public:
+  Item_func_docid(FT_INFO_EXT *handler) : ft_handler(handler) 
+  { 
+    max_length= 21;
+    maybe_null= false; 
+    unsigned_flag= true;
+  } 
+
+  const char *func_name() const { return "docid"; }
+
+  void update_used_tables()
+  {
+    Item_int_func::update_used_tables();
+    used_tables_cache|= RAND_TABLE_BIT;
+    const_item_cache= false;
+  }
+
+  longlong val_int() 
+  { 
+    DBUG_ASSERT(ft_handler);
+    return ft_handler->could_you->get_docid(ft_handler);
+  }
+};
 
 class Item_func_bit_xor : public Item_func_bit
 {

=== modified file 'sql/opt_sum.cc'
--- a/sql/opt_sum.cc	2012-03-06 14:29:42 +0000
+++ b/sql/opt_sum.cc	2012-03-14 09:25:40 +0000
@@ -239,7 +239,7 @@ int opt_sum_query(THD *thd,
 {
   List_iterator_fast<Item> it(all_fields);
   int const_result= 1;
-  bool recalc_const_item= 0;
+  bool recalc_const_item= false;
   ulonglong count= 1;
   bool is_exact_count= TRUE, maybe_exact_count= TRUE;
   table_map removed_tables= 0, outer_tables= 0, used_tables= 0;
@@ -348,11 +348,37 @@ int opt_sum_query(THD *thd,
             }
             is_exact_count= 1;                  // count is now exact
           }
-          ((Item_sum_count*) item)->make_const((longlong) count);
-          recalc_const_item= 1;
+        }
+        /* For result count of full-text search: If
+           1. it is a single table query,
+           2. the WHERE condition is a single MATCH expresssion,
+           3. the table engine can provide the row count from FTS result, and
+           4. the expr in COUNT(expr) can not be NULL,
+           we do the full-text search now, and replace with the actual count.
+
+           Note: Item_func_match::init_search() will be called again
+                 later in the optimization phase by init_fts_funcs(),
+                 but search will still only be done once.
+        */
+        else if (tables->next_leaf == NULL &&                             // 1 
+                 conds && conds->type() == Item::FUNC_ITEM && 
+                 ((Item_func*)conds)->functype() == Item_func::FT_FUNC && // 2
+                 (tables->table->file->ha_table_flags() &
+                  HA_CAN_FULLTEXT_EXT) &&                                 // 3
+                 !((Item_sum_count*) item)->get_arg(0)->maybe_null)       // 4
+        {
+          Item_func_match* fts_item= static_cast<Item_func_match*>(conds); 
+          fts_item->init_search(true);
+          count= fts_item->get_count();
         }
         else
           const_result= 0;
+
+        if (const_result == 1) {
+          ((Item_sum_count*) item)->make_const((longlong) count);
+          recalc_const_item= true;
+        }
+          
         break;
       case Item_sum::MIN_FUNC:
       case Item_sum::MAX_FUNC:

=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc	2012-04-18 07:07:07 +0000
+++ b/sql/sql_optimizer.cc	2012-04-18 10:37:28 +0000
@@ -276,6 +276,8 @@ JOIN::optimize()
   }
 #endif
 
+  optimize_fts_limit_query();
+
   /* 
      Try to optimize count(*), min() and max() to const fields if
      there is implicit grouping (aggregate functions but no
@@ -732,7 +734,10 @@ JOIN::optimize()
 
   /* Perform FULLTEXT search before all regular searches */
   if (!(select_options & SELECT_DESCRIBE))
+  {
     init_ftfuncs(thd, select_lex, test(order));
+    optimize_fts_query();
+  }
 
   /* Create all structures needed for materialized subquery execution. */
   if (setup_subquery_materialization())
@@ -6875,6 +6880,31 @@ bool JOIN::cache_const_exprs()
 }
 
 
+void JOIN::replace_item_field(const char* field_name, Item* new_item)
+{
+  if (conds)
+  {
+    conds= conds->compile(&Item::item_field_by_name_analyzer, 
+                          (uchar **)&field_name,
+                          &Item::item_field_by_name_transformer,
+                          (uchar *)new_item);
+    conds->update_used_tables();
+  }
+
+  List_iterator<Item> it(fields_list);
+  Item *item;
+  while ((item= it++))
+  {
+    item= item->compile(&Item::item_field_by_name_analyzer,
+                        (uchar **)&field_name,
+                        &Item::item_field_by_name_transformer,
+                        (uchar *)new_item);
+    it.replace(item);
+    item->update_used_tables();
+  }
+}
+
+
 /**
   Extract a condition that can be checked after reading given table
   
@@ -8784,6 +8814,159 @@ static void optimize_keyuse(JOIN *join, 
 }
 
 
+void JOIN::optimize_fts_query()
+{
+  if (tables > 1)
+    return;    // We only optimize single table FTS queries
+
+  JOIN_TAB * const tab= &(join_tab[0]);
+  if (tab->type != JT_FT)
+    return;    // Access is not using FTS result
+
+  if ((tab->table->file->ha_table_flags() & HA_CAN_FULLTEXT_EXT) == 0)
+    return;    // Optimizations requires extended FTS support by table engine
+
+  Item_func_match* fts_result= static_cast<Item_func_match*>(tab->keyuse->val);
+
+  /* If we are ordering on the rank of the same result as is used for access,
+     and the table engine deliver result ordered by rank, we can drop ordering.
+   */
+  if (order != NULL 
+      && order->next == NULL &&             
+      order->direction == ORDER::ORDER_DESC && 
+      fts_result->eq(*(order->item), true))
+  {
+    Item_func_match* fts_item= 
+      static_cast<Item_func_match*>(*(order->item)); 
+
+    /* If we applied the LIMIT optimization @see optimize_fts_limit_query,
+       check that the number of matching rows is sufficient.
+       Otherwise, revert this optimization and use table scan instead.
+    */
+    if (min_ft_matches != HA_POS_ERROR && 
+        min_ft_matches > fts_item->get_count())
+    {
+      // revert to table scan, do things make_join_readinfo would have done
+      tab->type= JT_ALL;
+      tab->read_first_record= join_init_read_record;
+      tab->use_quick= QS_NONE;
+      tab->ref.key= -1;
+
+      // Reset join condition
+      tab->select->cond= NULL;
+      conds= NULL;
+
+      thd->set_status_no_index_used();
+      // make_join_readinfo only calls inc_status_select_scan()
+      // when this is not SELECT_DESCRIBE
+      DBUG_ASSERT((select_options & SELECT_DESCRIBE) == 0);
+      thd->inc_status_select_scan();
+
+      return;
+    }
+    else if (fts_item->ordered_result())
+      order= NULL;
+  }
+  
+  /* Check whether the FTS result is covering.  If only document id
+     and rank is needed, there is no need to access table rows.
+  */
+  List_iterator<Item> it(all_fields);
+  Item *item;
+  // This optimization does not work with filesort nor GROUP BY
+  bool covering= (!order && !group);
+  bool docid_found= false;
+  while (covering && (item= it++))
+  {
+    switch (item->type()) {
+    case Item::FIELD_ITEM:
+    {
+      Item_field *item_field= static_cast<Item_field*>(item);
+      if (strcmp(item_field->field_name, FTS_DOC_ID_COL_NAME) == 0)
+      {
+        docid_found= true;
+        covering= fts_result->docid_in_result();
+      }
+      else
+        covering= false;
+      break;
+    }
+    case Item::FUNC_ITEM:
+      if (static_cast<Item_func*>(item)->functype() == Item_func::FT_FUNC)
+      {
+        Item_func_match* fts_item= static_cast<Item_func_match*>(item); 
+        if (fts_item->eq(fts_result, true))
+          break;
+      }
+      // Fall-through when not an equivalent MATCH expression
+    default:
+      covering= false;
+    }
+  }
+
+  if (covering) 
+  {
+    if (docid_found)
+    {
+      replace_item_field(FTS_DOC_ID_COL_NAME, 
+                         new Item_func_docid(reinterpret_cast<FT_INFO_EXT*>
+                                             (fts_result->ft_handler)));
+    }
+    
+    // Tell storage engine that row access is not necessary
+    fts_result->table->set_keyread(true);
+    fts_result->table->covering_keys.set_bit(fts_result->key);
+  }
+}
+
+
+  /**
+     Optimize FTS queries with ORDER BY/LIMIT, but no WHERE clause.
+
+     If MATCH expression is not in WHERE clause, but in ORDER BY,
+     JT_FT access will not apply. However, if we are ordering on rank and
+     there is a limit, normally, only the top ranking rows are needed
+     returned, and one would benefit from the optimizations associated
+     with JT_FT acess (@see optimize_fts_query).  To get JT_FT access we
+     will add the MATCH expression to the WHERE clause.
+
+     @note This optimization will only be applied to single table
+           queries with no existing WHERE clause.
+     @note This transformation is not correct if number of matches 
+           is less than the number of rows requested by limit.
+           If this turns out to be the case, the transformation will
+           be reverted @see optimize_fts_query()
+   */
+void 
+JOIN::optimize_fts_limit_query()
+{
+  /* 
+     Only do this optimization if
+     1. It is a single table query
+     2. There is no WHERE condition
+     3. There is a single ORDER BY element
+     4. Ordering is descending
+     5. There is a LIMIT clause
+     6. Ordering is on a MATCH expression
+   */
+  if (tables == 1 &&                                // 1
+      conds == NULL &&                              // 2
+      order && order->next == NULL &&     // 3
+      order->direction == ORDER::ORDER_DESC && // 4
+      m_select_limit != HA_POS_ERROR)               // 5
+  {
+    DBUG_ASSERT(order->item);
+    Item* item= *order->item;
+    DBUG_ASSERT(item);
+
+    if (item->type() == Item::FUNC_ITEM &&
+        static_cast<Item_func*>(item)->functype() == Item_func::FT_FUNC)  // 6
+    {
+      conds= item;
+      min_ft_matches= m_select_limit;
+    }
+  }
+}
 
 /**
   @} (end of group Query_Optimizer)

=== modified file 'sql/sql_optimizer.h'
--- a/sql/sql_optimizer.h	2012-04-18 07:07:07 +0000
+++ b/sql/sql_optimizer.h	2012-04-18 09:36:30 +0000
@@ -105,6 +105,13 @@ public:
       - on each fetch iteration we add num_rows to fetch to fetch_limit
   */
   ha_rows  fetch_limit;
+
+  /**
+     Minimum number of matches that is needed to use JT_FT access.
+     @see optimize_fts_limit_query
+  */
+  ha_rows  min_ft_matches;
+
   /* Finally picked QEP. This is result of join optimization */
   POSITION *best_positions;
 
@@ -295,6 +302,8 @@ public:
     operator       ORDER *()       { return order; }
     operator const ORDER *() const { return order; }
 
+    ORDER* operator->() const { return order; }
+ 
     void clean() { order= NULL; src= ESC_none; flags= ESP_none; }
 
     void set_flag(Explain_sort_property flag)
@@ -418,6 +427,7 @@ public:
     send_records= 0;
     found_records= 0;
     fetch_limit= HA_POS_ERROR;
+    min_ft_matches= HA_POS_ERROR;
     examined_rows= 0;
     exec_tmp_table1= 0;
     exec_tmp_table2= 0;
@@ -621,6 +631,39 @@ private:
   */
   void optimize_distinct();
 
+  /** 
+      Optimize FTS queries where JT_FT access has been selected.
+
+      The following optimization is may be applied:
+      1. Skip filesort if FTS result is ordered
+      2. Skip accessing table rows if FTS result contains necessary information
+      Also verifize that LIMIT optimization was sound.
+
+      @note Optimizations are restricted to single table queries, and the table
+            engine needs to support the extended FTS API.
+   */
+  void optimize_fts_query();
+
+
+  /**
+     Optimize FTS queries with ORDER BY/LIMIT, but no WHERE clause.
+   */
+  void optimize_fts_limit_query();
+
+  /**
+     Replace all Item_field objects with the given field name with the
+     given item in all parts of the query.
+
+     @todo So far this function only handles SELECT list and WHERE clause,
+           For more general use, ON clause, ORDER BY list, GROUP BY list and
+	   HAVING clause also needs to be handled.
+
+     @param field_name Name of the field to search for
+     @param new_item Replacement item
+  */
+  void replace_item_field(const char* field_name, Item* new_item);
+
+
   /**
     TRUE if the query contains an aggregate function but has no GROUP
     BY clause. 

=== modified file 'storage/innobase/handler/ha_innodb.cc'
--- a/storage/innobase/handler/ha_innodb.cc	2012-04-16 09:26:56 +0000
+++ b/storage/innobase/handler/ha_innodb.cc	2012-04-20 06:48:15 +0000
@@ -243,6 +243,11 @@ const struct _ft_vft ft_vft_result = {NU
 				      innobase_fts_retrieve_ranking,
 				      NULL};
 
+const struct _ft_vft_ext ft_vft_ext_result = {innobase_fts_get_version,
+					      innobase_fts_flags,
+					      innobase_fts_retrieve_docid,
+					      innobase_fts_count_matches};
+
 #ifdef HAVE_PSI_INTERFACE
 /* Keys to register pthread mutexes/cond in the current file with
 performance schema */
@@ -2082,7 +2087,8 @@ ha_innobase::ha_innobase(
 		  HA_PRIMARY_KEY_IN_READ_INDEX |
 		  HA_BINLOG_ROW_CAPABLE |
 		  HA_CAN_GEOMETRY | HA_PARTIAL_COLUMN_READ |
-		  HA_TABLE_SCAN_ON_INDEX | HA_CAN_FULLTEXT),
+		  HA_TABLE_SCAN_ON_INDEX | HA_CAN_FULLTEXT |
+		  HA_CAN_FULLTEXT_EXT),
 	start_of_scan(0),
 	num_write_row(0)
 {}
@@ -7726,6 +7732,7 @@ ha_innobase::ft_init_ext(
 						   MYF(0));
 
 		fts_hdl->please = (struct _ft_vft*)(&ft_vft_result);
+		fts_hdl->could_you = (struct _ft_vft_ext*)(&ft_vft_ext_result);
 		fts_hdl->ft_prebuilt = prebuilt;
 		fts_hdl->ft_result = result;
 	}
@@ -7778,6 +7785,13 @@ next_record:
 		dict_index_t*	index;
 		dtuple_t*	tuple = prebuilt->search_tuple;
 
+		/* If we only need information from result we can return
+		   without fetching the table row */
+		if (ft_prebuilt->read_just_key) {
+			table->status= 0;
+			return (0);
+		}
+
 		index = dict_table_get_index_on_name(
 			prebuilt->table, FTS_DOC_ID_INDEX_NAME);
 
@@ -14033,6 +14047,12 @@ innobase_fts_retrieve_ranking(
 
 	ft_prebuilt = ((NEW_FT_INFO*) fts_hdl)->ft_prebuilt;
 
+	if (ft_prebuilt->read_just_key) {
+		fts_ranking_t*  ranking = 
+			rbt_value(fts_ranking_t, result->current);
+		return (ranking->rank);
+	}
+
 	/* Retrieve the ranking value for doc_id with value of
 	prebuilt->fts_doc_id */
 	return(fts_retrieve_ranking(result, ft_prebuilt->fts_doc_id));
@@ -14087,6 +14107,69 @@ innobase_fts_find_ranking(
 	return fts_retrieve_ranking(result, ft_prebuilt->fts_doc_id);
 }
 
+/***********************************************************************
+@return version of the extended FTS API */
+uint
+innobase_fts_get_version()
+{
+	/* Currently this doesn't make much sense as returning
+	HA_CAN_FULLTEXT_EXT automatically mean this version is supported.
+	This supposed to ease future extensions.  */
+	return 2;
+}
+
+/***********************************************************************
+@return Which part of the extended FTS API is supported */
+ulonglong
+innobase_fts_flags()
+{
+	return (FTS_ORDERED_RESULT | FTS_DOCID_IN_RESULT);
+}
+
+
+/***********************************************************************
+Find and Retrieve the FTS doc_id for the current result row
+@return the document ID */
+ulonglong
+innobase_fts_retrieve_docid(
+/*============================*/
+		FT_INFO_EXT * fts_hdl)	/*!< in: FTS handler */
+{
+	row_prebuilt_t* ft_prebuilt;
+	fts_result_t*	result;
+
+	ft_prebuilt = ((NEW_FT_INFO *)fts_hdl)->ft_prebuilt;
+	result = ((NEW_FT_INFO *)fts_hdl)->ft_result;
+
+	if (ft_prebuilt->read_just_key) {
+		fts_ranking_t* ranking = 
+			rbt_value(fts_ranking_t, result->current);
+		return (ranking->doc_id);
+	}
+
+	return(ft_prebuilt->fts_doc_id);
+}
+
+/***********************************************************************
+Find and retrieve the size of the current result
+@return number of matching rows */
+ulonglong
+innobase_fts_count_matches(
+/*============================*/
+		FT_INFO_EXT * fts_hdl)	/*!< in: FTS handler */
+{
+	row_prebuilt_t* ft_prebuilt;
+
+	ft_prebuilt = ((NEW_FT_INFO *)fts_hdl)->ft_prebuilt;
+
+	if (ft_prebuilt->result->rankings_by_id != NULL) {
+		return rbt_size(ft_prebuilt->result->rankings_by_id);
+	} else {
+		return(0);
+	}
+}
+
+
 /* These variables are never read by InnoDB or changed. They are a kind of
 dummies that are needed by the MySQL infrastructure to call
 buffer_pool_dump_now(), buffer_pool_load_now() and buffer_pool_load_abort()

=== modified file 'storage/innobase/handler/ha_innodb.h'
--- a/storage/innobase/handler/ha_innodb.h	2012-04-16 09:26:56 +0000
+++ b/storage/innobase/handler/ha_innodb.h	2012-04-20 06:48:15 +0000
@@ -444,6 +444,7 @@ extern const struct _ft_vft ft_vft_resul
 typedef struct new_ft_info
 {
 	struct _ft_vft		*please;
+	struct _ft_vft_ext	*could_you;
 	row_prebuilt_t*		ft_prebuilt;
 	fts_result_t*		ft_result;
 } NEW_FT_INFO;
@@ -566,6 +567,32 @@ innobase_fts_check_doc_id_index_in_def(
 	const KEY*	key_info)	/*!< in: Key definitions */
 	__attribute__((nonnull, warn_unused_result));
 
+/***********************************************************************
+@return version of the extended FTS API */
+uint
+innobase_fts_get_version();
+
+/***********************************************************************
+@return Which part of the extended FTS API is supported */
+ulonglong
+innobase_fts_flags();
+
+/***********************************************************************
+Find and Retrieve the FTS doc_id for the current result row
+@return the document ID */
+ulonglong
+innobase_fts_retrieve_docid(
+/*============================*/
+	FT_INFO_EXT*	fts_hdl);	/*!< in: FTS handler */
+
+/***********************************************************************
+Find and retrieve the size of the current result
+@return number of matching rows */
+ulonglong
+innobase_fts_count_matches(
+/*============================*/
+	FT_INFO_EXT*	fts_hdl);	/*!< in: FTS handler */
+
 /** "GEN_CLUST_INDEX" is the name reserved for InnoDB default
 system clustered index when there is no primary key. */
 extern const char innobase_index_reserve_name[];

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (oystein.grovlen:3702) WL#6043Oystein Grovlen20 Apr