Long story short, we cannot use auto increment with MyISAM. Anyway, the replication works way better with InnoDB.
Finding all MyISAM tables using AUTO_INCREMENT
To do so, we will use the marvellous database information_schema which contains all sorts of useful information. The column AUTO_INCREMENT contains the value of the next PRIMARY KEY to be inserted. If the table has no AUTO_INCREMENT, it will be NULL.
Converting MyISAM to InnoDB
You can find plenty of literature on MyISAM vs InnoDB, but our main concern was FULLTEXT indices. InnoDB supports FULLTEXT only as of MySQL 5.6 and Percona 5.6 is still in alpha.Percona does not support MyISAM + AUTO_INCREMENT, but it does support MyISAM, so we can create a separate table to hold all the indexed data and join on this for a FULLTEXT search.
We have a pretty big codebase, adding a join for searches is an acceptable task, but removing columns and changing all SELECTs, INSERTs, UPDATEs, DELETEs, etc. was not.
The solution was to add triggers that would mirror columns that needed to be indexed from the original table. This way, you can deal with your original table as you used to do and only need to rewrite the SELECTs that are using FULLTEXT.
For those interested, I also made a little PHP script that generates the above SQL
And the new query will be like:
This duplicates the data, but it works. Don’t forget to drop the old FULLTEXT indices and convert the table to InnoDB.
Final thoughts
The triggers where written using temporary variables because it seems not to work inline with Percona, but I haven’t search this thoroughly. If you are not using Percona, you can safely drop these and use NEW.id directly.
- The original table is a lot smaller
- Smaller and fewer indices means faster INSERT/UPDATE/DELETE.
- Now supports foreign keys.
