Debian Codegraph @

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

raw
1 # This is the basic function tests for INNODB_FT_INDEX_TABLE
2 # and INNODB_FT_INDEX_TABLE in INFORMATION_SCHEMA.
3
4
5 # Must have debug code to use SET SESSION debug
6 -- source include/have_debug.inc
7
8 SET GLOBAL INNODB_OPTIMIZE_FULLTEXT_ONLY=1;
9
10 # Test Case 1: Test Result Cache Limit
11 CREATE TABLE articles (
12 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
13 title VARCHAR(200),
14 content TEXT
15 ) ENGINE= InnoDB;
16
17 CREATE FULLTEXT INDEX idx ON articles (title, content);
18
19 INSERT INTO articles (title, content) VALUES
20 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
21 ('How To Use MySQL Well','After you went through a ...'),
22 ('Optimizing MySQL','In this tutorial we will show ...'),
23 ('1001 MySQL Tricks','How to use full-text search engine'),
24 ('Go MySQL Tricks','How to use full text search engine');
25
26 SET GLOBAL innodb_ft_aux_table="test/articles";
27
28 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
29
30 OPTIMIZE TABLE articles;
31
32 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
33
34 SET SESSION debug="+d,fts_instrument_result_cache_limit";
35
36 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
37
38 SET SESSION debug="-d,fts_instrument_result_cache_limit";
39
40 DROP TABLE articles;
41
42 SET GLOBAL innodb_ft_result_cache_limit=default;
43
44 # Test Case 2: Test Multiple Indexes
45 CREATE TABLE articles (
46 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
47 title VARCHAR(200),
48 content TEXT
49 ) ENGINE= InnoDB;
50
51 CREATE FULLTEXT INDEX idx_t ON articles (title);
52
53 CREATE FULLTEXT INDEX idx_c ON articles (content);
54
55 INSERT INTO articles (title, content) VALUES
56 ('MySQL Tutorial','DBMS stands for MySQL DataBase ...'),
57 ('How To Use MySQL Well','After you went through a ...'),
58 ('Optimizing MySQL','In this tutorial we will show ...'),
59 ('1001 MySQL Tricks','How to use full-text search engine'),
60 ('Go MySQL Tricks','How to use full text search engine');
61
62 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
63
64 SET GLOBAL innodb_ft_aux_table="test/articles";
65
66 OPTIMIZE TABLE articles;
67
68 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
69
70 DROP TABLE articles;
71
72 SET NAMES utf8mb4;
73
74 # Test Case 3: Test UFT8 Charset
75 CREATE TABLE articles (
76 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
77 title VARCHAR(200)
78 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
79
80 CREATE FULLTEXT INDEX idx ON articles (title);
81
82 INSERT INTO articles (title) VALUES
83 ('相亲相爱'),('怜香惜爱'),('充满可爱'),('爱恨交织');
84
85 SET GLOBAL innodb_ft_aux_table="test/articles";
86
87 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
88
89 OPTIMIZE TABLE articles;
90
91 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
92
93 DROP TABLE articles;
94
95 # Test Case 4: Test GB2312 Charset
96 CREATE TABLE articles (
97 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
98 title VARCHAR(200)
99 ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
100
101 CREATE FULLTEXT INDEX idx ON articles (title);
102
103 INSERT INTO articles (title) VALUES
104 ('相亲相爱'),('怜香惜爱'),('充满可爱'),('爱恨交织');
105
106 SET GLOBAL innodb_ft_aux_table="test/articles";
107
108 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
109
110 OPTIMIZE TABLE articles;
111
112 SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
113
114 DROP TABLE articles;
115
116 # Restore global variables
117 SET GLOBAL innodb_ft_aux_table=default;
118
119 SET GLOBAL INNODB_OPTIMIZE_FULLTEXT_ONLY=default;