Debian Codegraph @

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

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