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#6043 | Oystein Grovlen | 20 Apr |
