fulltext2.test
上传用户:romrleung
上传日期:2022-05-23
资源大小:18897k
文件大小:6k
- #
- # test of new fulltext search features
- #
- #
- # two-level tree
- #
- --disable_warnings
- DROP TABLE IF EXISTS t1;
- --enable_warnings
- CREATE TABLE t1 (
- i int(10) unsigned not null auto_increment primary key,
- a varchar(255) not null,
- FULLTEXT KEY (a)
- ) ENGINE=MyISAM;
- # two-level entry, second-level tree with depth 2
- --disable_query_log
- let $1=260;
- while ($1)
- {
- eval insert t1 (a) values ('aaaxxx');
- dec $1;
- }
- # two-level entry, second-level tree has only one page
- let $1=255;
- while ($1)
- {
- eval insert t1 (a) values ('aaazzz');
- dec $1;
- }
- # one-level entry (entries)
- let $1=250;
- while ($1)
- {
- eval insert t1 (a) values ('aaayyy');
- dec $1;
- }
- --enable_query_log
- # converting to two-level
- repair table t1 quick;
- check table t1;
- optimize table t1; # BUG#5327 - mi_sort_index() of 2-level tree
- check table t1;
- select count(*) from t1 where match a against ('aaaxxx');
- select count(*) from t1 where match a against ('aaayyy');
- select count(*) from t1 where match a against ('aaazzz');
- select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
- select count(*) from t1 where match a against ('aaayyy' in boolean mode);
- select count(*) from t1 where match a against ('aaazzz' in boolean mode);
- select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz');
- select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz' in boolean mode);
- select count(*) from t1 where match a against ('aaax*' in boolean mode);
- select count(*) from t1 where match a against ('aaay*' in boolean mode);
- select count(*) from t1 where match a against ('aaa*' in boolean mode);
- # mi_write:
- insert t1 (a) values ('aaaxxx'),('aaayyy');
- # call to enlarge_root() below
- insert t1 (a) values ('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz');
- select count(*) from t1 where match a against ('aaaxxx');
- select count(*) from t1 where match a against ('aaayyy');
- select count(*) from t1 where match a against ('aaazzz');
- # mi_delete
- insert t1 (a) values ('aaaxxx 000000');
- select count(*) from t1 where match a against ('000000');
- delete from t1 where match a against ('000000');
- select count(*) from t1 where match a against ('000000');
- select count(*) from t1 where match a against ('aaaxxx');
- delete from t1 where match a against ('aaazzz');
- select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
- select count(*) from t1 where match a against ('aaayyy' in boolean mode);
- select count(*) from t1 where match a against ('aaazzz' in boolean mode);
- # double-check without index
- select count(*) from t1 where a = 'aaaxxx';
- select count(*) from t1 where a = 'aaayyy';
- select count(*) from t1 where a = 'aaazzz';
- # update
- insert t1 (a) values ('aaaxxx 000000');
- select count(*) from t1 where match a against ('000000');
- update t1 set a='aaazzz' where match a against ('000000');
- select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
- select count(*) from t1 where match a against ('aaazzz' in boolean mode);
- update t1 set a='aaazzz' where a = 'aaaxxx';
- update t1 set a='aaaxxx' where a = 'aaayyy';
- select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
- select count(*) from t1 where match a against ('aaayyy' in boolean mode);
- select count(*) from t1 where match a against ('aaazzz' in boolean mode);
- drop table t1;
- CREATE TABLE t1 (
- i int(10) unsigned not null auto_increment primary key,
- a varchar(255) not null,
- FULLTEXT KEY (a)
- ) ENGINE=MyISAM;
- #
- # now same as about but w/o repair table
- # 2-level tree created by mi_write
- #
- # two-level entry, second-level tree with depth 2
- --disable_query_log
- let $1=260;
- while ($1)
- {
- eval insert t1 (a) values ('aaaxxx');
- dec $1;
- }
- let $1=255;
- while ($1)
- {
- eval insert t1 (a) values ('aaazzz');
- dec $1;
- }
- let $1=250;
- while ($1)
- {
- eval insert t1 (a) values ('aaayyy');
- dec $1;
- }
- --enable_query_log
- select count(*) from t1 where match a against ('aaaxxx');
- select count(*) from t1 where match a against ('aaayyy');
- select count(*) from t1 where match a against ('aaazzz');
- select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
- select count(*) from t1 where match a against ('aaayyy' in boolean mode);
- select count(*) from t1 where match a against ('aaazzz' in boolean mode);
- select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz');
- select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz' in boolean mode);
- select count(*) from t1 where match a against ('aaax*' in boolean mode);
- select count(*) from t1 where match a against ('aaay*' in boolean mode);
- select count(*) from t1 where match a against ('aaa*' in boolean mode);
- # mi_write:
- insert t1 (a) values ('aaaxxx'),('aaayyy');
- insert t1 (a) values ('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz');
- select count(*) from t1 where match a against ('aaaxxx');
- select count(*) from t1 where match a against ('aaayyy');
- select count(*) from t1 where match a against ('aaazzz');
- # mi_delete
- insert t1 (a) values ('aaaxxx 000000');
- select count(*) from t1 where match a against ('000000');
- delete from t1 where match a against ('000000');
- select count(*) from t1 where match a against ('000000');
- select count(*) from t1 where match a against ('aaaxxx');
- delete from t1 where match a against ('aaazzz');
- select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
- select count(*) from t1 where match a against ('aaayyy' in boolean mode);
- select count(*) from t1 where match a against ('aaazzz' in boolean mode);
- # double-check without index
- select count(*) from t1 where a = 'aaaxxx';
- select count(*) from t1 where a = 'aaayyy';
- select count(*) from t1 where a = 'aaazzz';
- # update
- insert t1 (a) values ('aaaxxx 000000');
- select count(*) from t1 where match a against ('000000');
- update t1 set a='aaazzz' where match a against ('000000');
- select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
- select count(*) from t1 where match a against ('aaazzz' in boolean mode);
- update t1 set a='aaazzz' where a = 'aaaxxx';
- update t1 set a='aaaxxx' where a = 'aaayyy';
- select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
- select count(*) from t1 where match a against ('aaayyy' in boolean mode);
- select count(*) from t1 where match a against ('aaazzz' in boolean mode);
- drop table t1;
- # End of 4.1 tests