1

What are the implications if I create all the Craft database tables on the InnoDB storage engine? Will there be any performance problem?

2 Answers 2

1

Actually, all the tables, but one use InnoDB storage engine. The one that uses myisam engine is table "searchindex".

When you check for index type:

mysql> show index from searchindex where Index_type="FULLTEXT" \G

you get that the "searchindex" table indeed uses FULLTEXT.

I would say that Craft uses some specific fulltext searches specific to myisam engine, therefore not using innodb.

You can change the engine of the "searchindex" table (and see what brakes) with:

mysql> alter table searchindex engine="InnoDB";

and revert with:

alter table searchindex engine="MyISAM";
1

Craft CMS 4.1 now creates the searchindex table as InnoDB by default, because the minimum MySQL version Craft CMS 4 supports will always ensure that full text searching support is available with InnoDB.

Craft CMS 3 still creates this table as MyISAM because the minimum MySQL version supported, doesn't have full text searching support, so there is a possibility that full text searching may not be available for older MySQL versions with InnoDB. If however you are running a MySQL version with full text support using InnoDB, you can go ahead and run an ALTER TABLE command in your project to change it or run it through a migration, so there's a record of the change in your migrations table.

There shouldn't be any major issues, there is still a unresolved MySQL bug with InnoDB and full text searching, which Craft CMS has a workaround for: https://github.com/craftcms/cms/commit/b39240a4b409b535cae7ea1acdf5880c0ef8cb2f in it's own codebase, as the bug remains unresolved as per the bug tracker: https://bugs.mysql.com/bug.php?id=78485

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.