InnoDB Support in processwire

[mrjasongorman]

I'm sure there may have been a topic talking about this previously, but ironically i couldn't find it when using the search box in the forum.

Recently i've been using Processwire as more of a CMF really, which i've found it really easy to mould and shape to the application needs.

The one thing that concerns me though is the fact it only uses the MyISAM DB Engine.

These days InnoDB seems to be the standard, and seems to fit better with the way PW stores things, for example Foreign keys help the DB understand links of data across different tables (PW stores each field in a separate table). This would also benefit greatly from transactions, making sure that every SQL operation needed to store an item and its field data would either all be successful, or wouldn't happen at all. This gives guarantees that no data went missing during the save due to DB issues and crashes.

Row locking (InnoDB) rather than Table Locking (MyISAM) is a huge advantage, take the situation where i want to save an item that has a common field, like field_body, if i understand it correctly, then the field_body table would be locked on every read as MyISAM uses table locking, so other queries to read or write would have to wait until the table lock is released. InnoDB on the other hand only locks the row in question so other operations can happen to the table at the same time.

Another feature that goes along with the ACID compliance is The commit log, InnoDB keeps a commit log of transactions, so in the event of a crash it can recover to a consistent state. MYISAM however does not so it can be hard to know what state the data should be in, when recovering.

I think i read previously that Ryan chose MyISAM at the time for it's Full text search capabilities, which InnoDB only introduced in MySQL 5.6, but i think unless full text search is the key part to the internal PW system, then is it really that necessary a trade off?

I would rather the reliability of InnoDB storing data than to have full text search, for that kind of functionality i would use a separate system built for specifically with this feature, such as ElasticSearch.

So i was wondering, will InnoDB become the default DB engine for PW ??

[horst]

I'm on mobile and not answering directly to your question, but want mention that instead of the forums search box, you should use google with site:processwire.com/talk as additional search tag.

[LostKobrakai]

MySQL 5.6 is still far far away from broad adoption especially on shared hostings and fulltext search is essential to processwire's selector engine (e.g every search related to text in any form). Under this circumstances will InnoDB not become the default anytime soon. On the other hand is InnoDB already fully supported, but without using the fancyness of it in core functionality. You need to keep in mind that not everybody is using processwire as cmf (I do so as well), but it's still a simple cms like drumlapress to many people.

[Beluga]

http://processwire.com/blog/posts/happy-new-year-heres-a-roadmap-for-processwire-in-2016/#what-else-is-coming-for-processwire-3.x-in-2016

More MySQL options. ProcessWire 3.x will provide more direct install-time options for selection of database engine and character set. We will likely be defaulting to use InnoDB (rather than MyISAM) when the conditions and environment support it.

[mrjasongorman]

Thanks for replying guys, i thought the reason for holding back might be something to do with the core. Looking forward to seeing the 3.x features!

[mrjasongorman]

Just curious how does the selector engine generate it's SQL request?

[DaveP]

https://github.com/ryancramerdesign/ProcessWire/tree/master/wire/core Files starting DatabaseQuery...