1
CREATE TABLE fts_test (
2
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
6
INSERT INTO fts_test (title,body) VALUES
7
('MySQL Tutorial','DBMS stands for DataBase ...') ,
8
('How To Use MySQL Well','After you went through a ...'),
9
('Optimizing MySQL','In this tutorial we will show ...'),
10
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
11
('MySQL vs. YourSQL','In the following database comparison ...'),
12
('MySQL Security','When configured properly, MySQL ...');
13
CREATE FULLTEXT INDEX idx on fts_test (title, body);
15
Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
16
SELECT * FROM fts_test WHERE MATCH (title, body)
17
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
19
1 MySQL Tutorial DBMS stands for DataBase ...
20
3 Optimizing MySQL In this tutorial we will show ...
21
DROP INDEX idx ON fts_test;
22
INSERT INTO fts_test (title,body) VALUES
23
('MySQL Tutorial','DBMS stands for DataBase ...') ,
24
('How To Use MySQL Well','After you went through a ...'),
25
('Optimizing MySQL','In this tutorial we will show ...'),
26
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
27
('MySQL vs. YourSQL','In the following database comparison ...'),
28
('MySQL Security','When configured properly, MySQL ...');
29
CREATE FULLTEXT INDEX idx on fts_test (title, body);
30
SELECT * FROM fts_test WHERE MATCH (title, body)
31
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
33
1 MySQL Tutorial DBMS stands for DataBase ...
34
3 Optimizing MySQL In this tutorial we will show ...
35
7 MySQL Tutorial DBMS stands for DataBase ...
36
9 Optimizing MySQL In this tutorial we will show ...
37
SELECT * FROM fts_test WHERE MATCH (title,body)
38
AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
40
6 MySQL Security When configured properly, MySQL ...
41
12 MySQL Security When configured properly, MySQL ...
42
1 MySQL Tutorial DBMS stands for DataBase ...
43
2 How To Use MySQL Well After you went through a ...
44
3 Optimizing MySQL In this tutorial we will show ...
45
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
46
7 MySQL Tutorial DBMS stands for DataBase ...
47
8 How To Use MySQL Well After you went through a ...
48
9 Optimizing MySQL In this tutorial we will show ...
49
10 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
50
TRUNCATE TABLE fts_test;
51
DROP INDEX idx ON fts_test;
52
INSERT INTO fts_test (title,body) VALUES
53
('MySQL Tutorial','DBMS stands for DataBase ...') ,
54
('How To Use MySQL Well','After you went through a ...'),
55
('Optimizing MySQL','In this tutorial we will show ...'),
56
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
57
('MySQL vs. YourSQL','In the following database comparison ...'),
58
('MySQL Security','When configured properly, MySQL ...');
59
CREATE FULLTEXT INDEX idx on fts_test (title, body);
60
SELECT * FROM fts_test WHERE MATCH (title, body)
61
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
63
1 MySQL Tutorial DBMS stands for DataBase ...
64
3 Optimizing MySQL In this tutorial we will show ...
66
CREATE TABLE fts_test (
67
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
71
create unique index FTS_DOC_ID_INDEX on fts_test(FTS_DOC_ID);
72
INSERT INTO fts_test (title,body) VALUES
73
('MySQL Tutorial','DBMS stands for DataBase ...') ,
74
('How To Use MySQL Well','After you went through a ...'),
75
('Optimizing MySQL','In this tutorial we will show ...'),
76
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
77
('MySQL vs. YourSQL','In the following database comparison ...'),
78
('MySQL Security','When configured properly, MySQL ...');
79
CREATE FULLTEXT INDEX idx on fts_test (title, body) LOCK=NONE;
80
ERROR 0A000: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED.
81
CREATE FULLTEXT INDEX idx on fts_test (title, body);
82
ALTER TABLE fts_test ROW_FORMAT=REDUNDANT, LOCK=NONE;
83
ERROR 0A000: LOCK=NONE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try LOCK=SHARED.
84
ALTER TABLE fts_test ROW_FORMAT=REDUNDANT;
85
SELECT * FROM fts_test WHERE MATCH (title, body)
86
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
88
1 MySQL Tutorial DBMS stands for DataBase ...
89
3 Optimizing MySQL In this tutorial we will show ...
90
drop index idx on fts_test;
91
CREATE FULLTEXT INDEX idx on fts_test (title, body);
92
SELECT * FROM fts_test WHERE MATCH (title, body)
93
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
95
1 MySQL Tutorial DBMS stands for DataBase ...
96
3 Optimizing MySQL In this tutorial we will show ...
97
drop index idx on fts_test;
98
drop index FTS_DOC_ID_INDEX on fts_test;
99
CREATE FULLTEXT INDEX idx on fts_test (title, body);
100
SELECT * FROM fts_test WHERE MATCH (title, body)
101
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
102
FTS_DOC_ID title body
103
1 MySQL Tutorial DBMS stands for DataBase ...
104
3 Optimizing MySQL In this tutorial we will show ...
106
CREATE TABLE fts_test (
107
FTS_DOC_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
108
title varchar(255) NOT NULL DEFAULT '',
109
text mediumtext NOT NULL,
110
PRIMARY KEY (FTS_DOC_ID),
111
UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
112
FULLTEXT KEY idx (title,text)
113
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
114
set @@auto_increment_increment=10;
115
INSERT INTO fts_test (title, text) VALUES
116
('MySQL Tutorial','DBMS stands for DataBase ...'),
117
('How To Use MySQL Well','After you went through a ...'),
118
('Optimizing MySQL','In this tutorial we will show ...'),
119
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
120
('MySQL vs. YourSQL','In the following database comparison ...'),
121
('MySQL Security','When configured properly, MySQL ...');
122
ANALYZE TABLE fts_test;
123
set @@auto_increment_increment=1;
124
select *, match(title, text) AGAINST ('database') as score
125
from fts_test order by score desc;
126
FTS_DOC_ID title text score
127
11 MySQL Tutorial DBMS stands for DataBase ... 0.22764469683170319
128
51 MySQL vs. YourSQL In the following database comparison ... 0.22764469683170319
129
21 How To Use MySQL Well After you went through a ... 0
130
31 Optimizing MySQL In this tutorial we will show ... 0
131
41 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 0
132
61 MySQL Security When configured properly, MySQL ... 0
133
drop index idx on fts_test;
135
CREATE TABLE fts_test (
136
FTS_DOC_ID int(20) unsigned NOT NULL AUTO_INCREMENT,
137
title varchar(255) NOT NULL DEFAULT '',
138
text mediumtext NOT NULL,
139
PRIMARY KEY (FTS_DOC_ID),
140
UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
141
FULLTEXT KEY idx (title,text)
142
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
143
ERROR 42000: Incorrect column name 'FTS_DOC_ID'
144
CREATE TABLE fts_test (
145
FTS_DOC_ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
146
title varchar(255) NOT NULL DEFAULT '',
147
text mediumtext NOT NULL,
148
PRIMARY KEY (FTS_DOC_ID),
149
KEY FTS_DOC_ID_INDEX (FTS_DOC_ID),
150
FULLTEXT KEY idx (title,text)
151
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
152
ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index
153
CREATE TABLE articles (
154
FTS_DOC_ID BIGINT UNSIGNED NOT NULL ,
158
INSERT INTO articles (FTS_DOC_ID, title, body) VALUES
159
(9, 'MySQL Tutorial','DBMS stands for DataBase ...'),
160
(10, 'How To Use MySQL Well','After you went through a ...'),
161
(12, 'Optimizing MySQL','In this tutorial we will show ...'),
162
(14,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
163
(19, 'MySQL vs. YourSQL','In the following database comparison ...'),
164
(20, 'MySQL Security','When configured properly, MySQL ...');
165
ALTER TABLE articles ADD FULLTEXT INDEX idx3 (title),
166
ADD FULLTEXT INDEX idx5 (title);
167
ERROR HY000: InnoDB presently supports one FULLTEXT index creation at a time
168
CREATE FULLTEXT INDEX idx on articles (title);
169
ALTER TABLE articles ADD FULLTEXT INDEX idx3 (title);
170
ALTER TABLE articles ADD INDEX t20 (title(20)), LOCK=NONE;
171
ALTER TABLE articles DROP INDEX t20;
172
INSERT INTO articles (FTS_DOC_ID, title, body) VALUES
173
(29, 'MySQL Tutorial','DBMS stands for DataBase ...'),
174
(30, 'How To Use MySQL Well','After you went through a ...'),
175
(32, 'Optimizing MySQL','In this tutorial we will show ...'),
176
(34,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
177
(39, 'MySQL vs. YourSQL','In the following database comparison ...'),
178
(40, 'MySQL Security','When configured properly, MySQL ...');
179
SELECT * FROM articles WHERE MATCH (title)
180
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
181
FTS_DOC_ID title body
182
9 MySQL Tutorial DBMS stands for DataBase ...
183
29 MySQL Tutorial DBMS stands for DataBase ...
184
DROP INDEX idx ON articles;
185
SELECT * FROM articles WHERE MATCH (title)
186
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
187
FTS_DOC_ID title body
188
9 MySQL Tutorial DBMS stands for DataBase ...
189
29 MySQL Tutorial DBMS stands for DataBase ...
190
CREATE FULLTEXT INDEX idx on articles (title, body);
191
SELECT * FROM articles WHERE MATCH (title, body)
192
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
193
FTS_DOC_ID title body
194
9 MySQL Tutorial DBMS stands for DataBase ...
195
12 Optimizing MySQL In this tutorial we will show ...
196
29 MySQL Tutorial DBMS stands for DataBase ...
197
32 Optimizing MySQL In this tutorial we will show ...
199
create table articles(`FTS_DOC_ID` serial,
200
`col32` timestamp not null,`col115` text) engine=innodb;
201
create fulltext index `idx5` on articles(`col115`) ;
202
alter ignore table articles add primary key (`col32`) ;
204
CREATE TABLE articles (
205
id INT UNSIGNED NOT NULL,
209
INSERT INTO articles VALUES
210
(1, 'MySQL Tutorial','DBMS stands for DataBase ...') ,
211
(2, 'How To Use MySQL Well','After you went through a ...'),
212
(3, 'Optimizing MySQL','In this tutorial we will show ...'),
213
(4, '1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
214
(5, 'MySQL vs. YourSQL','In the following database comparison ...'),
215
(6, 'MySQL Security','When configured properly, MySQL ...');
216
CREATE FULLTEXT INDEX idx on articles (title, body);
218
Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
219
DROP INDEX idx ON articles;
220
CREATE UNIQUE INDEX idx2 ON articles(id);
221
CREATE FULLTEXT INDEX idx on articles (title, body);
223
Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
224
SELECT * FROM articles WHERE MATCH (title, body)
225
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
227
1 MySQL Tutorial DBMS stands for DataBase ...
228
3 Optimizing MySQL In this tutorial we will show ...