Different key_len results in same database (different enviroments)

Question

I have a production database in AWS, but same database in localhost is much more faster(25s VS 7s), doing some research I've found one discrepancy in same SQL:

EXPLAIN extended 
SELECT * 
FROM pipeline p 
JOIN invoice i ON p.invoice_id = i.id 
WHERE i.whenCreated BETWEEN "2017-01-01 00:00:00" AND "2017-01-31 00:00:00" 

In AWS =====> key_len = 8 and Extra = Using where.
In localhost ==> key_len = 5 and Extra = Using index condition.

I ran in both sites before:

OPTIMIZE TABLE invoice;

Probably it is a config problem, but I'm lost.

---

More info:
Mysql version in AWS: 5.5.46
Mysql version in Localhost: 5.6.24

explain in AWS:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
         type: range
possible_keys: PRIMARY,IDX_5FD82ED84DD79520
          key: IDX_5FD82ED84DD79520
      key_len: 8
          ref: NULL
         rows: 847
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: ref
possible_keys: UNIQ_848ABB8F2989F1FD
          key: UNIQ_848ABB8F2989F1FD
      key_len: 5
          ref: ontrocrm.i.id
         rows: 1
     filtered: 100.00
        Extra: Using where

explain in localhost:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: i
         type: range
possible_keys: PRIMARY,IDX_5FD82ED84DD79520
          key: IDX_5FD82ED84DD79520
      key_len: 5
          ref: NULL
         rows: 847
     filtered: 100.00
        Extra: Using index condition
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
         type: ref
possible_keys: UNIQ_848ABB8F2989F1FD
          key: UNIQ_848ABB8F2989F1FD
      key_len: 5
          ref: ontrocrm.i.id
         rows: 1
     filtered: 100.00
        Extra: NULL

create table is the same in both, one is a backup from the other:

CREATE TABLE `invoice` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(11) NOT NULL,
  `whenCreated` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_number` (`number`),
  KEY `IDX_5FD82ED84DD79520` (`whenCreated`)
)

Answer 1

Explaining key_len diff

You discovered the change that happened with DATETIME and TIMESTAMP in 5.6.4. Before, they were 8 bytes (packed decimal) and 4 bytes (just an INT), respectively. Afterwards they are both 5+ bytes, including the ability to include up to 6 decimal places microseconds.

"Using where" does not mean a lot. "Using index condition" refers to "ICP" or "Index Condition Pushdown", which is an Optimization that usually speeds up queries that do not have the an optimal composite index. [At this point, I need to see SHOW CREATE TABLE; I will guess that you are using InnoDB.] Instead of passing each row from the Engine (InnoDB) back 'up' the 'handler' for further analysis, the 'condition' (WHERE ...) is pushed 'down' to the Engine, where it can be more quickly processed.

In your case, you get 3x-4x speedup; nice. (My rule of thumb: 2x.)

Other

Are you really doing SELECT *? If you do not need all the columns, should spell out the desired columns; you may be missing some optimization potential.

Is UNIQ_848ABB8F2989F1FD "unique", or is it misnamed?

If invoice.number is UNIQUE, why not use it as the PRIMARY KEY and completely get rid of id? This would speed up some operations (though probably not the current SELECT).

You probably noticed that OPTIMIZE TABLE had little or no impact?

Instead of WHERE i.whenCreated BETWEEN "2017-01-01 00:00:00" AND "2017-01-31 00:00:00", which involves an extra second and requires computing the end-of-month, I like to say:

WHERE i.whenCreated >= "2017-01-01"
  AND i.whenCreated  < "2017-01-01" + INTERVAL 1 MONTH