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