5

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?

  • Will it make troubles for you that MyISAM is not transactional and so the data might desynchronize because of rollbacks etc? (it might mean returning wrong results in some cases, not finding records which exist and similar weird stuff) – jkavalik Jan 6 '16 at 20:55
3
+50

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.

3

Lets start with comparing between MyISAM and InnoDB:

Overview

MyISAM:

  • Faster than InnoDB since the structure is simpler and take less resources.
  • It support full text indexing better than InnoDB.
  • Good for read-intensive table.
  • Simplicity

InnoDB:

  • Data integrity and foreign key constraints.
  • InnoDB is more resistant to table corruption than MyISAM.

  • Support for large buffer pool for both data and indexes.

  • Row-level locking.

Disadvantages or limitations

MyISAM

  • Poor data integrity (No foreign keys and cascading deletes/updates)
  • Poor crash recovery.
  • Table locking
  • Row limit of 4,284,867,296 rows (232)
  • Maximum of 64 indexes per row

InnoDB:

  • No full text indexing (Below-5.6 mysql version)
  • Increased complexity.
  • Slower performance.

When should I use MySIAM? If the application is semple and need ot be fast. However, it should not be used when data integrity is a priority.

When should I use InnoDB? InnoDB is the best option if you need to create a reliable data-driven web application.

Compare between MyISAM and InnoDB FULLTEXT indexes and LIKE queries for full text search you can read the following example.

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

A: yes usually it done by full text work around for InnoDB

InnoDB full text search workaround:

You might be able to do some kind of data sync using triggers (if your version of mysql supports them). They allow you to run small snippets of SQL at certain points such as after data is inserted into or deleted from a table.

create trigger TRIGGER_NAME after insert on INNODB_TABLE
insert into MYISAM_TABLE select * from INNODB_TABLE
where id = last_insert_id();

Whenever data is inserted into the INNODB table, the same data is automatically inserted into the MYISAM table.

References:

Additional link in-case you want to read upgrade to MySQL 5.6: https://www.percona.com/blog/2013/07/31/innodb-full-text-search-in-mysql-5-6-part-3/

  • May benchmarks are showing InnoDB to be as fast or faster than MyISAM, especially when there are multiple threads running. MyISAM is not limited to 2^32 rows; that limit comes from a default that was changed a decade ago. It is now a myth that won't die. – Rick James Jan 10 '16 at 17:49
  • @RickJames MySAM limit still exist even in MySQL 5.7.x reference MySQL official website dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html – Ahmad Abuhasna Jan 11 '16 at 8:25
  • What I see on that page is (2^32)^2; that is, 2^64. It derives from how long the 'pointer' is. And that defaults to (I think) 6 bytes, but can be made larger or smaller. 4B rows implies 4-byte pointer, which was the default long, long ago. – Rick James Jan 11 '16 at 17:25
  • More differences can be found in here. – Rick James Jan 11 '16 at 17:26
  • @AhmadAbuhasna Thank you so much for this great answer! Unfortunately I can only accept one answer, but I've up-voted your answers on other questions to make sure you also get the credit you deserve :) Thanks again – Alon Eitan Jan 13 '16 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.