It works. It performs reasonably well.
You have two tables, one is InnoDB and contains most of the attributes of an entity. The other is MyISAM, is 1:1 with the first table, and contains a
TEXT field, plus
The relevant query looks something like:
FROM inno_tbl i
JOIN ft_tbl f ON i.id = f.id
AND MATCH (f.text) AGAINST (...);
I think that the
MATCH will always occur first, even though the tests on
i might be more selective. That is the nature of
jkavalik mentioned some consistency issues; but these can mostly be avoided by carefully picking the order in which you
INSERT into the two tables, and whether you use
IODKU instead of a plain
INSERT for one of the `INSERTs.
(I believe I have done what you describe in one or two projects. I have since measured that InnoDB's
FULLTEXT seems to be faster.)
Bottom line: Go ahead and do it.
Addendum How to order the statements to minimize data integrity problems.
INSERT into InnoDB table
$id = SELECT LAST_INSERT_ID(); -- assuming you are using an AUTO_INCREMENT
INSERT INTO MyISAM_table
(id, text) VALUES ($id, '$escaped_text')
ON DUPLICATE KEY UPDATE
text = '$escaped_text';
- There is no integrity problem if both
INSERTs succeed or both fail.
- If there is an error on the MyISAM
INSERT, you should catch it and
ROLLBACK instead of
COMMIT. Hence good integrity.
- If the MyISAM succeeds but the
COMMIT fails, there will be an extra row in the MyISAM Table, for which there is no InnoDB row. Two cases...
FULLTEXT search hits that row, then the
JOIN to the InnoDB table will fail, thereby getting the 'right' answer (at a minor cost).
If you come along later and reuse that
id to re-insert the row (or insert a different row), then the IODKU will "do the right thing". All is well.
Note (aimed at other readers): This technique of mixing an InnoDB table with a non-transactional
INSERT works in other cases. Consider putting an image (.jpg) in a file, while putting the image's 'meta data' in an InnoDB row. At worst, you might have an extra or duplicate image stored in the file system.