Dérico Filho

Hi,

eZ Publish is a Content Management System and Content Management Framework translated into 26 languagues and their variations coded in PHP and it stores its information on a Relational Database Management System, such MySQL and PostgreSQL.

I would like write a bit more on MySQL and eZ Publish integration about a very specific yet important point: tables storage engines.

Among many there are two that are broadly used: MyISAM and InnoDB. Both are great and each has its own advantages and disadvantages.

MyISAM engine is the MySQL version of the old IBM’s ISAM engine used in its early versions. It was designed to be fast and it is used as an engine of choice up to MySQL 5. InnoDB engine on the other hand was created by Innobase that was buyed by Oracle. It has been replacing MyISAM as engine of choice from MySQL 5 up to the latest.

It is possible to summarise the differences between them in these points:

- InnoDB has support for transaction queries. It means that eZ Publish may access the database, change its data and store (or discard) the modifications all at once in the end of process. MyISAM tables simply cannot operate like this. It is particulary importante in routines that consistency is pretty critical. All data changed is logged apart.

- When an InnoDB tables crashes, it recovers replaying the log. Therefore, the time to fix the table is always very short because it does not have to search for corrupted data. MyISAM on the other hand, has to scan the table each time it crashes, the larger ammount of data the longe it will take to check and recover it.

- MyISAM is quicker than InnoDB in many situations, because it does not have to store and keep log of changes while another command is processing reading. MyISAM either reads a table or writes to table, and these operations are not concurrent. However if you have a eZ Published site with a lot of changes, all of them happening together, you will problems with performance. Each time a MyISAM table is changed, the table cache is discarded - and it cannot be read until the write is done.

- MyISAM supports FULL TEXT index. When a Text field is created and indexed in MySQL, just a tiny part of it is stored in the index, and therefore it may return results lacking registers that should match a filter rule. FULL TEXT index prevents that, it creates a hash of all the words in the index so it returns consistently the expected result.

Thus what is it the best choice when developing with an eZ Publish object? The answer is quite easy and straightforward. The rule of thumb is: if your eZ Publish site reads much more than it needs to be updated, use MyISAM engine. Otherwise use InnoDB engine. Even if the proportion is 50% / 50% keep InnoDB as choice, on eventual crash it will recover quicker and very consistently.

This article is offered by WebDeal Hosting.