Description:
If all tables in a MERGE union have full text indexes, the MERGE engine is unable to take advantage of them and provide a full text search.
Even if you try to create a full-text index with an identical specification as the tables in the union have, the result is:
ERROR 1214: The used table type doesn't support FULLTEXT indexes
How to repeat:
mysql> CREATE TABLE a (sample TINYTEXT);
mysql> CREATE FULLTEXT INDEX test_idx ON a (sample);
mysql> CREATE TABLE b (sample TINYTEXT);
mysql> CREATE FULLTEXT INDEX test_idx ON b (sample);
mysql> CREATE TEMPORARY TABLE ab (sample TINYTEXT) ENGINE=MERGE UNION=(a,b);
mysql> INSERT INTO a VALUES('foo');
mysql> INSERT INTO b VALUES('bar');
mysql> SELECT * FROM ab;
+--------+
| sample |
+--------+
| foo |
| bar |
+--------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM ab WHERE MATCH(sample) AGAINST('foo');
ERROR 1191: Can't find FULLTEXT index matching the column list
mysql> SELECT * FROM ab WHERE MATCH(sample) AGAINST('foo' IN BOOLEAN MODE);
+--------+
| sample |
+--------+
| foo |
| bar |
+--------+
2 rows in set (0.00 sec)
mysql> CREATE FULLTEXT INDEX test_idx ON ab (sample);
ERROR 1214: The used table type doesn't support FULLTEXT indexes
Suggested fix:
Full-text index support would be great for the MERGE engine.
Description: If all tables in a MERGE union have full text indexes, the MERGE engine is unable to take advantage of them and provide a full text search. Even if you try to create a full-text index with an identical specification as the tables in the union have, the result is: ERROR 1214: The used table type doesn't support FULLTEXT indexes How to repeat: mysql> CREATE TABLE a (sample TINYTEXT); mysql> CREATE FULLTEXT INDEX test_idx ON a (sample); mysql> CREATE TABLE b (sample TINYTEXT); mysql> CREATE FULLTEXT INDEX test_idx ON b (sample); mysql> CREATE TEMPORARY TABLE ab (sample TINYTEXT) ENGINE=MERGE UNION=(a,b); mysql> INSERT INTO a VALUES('foo'); mysql> INSERT INTO b VALUES('bar'); mysql> SELECT * FROM ab; +--------+ | sample | +--------+ | foo | | bar | +--------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM ab WHERE MATCH(sample) AGAINST('foo'); ERROR 1191: Can't find FULLTEXT index matching the column list mysql> SELECT * FROM ab WHERE MATCH(sample) AGAINST('foo' IN BOOLEAN MODE); +--------+ | sample | +--------+ | foo | | bar | +--------+ 2 rows in set (0.00 sec) mysql> CREATE FULLTEXT INDEX test_idx ON ab (sample); ERROR 1214: The used table type doesn't support FULLTEXT indexes Suggested fix: Full-text index support would be great for the MERGE engine.