XML FeedsI 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 Schultz Borland is a Consultant with Brent Ozar PLF, and a Microsoft SQL Server MVP. She holds an AAS - Programmer/Analyst degree, and has worked with SQL Server since 2007, focusing on Reporting Services and day-to-day administration. She is an active member of PASS, Director of Communication for MADPASS, founder of Tech on Tap, and a LessThanDot.com blogger. She's a frequent presenter at user groups, SQL Saturdays, and other community events. She is also an avid runner and chef. 
So I know you've been tweeting about using php to talk to MySQL, but have you tried PowerShell? I know there is a .NET driver, but I haven't tried it with .NET.
I guess the Maria engine didn't make it into MySQL yet, looks like they forked that one into MariaDB 
I remember first using MySQL in the late 99-2000 time period. I couldn't tell you the first thing about which engine it was using, but I do remember that all I had for tools was a command line and telnet (not ssh, telnet) session. I think that helped me like phpMyADmin more given the alternative (although I also use the MySQL workbench tools on personal systems).
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.