MySQL: MyISAM versus InnoDB

Last week I saw an interesting article by Craig Buckler on one of the Sitepoint blogs: "Top 10 MySQL Mistakes Made By PHP Developers". I was already aware of a few of them (for example sanitizing user input, avoiding * in SQL SELECT statements) - and I've been using the PDO (PHP Data Objects) interface for database access pretty much since I started with PHP, which made recently switching an application from an SQLite to MySQL backend almost trivially straightforward.

However the first mistake listed was something I hadn't previously come across: "#1: Using MyISAM rather than InnoDB" - which prompted me to do a bit of background research.

MyISAM and InnoDB are two of several database "storage engines" offered by MySQL (a storage engine is the underlying software that implements the low-level database operations to create, read, update and delete data - you can list all the supported engines for a MySQL installation using SHOW ENGINES). Surprisingly (to me at least) the engine type is specified at table- rather than database-level, and can be done when first creating the table:

CREATE TABLE myTable (
...
) ENGINE = MyISAM | InnoDB;

If the engine isn't explicitly specified then MySQL uses the default (MyISAM for MySQL versions before 5.5.5, InnoDB since).

The key differences between the two are:

• InnoDB supports transactions, but MyISAM doesn't. Transactions enable several SQL statements to be combined and then accepted all at the same time, essentially treating them as a single query. This helps maintain database integrity - the database state is only changed if all statements in the transaction are executed successfully.
• InnoDB uses row-level locking, whereas MyISAM uses table-level locking. This means that InnoDB offers better concurrency for tables with a high volume of updates at the same time as reads. (See another Sitepoint blog post for a real-world example of where this can make a difference: "Free Performance With MySQL Table Types".)
• InnoDB supports foreign-key constraints, which again can help maintain database integrity - operations on one table will fail if some condition - say a matching row in another table - is not met.
  
• MyISAM supports full-text indexing; InnoDB doesn't.
  

From what I understand, in the past MyISAM's principal advantage over InnoDB has been its speed, operating significantly faster especially for applications where there is a high volume of reads compared with updates. However more recent implementations of InnoDB seemed to have closed the performance gap enough that this is no longer an issue (which is presumably why MySQL now uses it by default).

New databases created using the latest MySQL will automatically use InnoDB, but for older databases you can find out what engine your tables are using (along with other information) with:

SHOW TABLE STATUS;

The storage engine can be changed using the ALTER TABLE command, e.g.

ALTER TABLE myTable ENGINE = InnoDB;

Clearly it's possible to mix tables with different storage engines, but presumably great care needs to be taken to understand the implications (for example, what happens if a transaction that involves InnoDB and MyISAM tables fails?). There's more about storage engines in the MySQL reference manual.

Returning to the original posting, Craig's rather blunt statement about favouring InnoDB over MyISAM seemed to generate plenty of controversy amongst the commenters, but from what I've read InnoDB does look to be the better choice even if you're not currently taking advantage of transactions or foreign key constraints (which I'm not). I think there's also a wider point (as hinted at in his mistake #5: "Favouring PHP over SQL"): to use something effectively, you need to take time to understand it and the context in which it's operating. So this post is my contribution to the cause - hope it helps.

nXhtml: an Emacs mode for web development

I'm a fan of Emacs but I've often been frustrated when using it to edit PHP files. Although the default Emacs major mode for PHP generally did an okay job with "pure" PHP files (i.e. those only containing PHP code), I would still find myself occasionally struggling against Emacs automatically insisting on mis-formatting my code. And when the files contained a mixture of PHP and HTML (commonplace in web applications), the auto-indentation could get quite deranged.

Programming is difficult enough sometimes without having your editor working against you as well - in fact for a while on Windows I even switched to using Notepad++ (which solved the problem by not having any auto-indentation at all). The problem with Emacs is two-fold: first, I felt I needed a better PHP mode than the default; and second, it needed to be able to deal with "mixed modes" (when two major modes - in this case PHP and HTML - are used in a single file).

Initially php-mode looked promising, working really nicely with pure PHP - but unfortunately the solution for handling embedded HTML (to toggle manually between PHP and HTML modes as required using the M-x html-mode/M-x php-mode sequences) didn't feel that practical to me.

However the documentation for php-mode also suggested nXhtml, which describes itself as "an Emacs mode for web development". nXhtml includes a version of php-mode along with support for mixed modes, and it does a great job of handling PHP both on its own and with embedded HTML. The automatic formatting is nicely behaved so it works for rather than against me, and the syntax highlighting also distinguishes PHP code sections from those containing HTML. In addition there are some other useful-looking features (such as tag completion) that I haven't really explored yet.

