4

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   ref food_item_id,recipe_id  recipe_id   4   staging_myplan.r.id 2   Using index condition
1   SIMPLE  fi  eq_ref  PRIMARY,usda_code   PRIMARY 8   staging_myplan.i.food_item_id   1   Using where
1   SIMPLE  w   ref ndbnum,MEASURE_CODE ndbnum  12  staging_myplan.fi.usda_code 1   Using index condition; Using where

Yet the same exact query on production RDS (both a instance install and on an RDS) Takes forever and returns this explain:

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   don_moreactive.m.id 1   Using where
1   SIMPLE  mmt ref meal_id,meal_type_id    meal_id 4   don_moreactive.m.id 1   Using where
1   SIMPLE  met ref meal_id,ethnic_type_id  meal_id 4   don_moreactive.m.id 1   Using where
1   SIMPLE  mr  ref meal_id,recipe_id   meal_id 4   don_moreactive.m.id 1   Using where
1   SIMPLE  r   eq_ref  PRIMARY PRIMARY 4   don_moreactive.mr.recipe_id 1   Using where
1   SIMPLE  i   ref food_item_id,recipe_id  recipe_id   4   don_moreactive.r.id 2   Using where
1   SIMPLE  fi  eq_ref  PRIMARY,usda_code   PRIMARY 8   don_moreactive.i.food_item_id   1   Using where
1   SIMPLE  w   ALL MEASURE_CODE    NULL    NULL    NULL    13162   Using where; Using join buffer

So as you can see the production databases are not using the index and are attempting to use where and the join buffer.

I've compared the keys and indexes on all database servers and they're identical.

Any clues?

Staging Table:

CREATE TABLE `WEIGHT` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `NDB_NUM` varchar(10) NOT NULL,
  `MEASURE_CODE` varchar(10) NOT NULL DEFAULT '',
  `GRAMS` decimal(11,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ndbnum` (`NDB_NUM`) USING BTREE,
  KEY `MEASURE_CODE` (`MEASURE_CODE`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12920 DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 1167360 kB; (`NDB_NUM`) REFER `FVG_NEW_NUTRITIO'

WEIGHT  0   PRIMARY 1   id  A   12774   NULL    NULL        BTREE       
WEIGHT  1   ndbnum  1   NDB_NUM A   12774   NULL    NULL        BTREE       
WEIGHT  1   MEASURE_CODE    1   MEASURE_CODE    A   3193    NULL    NULL        BTREE           

Production Table:

CREATE TABLE `WEIGHT` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `NDB_NUM` varchar(10) NOT NULL,
  `MEASURE_CODE` varchar(10) CHARACTER SET utf8 NOT NULL,
  `GRAMS` decimal(11,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ndbnum` (`NDB_NUM`) USING BTREE,
  KEY `MEASURE_CODE` (`MEASURE_CODE`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12920 DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 1167360 kB; (`NDB_NUM`) REFER `FVG_NEW_NUTRITIO'

WEIGHT  0   PRIMARY 1   id  A   13076   NULL    NULL        BTREE       
WEIGHT  1   ndbnum  1   NDB_NUM A   13076   NULL    NULL        BTREE       
WEIGHT  1   MEASURE_CODE    1   MEASURE_CODE    A   3269    NULL    NULL        BTREE       

STAGING: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on

PRODUCTION: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on

4

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
  • in most case it return error - collation mismatch, but more important - it same table on different server – a_vlad Nov 25 '15 at 7:39
  • @a_vlad, I added an example to illustrate my point. The query works even if character sets don't match, but it results in implicit conversion. – Vladimir Baranov Nov 25 '15 at 9:29
  • yes, You are right Vladimir. – a_vlad Nov 25 '15 at 9:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.