From: Oystein Grovlen Date: April 20 2012 6:49am Subject: bzr push into mysql-trunk branch (oystein.grovlen:3702) WL#6043 List-Archive: http://lists.mysql.com/commits/143491 Message-Id: <201204200649.q3K6nY6J009040@khepri24.no.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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(*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(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 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(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 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(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(*(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 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); + 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)->functype() == Item_func::FT_FUNC) + { + Item_func_match* fts_item= static_cast(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 + (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)->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).