Mysql – Integrate MySql Full Text searches in CMS

full-text-searchinnodblikemyisamMySQL

I'm developing my own CMS and using MySql v5.5 DB to save the content items records.

Due to a technical limitations, I can't upgrade my database to a newer version that support Full-Text searches on InnoDB tables.

Would it be a good practise to use MyISAM tables for textual searches while the "real" data is saved in InnoDB storage engine?

Example:

  1. The user create content item and save it to the database
  2. All the content item fields are saved to an InnoDB table.
  3. All textual fields are STACKED together and saved in a single field inside a MyISAM table which also include additional ID field with reference to the original content item.

When the user perform a textual based search, I'll query against the MyISAM table using a Full-Text search query which will give me references to all the matching records in my InnoDB table.

The bottom line – Is that considered to be a good solution performance-wise and for improving the searching capabilities of my CMS, or should I stick with the good old LIKE operator and query using only the InnoDB table?

Best Answer

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.

Related Question