Re: Difference between using Merge vs. symbolic-links approach?

[M P]

I just did a quick test to verify, given two tables A and B each with a FULLTEXT index, that:
1) you cannot create a union of both A and B using a MERGE table that itself contains a FULLTEXT key.
2) you can create a union of both A and B using a MERGE table that does _not_ itself contains a FULLTEXT key, but then that MERGE table is not FULLTEXT searchable (even though both tables it "merges" have FULLTEXT indexes)
3) you can create a simple VIEW ("select * from A") using the merge algorithm, and the view is full-text searchable.
4) you cannot create a VIEW based on a UNION using the merge algorithm... trying to do so yields the warning:
"Warning | 1354 | View merge algorithm can't be used here for now"


In conclusion, given the current mySQL version, if you need to do a full-text search over the combined set of rows in A and B, whether you attempt to define that combination as a VIEW (i.e., with union) or as a MERGE table, it simply isn't possible.

That's really too bad. A Google search into this issue also uncovered that
"Support for full-text search in MERGE tables"
has been an official mySQL "TO DO" item for 3 years now. I hope that means it's almost time. :)



Edited 4 time(s). Last edit at 06/12/2007 02:50AM by M P.