I am a stranger in a strange land. I am a SQL Server DBA and developer wandering, lost, in the world of MySQL. Fundamentally, I know that a database is a database. Both MySQL and SQL Server are built on the same ANSI standards. However, as I started working with the MySQL databases of a couple websites, I ran into unexpected limitations in MySQL.
SQL Server has one database engine.
MySQL has, as of version 5.6, ten. Ten database engines. Each with different features. In-depth information about each can be found at http://dev.mysql.com/doc/refman/5.6/en/storage-engines.html.
The one primarily being used in the databases I’ve been working with is MyISAM. It’s a fast engine, designed for databases with a lot of read activity. The drawback to this, for me, is that it lacks both support for foreign keys and transactions.
I discovered this when I was looking in a database, at a table of resource articles. There was also a table of resource categories. I was trying to find the foreign key between the two tables. I even exported the structure out of phpMyAdmin, imported into Toad for MySQL, and created an ERD. There were no foreign keys in the database. After doing some research, I found out that MyISAM simply does not support foreign key constraints.
While writing some code for this database, I came to another realization: the MyISAM engine doesn’t support transactions. I can’t wrap a SQL statement in “BEGIN TRAN…COMMIT TRAN”. You can lock tables, however.
As of MySQL 5.5, the default engine type became InnoDB. This engine supports both foreign keys in tables and transactions. These tables also require more space on disk, and updates require more memory. So, there are trade-offs.
One other thing to note is that within a single database, tables can be of different engine types. If it makes sense for some of your tables to be formatted as MyISAM for speed and others to be InnoDB for transactional consistency, you can design the database this way. This flexibility is a really nice option.
Another lesson learned!
Jes Borland is a Premier Field Engineer - SQL Server for Microsoft, where she is a trusted technical adviser, deep technical support, and teacher for her customers. Her experience as a DBA, consultant, and 5-time Data Platform MVP allow her to help the business and the IT teams reach their goals. She has worked with SQL Server as a developer, report writer, DBA, and consultant. Her favorite topics are administration, automation, and performance. She frequently presents at user groups, SQL Saturdays, and other community events. She is also an avid runner and chef.
