Debian Codegraph @

mysql-test/suite/innodb_fts/r/limit_union.result

raw
1 # Bug #22709692 FTS QUERY EXCEEDS RESULT CACHE LIMIT
2 CREATE TABLE articles (
3 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
4 title VARCHAR(200),
5 body TEXT,
6 FULLTEXT (title,body),
7 FULLTEXT (body))ENGINE=InnoDB;
8 INSERT INTO articles (title,body) VALUES
9 ('MySQL Tutorial','DBMS stands for DataBase ...'),
10 ('How To Use MySQL Well','After you went through a ...'),
11 ('Optimizing MySQL','In this tutorial we will show ...'),
12 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
13 ('MySQL vs. YourSQL','In the following database comparison ...'),
14 ('MySQL Security','When configured properly, MySQL ...');
15 SET @default_cache_size = @@GLOBAL.query_cache_size;
16 SET GLOBAL query_cache_size=0;
17 # Query involves Ranking
18 SELECT * FROM articles
19 WHERE MATCH (title,body)
20 AGAINST ('MySQL' IN NATURAL LANGUAGE MODE) LIMIT 1;
21 id title body
22 6 MySQL Security When configured properly, MySQL ...
23 # Without optimization
24 SET @save_dbug = @@debug_dbug;
25 SET debug_dbug = '+d,fts_union_limit_off';
26 SELECT * FROM articles
27 WHERE MATCH (title,body)
28 AGAINST ('MySQL' IN NATURAL LANGUAGE MODE) LIMIT 1;
29 id title body
30 6 MySQL Security When configured properly, MySQL ...
31 SET debug_dbug = @save_dbug;
32 # Query involves No Ranking and fts_union operations
33 SELECT * FROM articles
34 WHERE MATCH (title,body)
35 AGAINST ('MySQL' IN BOOLEAN MODE) limit 1;
36 id title body
37 6 MySQL Security When configured properly, MySQL ...
38 # Without optimization
39 SET debug_dbug = '+d,fts_union_limit_off';
40 SELECT * FROM articles
41 WHERE MATCH (title,body)
42 AGAINST ('MySQL' IN BOOLEAN MODE) limit 1;
43 id title body
44 6 MySQL Security When configured properly, MySQL ...
45 SET debug_dbug = @save_dbug;
46 # Query involves No ranking and fts_union, fts_ignore
47 SELECT * FROM articles
48 WHERE MATCH (title,body)
49 AGAINST ('MySQL -YourSQL' IN BOOLEAN MODE) limit 1;
50 id title body
51 6 MySQL Security When configured properly, MySQL ...
52 # Without optimization
53 SET debug_dbug = '+d,fts_union_limit_off';
54 SELECT * FROM articles
55 WHERE MATCH (title,body)
56 AGAINST ('MySQL -YourSQL' IN BOOLEAN MODE) limit 1;
57 id title body
58 6 MySQL Security When configured properly, MySQL ...
59 SET debug_dbug = @save_dbug;
60 # Query with fts_intersect
61 SELECT * FROM articles
62 WHERE MATCH (title,body)
63 AGAINST ('MySQL +YourSQL' IN BOOLEAN MODE) limit 1;
64 id title body
65 5 MySQL vs. YourSQL In the following database comparison ...
66 # Without optimization
67 SET debug_dbug = '+d,fts_union_limit_off';
68 SELECT * FROM articles
69 WHERE MATCH (title,body)
70 AGAINST ('MySQL +YourSQL' IN BOOLEAN MODE) limit 1;
71 id title body
72 5 MySQL vs. YourSQL In the following database comparison ...
73 SET debug_dbug = @save_dbug;
74 INSERT INTO articles (title,body) VALUES
75 ('MySQL Tutorial','request [email protected] ...'),
76 ('MySQL Tutorial','request [email protected] ...'),
77 ('Trial version','query performace @1255 minute on 2.1Hz
78 Memory 2GB...'),
79 ('when To Use MySQL Well','for free faq [email protected] ...');
80 # Query with @distance
81 SELECT * FROM articles
82 WHERE MATCH (title,body)
83 AGAINST ('"MySQL request"@3' IN BOOLEAN MODE) limit 1;
84 id title body
85 7 MySQL Tutorial request [email protected] ...
86 # Without optimization
87 SET debug_dbug = '+d,fts_union_limit_off';
88 SELECT * FROM articles
89 WHERE MATCH (title,body)
90 AGAINST ('"MySQL request"@3' IN BOOLEAN MODE) limit 1;
91 id title body
92 7 MySQL Tutorial request [email protected] ...
93 SET debug_dbug = @save_dbug;
94 # Query with subexpression
95 SELECT * FROM articles
96 WHERE MATCH (title,body)
97 AGAINST ('+MySQL +(-support +doc)' IN BOOLEAN MODE) limit 1;
98 id title body
99 7 MySQL Tutorial request [email protected] ...
100 # Without optimization
101 SET debug_dbug = '+d,fts_union_limit_off';
102 SELECT * FROM articles
103 WHERE MATCH (title,body)
104 AGAINST ('+MySQL +(-support +doc)' IN BOOLEAN MODE) limit 1;
105 id title body
106 7 MySQL Tutorial request [email protected] ...
107 SET debug_dbug = @save_dbug;
108 # limit num1 OFFSET num2
109 SELECT * FROM articles
110 WHERE MATCH (title,body)
111 AGAINST ('MySQL' in boolean mode) limit 4 offset 2;
112 id title body
113 2 How To Use MySQL Well After you went through a ...
114 3 Optimizing MySQL In this tutorial we will show ...
115 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
116 5 MySQL vs. YourSQL In the following database comparison ...
117 # Without optimization
118 SET debug_dbug = '+d,fts_union_limit_off';
119 SELECT * FROM articles
120 WHERE MATCH (title,body)
121 AGAINST ('MySQL' in boolean mode) limit 4 offset 2;
122 id title body
123 2 How To Use MySQL Well After you went through a ...
124 3 Optimizing MySQL In this tutorial we will show ...
125 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
126 5 MySQL vs. YourSQL In the following database comparison ...
127 SET debug_dbug = @save_dbug;
128 # wild card search
129 SELECT * FROM articles
130 WHERE MATCH (title,body)
131 AGAINST ('ru*' IN BOOLEAN MODE) limit 1;
132 id title body
133 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
134 # Without optimization
135 SET debug_dbug = '+d,fts_union_limit_off';
136 SELECT * FROM articles
137 WHERE MATCH (title,body)
138 AGAINST ('ru*' IN BOOLEAN MODE) limit 1;
139 id title body
140 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
141 SET debug_dbug = @save_dbug;
142 # phrase search
143 SELECT * FROM articles
144 WHERE MATCH (title,body)
145 AGAINST ('"request support"' IN BOOLEAN MODE) limit 1;
146 id title body
147 8 MySQL Tutorial request [email protected] ...
148 # Without optimization
149 SET debug_dbug = '+d,fts_union_limit_off';
150 SELECT * FROM articles
151 WHERE MATCH (title,body)
152 AGAINST ('"request support"' IN BOOLEAN MODE) limit 1;
153 id title body
154 8 MySQL Tutorial request [email protected] ...
155 SET debug_dbug = @save_dbug;
156 DROP TABLE articles;
157 SET GLOBAL query_cache_size = @default_cache_size;