Debian Codegraph @

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

raw
1 drop table if exists t1;
2 CREATE TABLE t1 (
3 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
4 a VARCHAR(200),
5 b TEXT
6 ) ENGINE = InnoDB STATS_PERSISTENT=0;
7 INSERT INTO t1 (a,b) VALUES
8 ('MySQL Tutorial','DBMS stands for DataBase ...') ,
9 ('How To Use MySQL Well','After you went through a ...'),
10 ('Optimizing MySQL','In this tutorial we will show ...');
11 ALTER TABLE t1 ADD FULLTEXT INDEX idx_1 (a);
12 ALTER TABLE t1 ADD FULLTEXT INDEX idx_2 (b);
13 SHOW CREATE TABLE t1;
14 Table Create Table
15 t1 CREATE TABLE `t1` (
16 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
17 `a` varchar(200) DEFAULT NULL,
18 `b` text DEFAULT NULL,
19 PRIMARY KEY (`id`),
20 FULLTEXT KEY `idx_1` (`a`),
21 FULLTEXT KEY `idx_2` (`b`)
22 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci STATS_PERSISTENT=0
23 START TRANSACTION;
24 INSERT INTO t1 (a,b) VALUES
25 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
26 ('MySQL vs. YourSQL','In the following database comparison ...'),
27 ('MySQL Security','When configured properly, MySQL ...');
28 ROLLBACK;
29 SELECT * FROM t1 WHERE MATCH (a)
30 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
31 id a b
32 1 MySQL Tutorial DBMS stands for DataBase ...
33 select * from t1 where MATCH(a) AGAINST("+mysql +Tutorial" IN BOOLEAN MODE);
34 id a b
35 1 MySQL Tutorial DBMS stands for DataBase ...
36 select * from t1 where MATCH(b) AGAINST("+Tutorial" IN BOOLEAN MODE);
37 id a b
38 3 Optimizing MySQL In this tutorial we will show ...
39 select * from t1 where MATCH(b) AGAINST("+stands +(DataBase)" IN BOOLEAN MODE);
40 id a b
41 1 MySQL Tutorial DBMS stands for DataBase ...
42 select * from t1 where MATCH(b) AGAINST("+DataBase -(comparison)" IN BOOLEAN MODE);
43 id a b
44 1 MySQL Tutorial DBMS stands for DataBase ...
45 select *, MATCH(a) AGAINST("Optimizing MySQL" IN BOOLEAN MODE) as x from t1;
46 id a b x
47 1 MySQL Tutorial DBMS stands for DataBase ... 0.000000001885928302414186
48 2 How To Use MySQL Well After you went through a ... 0.000000001885928302414186
49 3 Optimizing MySQL In this tutorial we will show ... 0.22764469683170319
50 select *, MATCH(b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
51 id a b x
52 1 MySQL Tutorial DBMS stands for DataBase ... 0
53 2 How To Use MySQL Well After you went through a ... 0
54 3 Optimizing MySQL In this tutorial we will show ... 0
55 select * from t1 where MATCH a AGAINST ("+Optimiz* +Optimiz*" IN BOOLEAN MODE);
56 id a b
57 3 Optimizing MySQL In this tutorial we will show ...
58 select * from t1 where MATCH b AGAINST ('"DBMS stands"' IN BOOLEAN MODE);
59 id a b
60 1 MySQL Tutorial DBMS stands for DataBase ...
61 select * from t1 where MATCH b AGAINST ('"DBMS STANDS"' IN BOOLEAN MODE);
62 id a b
63 1 MySQL Tutorial DBMS stands for DataBase ...
64 select * from t1 where MATCH(b) AGAINST ("DataBase" WITH QUERY EXPANSION);
65 id a b
66 1 MySQL Tutorial DBMS stands for DataBase ...
67 select * from t1 where MATCH(a) AGAINST ("Security" WITH QUERY EXPANSION);
68 id a b
69 ALTER TABLE t1 DROP INDEX idx_1;
70 ALTER TABLE t1 DROP INDEX idx_2;
71 ALTER TABLE t1 ADD FULLTEXT INDEX idx_1 (a);
72 ALTER TABLE t1 ADD FULLTEXT INDEX idx_2 (b);
73 SELECT * FROM t1 WHERE MATCH (a)
74 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
75 id a b
76 1 MySQL Tutorial DBMS stands for DataBase ...
77 select * from t1 where MATCH(a) AGAINST("+mysql +Tutorial" IN BOOLEAN MODE);
78 id a b
79 1 MySQL Tutorial DBMS stands for DataBase ...
80 select * from t1 where MATCH(b) AGAINST("+Tutorial" IN BOOLEAN MODE);
81 id a b
82 3 Optimizing MySQL In this tutorial we will show ...
83 select * from t1 where MATCH(b) AGAINST("+stands +(DataBase)" IN BOOLEAN MODE);
84 id a b
85 1 MySQL Tutorial DBMS stands for DataBase ...
86 select * from t1 where MATCH(b) AGAINST("+DataBase -(comparison)" IN BOOLEAN MODE);
87 id a b
88 1 MySQL Tutorial DBMS stands for DataBase ...
89 select *, MATCH(a) AGAINST("Optimizing MySQL" IN BOOLEAN MODE) as x from t1;
90 id a b x
91 1 MySQL Tutorial DBMS stands for DataBase ... 0.000000001885928302414186
92 2 How To Use MySQL Well After you went through a ... 0.000000001885928302414186
93 3 Optimizing MySQL In this tutorial we will show ... 0.22764469683170319
94 select *, MATCH(b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1;
95 id a b x
96 1 MySQL Tutorial DBMS stands for DataBase ... 0
97 2 How To Use MySQL Well After you went through a ... 0
98 3 Optimizing MySQL In this tutorial we will show ... 0
99 select * from t1 where MATCH a AGAINST ("+Optimiz* +Optimiz*" IN BOOLEAN MODE);
100 id a b
101 3 Optimizing MySQL In this tutorial we will show ...
102 select * from t1 where MATCH b AGAINST ('"DBMS stands"' IN BOOLEAN MODE);
103 id a b
104 1 MySQL Tutorial DBMS stands for DataBase ...
105 select * from t1 where MATCH b AGAINST ('"DBMS STANDS"' IN BOOLEAN MODE);
106 id a b
107 1 MySQL Tutorial DBMS stands for DataBase ...
108 select * from t1 where MATCH(b) AGAINST ("DataBase" WITH QUERY EXPANSION);
109 id a b
110 1 MySQL Tutorial DBMS stands for DataBase ...
111 select * from t1 where MATCH(a) AGAINST ("Security" WITH QUERY EXPANSION);
112 id a b
113 INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...');
114 INSERT INTO t1 (a,b) VALUES
115 ('test proximity search, test, proximity and phrase',
116 'search, with proximity innodb');
117 INSERT INTO t1 (a,b) VALUES
118 ('test proximity fts search, test, proximity and phrase',
119 'search, with proximity innodb');
120 INSERT INTO t1 (a,b) VALUES
121 ('test more of proximity for fts search, test, more proximity and phrase',
122 'search, with proximity innodb');
123 SELECT * FROM t1
124 WHERE MATCH (a)
125 AGAINST ('"proximity search"@3' IN BOOLEAN MODE);
126 id a b
127 8 test proximity search, test, proximity and phrase search, with proximity innodb
128 9 test proximity fts search, test, proximity and phrase search, with proximity innodb
129 SELECT * FROM t1
130 WHERE MATCH (a)
131 AGAINST ('"proximity search"@2' IN BOOLEAN MODE);
132 id a b
133 8 test proximity search, test, proximity and phrase search, with proximity innodb
134 SELECT * FROM t1
135 WHERE MATCH (b)
136 AGAINST ('"proximity innodb"@4' IN BOOLEAN MODE);
137 id a b
138 8 test proximity search, test, proximity and phrase search, with proximity innodb
139 9 test proximity fts search, test, proximity and phrase search, with proximity innodb
140 10 test more of proximity for fts search, test, more proximity and phrase search, with proximity innodb
141 SELECT * FROM t1
142 WHERE MATCH (a)
143 AGAINST ('"test proximity"@3' IN BOOLEAN MODE);
144 id a b
145 8 test proximity search, test, proximity and phrase search, with proximity innodb
146 9 test proximity fts search, test, proximity and phrase search, with proximity innodb
147 10 test more of proximity for fts search, test, more proximity and phrase search, with proximity innodb
148 SELECT * FROM t1
149 WHERE MATCH (a)
150 AGAINST ('"more test proximity"@3' IN BOOLEAN MODE);
151 id a b
152 10 test more of proximity for fts search, test, more proximity and phrase search, with proximity innodb
153 SELECT * FROM t1
154 WHERE MATCH (a)
155 AGAINST ('"more test proximity"@2' IN BOOLEAN MODE);
156 id a b
157 SELECT * FROM t1
158 WHERE MATCH (a)
159 AGAINST ('"more fts proximity"@02' IN BOOLEAN MODE);
160 id a b
161 SELECT * FROM t1 WHERE CONCAT(t1.a,t1.b) IN (
162 SELECT CONCAT(a,b) FROM t1 AS t2 WHERE
163 MATCH (t2.a) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)
164 ) OR t1.id = 3 ;
165 id a b
166 1 MySQL Tutorial DBMS stands for DataBase ...
167 3 Optimizing MySQL In this tutorial we will show ...
168 SELECT * FROM t1 WHERE CONCAT(t1.a,t1.b) IN (
169 SELECT CONCAT(a,b) FROM t1 AS t2
170 WHERE MATCH (t2.a) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)
171 AND t2.id != 3) ;
172 id a b
173 1 MySQL Tutorial DBMS stands for DataBase ...
174 SELECT * FROM t1 WHERE id IN (SELECT MIN(id) FROM t1 WHERE
175 MATCH (b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)) OR id = 3 ;
176 id a b
177 3 Optimizing MySQL In this tutorial we will show ...
178 SELECT * FROM t1 WHERE id NOT IN (SELECT MIN(id) FROM t1
179 WHERE MATCH (b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)) ;
180 id a b
181 1 MySQL Tutorial DBMS stands for DataBase ...
182 2 How To Use MySQL Well After you went through a ...
183 7 test query expansion for database ...
184 8 test proximity search, test, proximity and phrase search, with proximity innodb
185 9 test proximity fts search, test, proximity and phrase search, with proximity innodb
186 10 test more of proximity for fts search, test, more proximity and phrase search, with proximity innodb
187 SELECT * FROM t1 WHERE EXISTS (SELECT t2.id FROM t1 AS t2 WHERE
188 MATCH (t2.b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)
189 AND t1.id = t2.id) ;
190 id a b
191 3 Optimizing MySQL In this tutorial we will show ...
192 SELECT * FROM t1 WHERE NOT EXISTS (SELECT t2.id FROM t1 AS t2 WHERE
193 MATCH (t2.a) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE)
194 AND t1.id = t2.id) ;
195 id a b
196 2 How To Use MySQL Well After you went through a ...
197 3 Optimizing MySQL In this tutorial we will show ...
198 7 test query expansion for database ...
199 8 test proximity search, test, proximity and phrase search, with proximity innodb
200 9 test proximity fts search, test, proximity and phrase search, with proximity innodb
201 10 test more of proximity for fts search, test, more proximity and phrase search, with proximity innodb
202 SELECT * FROM t1 WHERE t1.id = (SELECT MAX(t2.id) FROM t1 AS t2 WHERE
203 MATCH(t2.a) AGAINST ('"proximity search"@3' IN BOOLEAN MODE));
204 id a b
205 9 test proximity fts search, test, proximity and phrase search, with proximity innodb
206 SELECT * FROM t1 WHERE t1.id > (SELECT MIN(t2.id) FROM t1 AS t2 WHERE
207 MATCH(t2.b) AGAINST ('"proximity innodb"@3' IN BOOLEAN MODE));
208 id a b
209 9 test proximity fts search, test, proximity and phrase search, with proximity innodb
210 10 test more of proximity for fts search, test, more proximity and phrase search, with proximity innodb
211 DROP TABLE t1;