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 FULLTEXT index.
The relevant query looks something like:
SELECT ...
FROM inno_tbl i
JOIN ft_tbl f ON i.id = f.id
WHERE i.stuff...
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 FULLTEXT.
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 REPLACE or 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.
BEGIN;
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';
COMMIT;
Cases...
- 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...
If a 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.
