Debian Codegraph @

mysql-test/suite/innodb_fts/t/optimize_big.test

raw
1 # Test innodb_optimize_fulltext_only optimization on large text
2 # Because of the way this optimization is perfomed, some logic is only
3 # hit if the size of all individual tokens after compression
4 # exceeds a certain threshold
5
6 SET GLOBAL innodb_optimize_fulltext_only=ON;
7
8 CREATE TABLE book (
9 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
10 heading VARCHAR(20),
11 content TEXT,
12 FULLTEXT idx (content)
13 ) ENGINE= InnoDB;
14
15 CREATE PROCEDURE find (word CHAR(20))
16 SELECT heading FROM book WHERE MATCH(content)
17 AGAINST (word IN NATURAL LANGUAGE MODE)
18 ORDER BY id;
19
20 --replace_result $MYSQLTEST_VARDIR <MYSQLTEST_VARDIR>
21 eval
22 LOAD DATA INFILE '$MYSQLTEST_VARDIR/std_data/lob/frankenstein.txt' INTO TABLE book
23 FIELDS TERMINATED BY '#' LINES TERMINATED BY '@' (heading, content);
24
25 CALL find('they');
26 CALL find('wretched');
27 CALL find('mariner');
28
29 SET GLOBAL innodb_ft_aux_table="test/book";
30
31 OPTIMIZE TABLE book;
32
33 CALL find('they');
34 CALL find('wretched');
35 CALL find('mariner');
36
37 SET GLOBAL innodb_ft_aux_table="test/book";
38
39 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
40 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
41
42 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
43
44 DELETE FROM book;
45
46 CALL find('they');
47
48 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
49 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
50 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
51
52 OPTIMIZE TABLE book;
53
54 CALL find('they');
55
56 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
57 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
58 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
59
60 SET GLOBAL innodb_ft_num_word_optimize=10000;
61
62 OPTIMIZE TABLE book;
63 # Need to run OPTIMIZE TABLE twice because of Bug#35502793
64 OPTIMIZE TABLE book;
65
66 CALL find('they');
67
68 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
69 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
70 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
71
72 DROP TABLE book;
73 DROP PROCEDURE find;
74 SET GLOBAL innodb_optimize_fulltext_only=OFF;
75 SET GLOBAL innodb_ft_aux_table=default;
76 SET GLOBAL innodb_ft_num_word_optimize=default;