MySQL engines, constraints & keys

I wanted to see how I could improve the performance of a MySQL database with mixed table engines by converting all the MyISAM tables to InnoDB, as well as make the huge DB responsive while backing up by using mysqldump with the --single-transaction option. I used the following PHP script (I know, spare me):

After looking at the table status following the script run, there was one table which was still set to use MyISAM. In the mysql shell, I tried manually altering the one table to use InnoDB, and then needed a bunch of additional commands to smooth out the DDL problems before MySQL was happy.

Of course, I edited out a whole mess of trial and error here. The issue was that MySQL wants any column you mark as auto_increment to have its own key, it doesn’t need to be primary; but it can’t be an aggregate which is what it had originally. My solution was to simply add a non-primary key to that column, while keeping the aggregate key. Using a primary key was out of the question since there are duplicate values in the aid column.

Turns out, all I really needed to do was add the individual index, then alter the table engine. Oh well, it was educational. :-)

MySQL engines, constraints & keys is original content from Devolve.

Author: Charlie Herron

Denizen of Portland, Maine; tech jack; lover / hater / whatever; philosophical dabbler.