Hello
[mysql 3.23.28, sparc64-unknown-linux-gnu]
1) Does FULLTEXT indexes work with MERGE tables? I obtain error 1030 (ER_GET_ERRNO):
CREATE TABLE t1 (a TEXT, FULLTEXT (a));
INSERT INTO t1 VALUES ('MySQL'),('has'),('now'),('support'),('for full-text search');
CREATE TABLE t2 (a TEXT, FULLTEXT (a));
INSERT INTO t2 VALUES ('2 MySQL'),('2 has'),('2 now'),('2 support'),('2 for full-text
search');
CREATE TABLE tt (a TEXT, FULLTEXT (a)) TYPE=MERGE UNION=(t1,t2);
mysql> SELECT * FROM t1 WHERE MATCH (a) AGAINST ('MySQL');
+-------+
| a |
+-------+
| MySQL |
+-------+
1 row in set (0.00 sec)
mysql> SELECT * FROM tt WHERE MATCH (a) AGAINST ('MySQL');
ERROR 1030: Got error -1 from table handler
2) If I do an OR query on t1, the fulltext index is not used:
mysql> EXPLAIN SELECT * FROM t1 WHERE MATCH (a) AGAINST ('MySQL');
+-------+----------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+----------+---------------+------+---------+------+------+------------+
| t1 | fulltext | a | a | 5 | | 1 | where used |
+-------+----------+---------------+------+---------+------+------+------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM t1 WHERE MATCH (a) AGAINST ('MySQL') OR MATCH (a)
AGAINST ('support');
+-------+------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+------+------------+
| t1 | ALL | NULL | NULL | NULL | NULL | 5 | where used |
+-------+------+---------------+------+---------+------+------+------------+
1 row in set (0.00 sec)
...even if I suggest USE INDEX (a). Is this wanted?
cheers
--
TS
