mysql not using index on production query, yet does on staging and local

I have this query:

select m.id meal_id, 
    SUM(FORMAT( ((((fi.calories / fi.weight_in_grams) * w.GRAMS) * i.amount) / r.serves) * mr.servings , 3)) calories 
from meals m 
    join meal_ethnic_types met 
        on met.meal_id = m.id 
    join meal_protein_types mpt 
        on mpt.meal_id = m.id 
    join meal_recipes mr 
        on mr.meal_id = m.id 
    join recipes r 
        on r.id = mr.recipe_id 
    join ingredients i 
        on i.recipe_id = r.id 
    join food_items fi 
        on fi.id = i.food_item_id 
    join WEIGHT w 
        on w.NDB_NUM = fi.usda_code and w.MEASURE_CODE = i.measurement_id 
    join meal_meal_types mmt 
        on m.id = mmt.meal_id 
where m.is_active = 1 
    and (m.company_id = 44 or m.company_id is null) 
    and (m.location_id = 167 or m.location_id is null) 
    and (m.member_id = 2698 or m.member_id is null) 
    and (m.user_id = 3151 or m.user_id is null) 
    and mmt.meal_type_id = 2 
    and calories <= 343 
    and m.cpf_id = 2 
    and (mpt.protein_type_id in (2, 4)) 
    and (met.ethnic_type_id in (1, 7, 5, 4, 9, 6)) 
group by m.id 
order by rand() 
limit 0, 50;

So my issue is that on my development box, and on the RDS staging database the system query uses an index on the WEIGHT join. The explain returns this:

```
1 SIMPLE m ref PRIMARY,cpf_id,company_id,location_id,member_id,user_id,is_active cpf_id 4 const 273 Using where; Using temporary; Using filesort
1 SIMPLE mpt ref meal_id,protein_type_id meal_id 4 staging_myplan.m.id 1 Using index condition; Using where
1 SIMPLE mmt ref meal_id,meal_type_id meal_id 4 staging_myplan.m.id 1 Using index condition; Using where
1 SIMPLE mr ref meal_id,recipe_id meal_id 4 staging_myplan.m.id 1 Using index condition
1 SIMPLE r eq_ref PRIMARY PRIMARY 4 staging_myplan.mr.recipe_id 1 Using where
1 SIMPLE met ref meal_id,ethnic_type_id meal_id 4 staging_myplan.m.id 1 Using index condition; Using where
1 SIMPLE i re

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

StackExchange Database Administrators Q#122002, answer score: 4