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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
|
# Bug #22709692 FTS QUERY EXCEEDS RESULT CACHE LIMIT
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body),
FULLTEXT (body))ENGINE=InnoDB;
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
SET @default_cache_size = @@GLOBAL.query_cache_size;
SET GLOBAL query_cache_size=0;
# Query involves Ranking
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('MySQL' IN NATURAL LANGUAGE MODE) LIMIT 1;
id title body
6 MySQL Security When configured properly, MySQL ...
# Without optimization
SET @save_dbug = @@debug_dbug;
SET debug_dbug = '+d,fts_union_limit_off';
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('MySQL' IN NATURAL LANGUAGE MODE) LIMIT 1;
id title body
6 MySQL Security When configured properly, MySQL ...
SET debug_dbug = @save_dbug;
# Query involves No Ranking and fts_union operations
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('MySQL' IN BOOLEAN MODE) limit 1;
id title body
6 MySQL Security When configured properly, MySQL ...
# Without optimization
SET debug_dbug = '+d,fts_union_limit_off';
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('MySQL' IN BOOLEAN MODE) limit 1;
id title body
6 MySQL Security When configured properly, MySQL ...
SET debug_dbug = @save_dbug;
# Query involves No ranking and fts_union, fts_ignore
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('MySQL -YourSQL' IN BOOLEAN MODE) limit 1;
id title body
6 MySQL Security When configured properly, MySQL ...
# Without optimization
SET debug_dbug = '+d,fts_union_limit_off';
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('MySQL -YourSQL' IN BOOLEAN MODE) limit 1;
id title body
6 MySQL Security When configured properly, MySQL ...
SET debug_dbug = @save_dbug;
# Query with fts_intersect
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('MySQL +YourSQL' IN BOOLEAN MODE) limit 1;
id title body
5 MySQL vs. YourSQL In the following database comparison ...
# Without optimization
SET debug_dbug = '+d,fts_union_limit_off';
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('MySQL +YourSQL' IN BOOLEAN MODE) limit 1;
id title body
5 MySQL vs. YourSQL In the following database comparison ...
SET debug_dbug = @save_dbug;
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','request doc@oraclehelp.com ...'),
('MySQL Tutorial','request support@oraclehelp.com ...'),
('Trial version','query performace @1255 minute on 2.1Hz
Memory 2GB...'),
('when To Use MySQL Well','for free faq mail@xyz.com ...');
# Query with @distance
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('"MySQL request"@3' IN BOOLEAN MODE) limit 1;
id title body
7 MySQL Tutorial request doc@oraclehelp.com ...
# Without optimization
SET debug_dbug = '+d,fts_union_limit_off';
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('"MySQL request"@3' IN BOOLEAN MODE) limit 1;
id title body
7 MySQL Tutorial request doc@oraclehelp.com ...
SET debug_dbug = @save_dbug;
# Query with subexpression
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('+MySQL +(-support +doc)' IN BOOLEAN MODE) limit 1;
id title body
7 MySQL Tutorial request doc@oraclehelp.com ...
# Without optimization
SET debug_dbug = '+d,fts_union_limit_off';
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('+MySQL +(-support +doc)' IN BOOLEAN MODE) limit 1;
id title body
7 MySQL Tutorial request doc@oraclehelp.com ...
SET debug_dbug = @save_dbug;
# limit num1 OFFSET num2
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('MySQL' in boolean mode) limit 4 offset 2;
id title body
2 How To Use MySQL Well After you went through a ...
3 Optimizing MySQL In this tutorial we will show ...
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
5 MySQL vs. YourSQL In the following database comparison ...
# Without optimization
SET debug_dbug = '+d,fts_union_limit_off';
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('MySQL' in boolean mode) limit 4 offset 2;
id title body
2 How To Use MySQL Well After you went through a ...
3 Optimizing MySQL In this tutorial we will show ...
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
5 MySQL vs. YourSQL In the following database comparison ...
SET debug_dbug = @save_dbug;
# wild card search
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('ru*' IN BOOLEAN MODE) limit 1;
id title body
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
# Without optimization
SET debug_dbug = '+d,fts_union_limit_off';
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('ru*' IN BOOLEAN MODE) limit 1;
id title body
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
SET debug_dbug = @save_dbug;
# phrase search
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('"request support"' IN BOOLEAN MODE) limit 1;
id title body
8 MySQL Tutorial request support@oraclehelp.com ...
# Without optimization
SET debug_dbug = '+d,fts_union_limit_off';
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('"request support"' IN BOOLEAN MODE) limit 1;
id title body
8 MySQL Tutorial request support@oraclehelp.com ...
SET debug_dbug = @save_dbug;
DROP TABLE articles;
SET GLOBAL query_cache_size = @default_cache_size;
|
