Mysql – Changing storage engine to InnoDb from MyIsam is dropping records from table

A few notes and suggestions:

I was asking for the innodb% params to verify you're not running with silly defaults. You're not, and setup looks OK.

The inbound_link_changes table doesn't have a PRIMARY KEY. It should, as with all InnoDB tables, since these use a clustered index, which is the PRIMARY KEY. Yours does not have one, so it's clustered by internal row id, which is not optimal. Is there any UNIQUE combination on this table?

If not, I recommend making up a surrogate key, via AUTO_INCREMENT.

This could actually affect your query, since secondary keys are affected by clustering index (explanation is outside scope of this answer).

In addition, consider setting up an index on (link_id, change_type). Since you're giving away change_type as constant, this will be taken into account when looking up the index.

My recommendations are to start as follows:

1. Go for the latest version of the MySQL Server that you can get, there have been some pretty impressive work done on 5.6 although the production ready version is 5.5

2. Go for InnoDB - comes as the default for the higher MySQL versions

3. Configure InnoDB as follows:

   • file_per_table - creates an data file for each table instead of putting all tables in a single data files
   • innodb_buffer_pool_size - 80% of available RAM (if a dedicated machine) so that as much of your database is in memory

4. Table column characteristics:

   • use the smallest column size needed for the data
   • use numeric foreign keys
   • index the columns used in the searches, and setup the indexes to cover the most common searches, for example if you search on columns 4, then 6 alot create a multi-column index with column 4 first followed by 6

Remember these are just starting points you can fine tune specifics as you move along, but before you fine tune, measure tune then measure again