Mysql – How to sync two MySQL high load tables with 10M rows

OPTIMIZE rebuilds the table. This (for InnoDB) squeezes out some of the fragmentation and wasted space. This is unlikely to make a noticeable difference in any query.

Also, OPTIMIZE does an ANALYZE. This has a chance of changing the statistics, thereby leading to a different (better or worse) EXPLAIN plan.

Since ANALYZE is much faster (on InnoDB) than OPTIMIZE, just do the ANALYZE.

Various non-ANALYZE actions cause an ANALYZE to be done.

ANALYZE randomly probes the BTrees, gathering stats. Sometimes the resulting stats are poor. There is effectively no way to prevent this from happening. Several partial hacks have been created over the years; 5.6.7 gets close to eliminating this problem with ANALYZE. Here's one of them: http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_stats_persistent_sample_pages

First, I would try an index on (n_file, n_surn, n_type, n_id, n_surname) for this query. You may have to change the GROUP BY clause for the index to be used effectively by all versions of the optimizer:

GROUP BY n_file, n_surn COLLATE 'utf8_unicode_ci'

Second, why the derived table, the group by and the join? I think the query is equivalent to the much simpler:

SELECT SQL_CACHE 
    n2.n_surn, n1.n_surname, n1.n_id
FROM wt_name n1 
WHERE n_file = 4 
  AND n_surn NOT IN ('', '@N.N.')
  AND n_type <> '_MARNM' ;