Mysql – Syncronize thesql databases between local and hosted servers automatically

Schedule a job to backup one database and apply the backup script to another, that way you never need to worry about the synchronization as it is done automatically on a schedule

The difference is the CHARACTER SET of the MEASURE_CODE column.

In staging it is latin1, in production it is utf8.

If character set of columns in the join (w.MEASURE_CODE = i.measurement_id) doesn't match, the server converts one of the columns to a different character set. This is done row-by-row and prevents using the index.

Here is a simple set up to demonstrate the problem.

CREATE TABLE `Test1` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Code1` varchar(10) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `IX_Code1` (`Code1`)
) ENGINE=InnoDB AUTO_INCREMENT=128 DEFAULT CHARSET=latin1

CREATE TABLE `Test2Latin` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Code2` varchar(10) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `IX_Code2` (`Code2`)
) ENGINE=InnoDB AUTO_INCREMENT=512 DEFAULT CHARSET=latin1

CREATE TABLE `Test2Utf` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Code2` varchar(10) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `IX_Code2` (`Code2`)
) ENGINE=InnoDB AUTO_INCREMENT=512 DEFAULT CHARSET=latin1

When character set matches:

EXPLAIN
SELECT Test1.Code1, Test2Latin.Code2
FROM
    Test1
    INNER JOIN Test2Latin ON Test2Latin.Code2 = Test1.Code1
;

+----+-------------+------------+-------+---------------+----------+---------+----------------+------+-------------+
| id | select_type |   table    | type  | possible_keys |   key    | key_len |      ref       | rows |    Extra    |
+----+-------------+------------+-------+---------------+----------+---------+----------------+------+-------------+
|  1 | SIMPLE      | Test1      | index | IX_Code1      | IX_Code1 |      12 | \N             |   64 | Using index |
|  1 | SIMPLE      | Test2Latin | ref   | IX_Code2      | IX_Code2 |      12 | db.Test1.Code1 |    1 | Using index |
+----+-------------+------------+-------+---------------+----------+---------+----------------+------+-------------+

When character set doesn't match:

EXPLAIN
SELECT Test1.Code1, Test2Utf.Code2
FROM
    Test1
    INNER JOIN Test2Utf ON Test2Utf.Code2 = Test1.Code1
;

+----+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+
| id | select_type |  table   | type  | possible_keys |   key    | key_len | ref  | rows |          Extra           |
+----+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | Test1    | index | \N            | IX_Code1 |      12 | \N   |   64 | Using index              |
|  1 | SIMPLE      | Test2Utf | ref   | IX_Code2      | IX_Code2 |      32 | func |    1 | Using where; Using index |
+----+-------------+----------+-------+---------------+----------+---------+------+------+--------------------------+

You can see the difference in possible_keys and ref columns. Note, that ref is actual column in the first variant (db.Test1.Code1) and func in the second variant. I believe this func is implicit conversion from latin1 to utf8.

This is tested on:

SELECT VERSION()
5.6.19-log