However it looks like nXhtml was the solution that I was looking for all along. It's a straightforward install: on Linux, download the nXhtml.zip file, extract the contents to e.g. $HOME/nxhtml/, then edit $HOME/.emacs to include:

(load "your-path-to/nxhtml/autostart.el")

For Windows it's even easier, you can download and install a version of Emacs bundled with nXhtml (however note that I couldn't get nXhtml to work with an existing Xemacs 21.4 Windows install).

Although I haven't worked with it extensively yet, so far I've found nXhtml a great improvement for working with my web application code and I'd recommend anyone interested in using Emacs for PHP development to also give it a try.

PHPNW10

Last weekend I was in Manchester at PHPNW10, the annual conference for the north-west of England PHP community. It was a fairly last minute decision to attend but looking at the conference programme persuaded me that I'd be rewarded with lots of good material, and I wasn't wrong - it's going to take me a while to process the volume of quality information from the talks I attended.

With that in mind I won't try to do more here than just summarise the sessions I was in on Saturday, kicking off with the keynote talk by Lorna Mitchell on professional development ("Teach a man to fish"). While at first this might have seemed a little out of place amidst all the technical content, it was entirely appropriate given that many people (including me) come to these events to learn. Much of the focus was on benchmarking and improving the skills of your team as a whole (making the point that skills gaps only exist in the context of knowing what skills are actually needed), but emphasis was also placed on individuals taking the responsibility for their own professional development. There was some solid practical advice for making the business case for training to managers, and suggested alternatives to formal training courses (for example, allowing developers "study days" on company time) if you have "a training budget of zero." And if you're working on your own then the suggestion to make your own "team" sounds like a good one to try.

After the keynote the sessions split into three parallel "tracks" for the remainder of the day, and in general I chose to focus on those talks on basic development tools and methodology (with a plan to catch the talks I missed about geolocation, REST interfaces and so on later online).

Things were off to a great start with Robert Mortimer's talk "Let your toolchain set you free", which dealt with choosing and using appropriate tools for setting up your development environment (Linux-Apache-MySQL-PHP i.e. LAMP), source code control (subversion), performing code validation (PHP_codesniffer), enforcing coding styles (PHP_Beautifier), generating documentation (docblockgen), unit testing, debugging (Xdebug) and developing using an IDE (NetBeans). Accompanied by live demos and full of little tricks to automate things, it was a dizzying talk.

I followed that with Ian Barber's talk "Debugging - Rules and Tools". At the beginning Ian confessed to being something of a debugging nerd and described the satisfaction he felt when tracking down bugs in an application - something I identified with from my former life in software maintenance. His presentation was structured around the nine rules outlined in David J. Agan's book "Debugging", giving practical advice and suggesting useful tools and resources for each rule. As with the earlier talk there was an almost overwhelming number of tips and tricks (a small number of examples: Jmeter for load-testing; Selenium for simulating user interactions; MySQL Proxy to isolate your database; Tamperdata to modify http requests...). I also learned a new word, "heisenbug", to describe an intermittent fault.

After lunch and a chat with some fellow attendees I dropped into Marco Tabini's talk describing the development of the php|architect website ("The curious case of php|architect"). Marco focused on higher-level design decisions - and the reasoning behind them - that had been made at various stages in the site's evolution, and reminding the audience that real-world projects don't always follow a smooth path.

Marco was followed by "Developing Easily Deployable PHP Applications", given by John Mertic of SugarCRM. At the beginning John made the important point that you need to define the "support matrix" (i.e. the set of environments defined by combinations of operating system, web server, database and PHP version) for your application, as knowing this will inform your design and test options. He then went on to describe how this is managed for SugarCRM, raising a number of interesting points - for example, providing hooks and other mechanism to make customising, configuring and extending the application as easy and as maintainable as possible for the end user.

The last real talk was Harrie Verveer's "Database version control without pain", which aimed to address the issues with managing changes to database schema in sync with changes to your application code. The problem is that the mechanism used for patching PHP code as part of an update can't be used to update the database - in this case the patches are in the form of SQL code rather than code differences - and Harrie conceded that (contrary to the title) there isn't a magic bullet for painless database version control. Nevertheless he did a great job of outlining the pro's and con's of the various options, ranging from a simple patching strategy (probably the way to go for me at the moment) through to tools like Phing, Liquibase, Akrabat DB Schema Manager and Doctrine Migrations.

I was feeling a little brain dead by then, and as entertaining as it was much of the substance of the closing "framework shoot-out" was lost on me (particularly as I'm not a framework user). But no matter - because I'd had a great day at a well-run conference, met some great fellow software developers, and been exposed to far more useful information than I could have hoped to find elsewhere on my own. I know it's going to take me some time to follow up on the various tools and techniques - in the meantime thanks to all the organisers and speakers for making my first PHPNW conference such a memorable and enjoyable one.