Re: query hangs after migration from mysql 5.5 to mysql 5.6

[Rick James]

Please use the JOIN...ON syntax, as in:

    select  la.`provider` as `LTV_AGGREGATE_provider`,
            la.`DATA_INIZIO` as `LTV_AGGREGATE_DATA_INIZIO`,
            glp.`prodotto_liv_2` as `GES_L_PRODOTTO_prodotto_liv_2`,
            glp.`prodotto_liv_1` as `GES_L_PRODOTTO_prodotto_liv_1`
        FROM  `LTV_AGGREGATE` AS la
        JOIN  `GES_L_PRODOTTO` AS glp ON la.`product_id` = glp.`id_prodotto`
        WHERE  (la.`DATA_INIZIO` >= '2015-12-09'
           and  la.`DATA_INIZIO` <= '2015-12-16' 
               )
          and  (((la.`product_id` not in ('EI_ADIG_ENI1W', 'EI_CALP_ENI1W',
                                            'EI_CENT_ENI1W', 'EI_CSAL_ENI1W', 'EI_ESP_ENI1W', 'EI_GMAN_ENI1W',
                                            'EI_GMON_ENI1W', 'EI_GREG_ENI1W', 'EI_MPAD_ENI1W', 'EI_NFER_ENI1W',
                                            'EI_NSAR_ENI1W', 'EI_NVEN_ENI1W', 'EI_PIC_ENI1W', 'EI_PPAV_ENI1W',
                                            'EI_REP_ENI1W', 'EI_SCAN_ENI1W', 'EI_TIRR_ENI1W', 'EI_TREN_ENI1W',
                                            'EI_TTRE_ENI1W')) ) 
               )
          and  glp.`prodotto_liv_2` in ('BUNDLE', 'ESPRESSO', 'LIMES WEB',
                    'MANCANTE', 'REPUBBLICA+', 'REPUBBLICAMOBILE' 
                              )
        order by  la.`DATA_INIZIO`
        limit  200;

decimal(63,0) -- Really that big?? That's 28 bytes for each such field. INT UNSIGNED would take only 4 bytes and allow positive numbers up to 4 billion. BIGINT UNSIGNED takes 8 bytes and can hold a number that you will never reach.

`expire_date` varchar(10) CHARACTER SET utf8 -- not a DATE?

The lack of an explicit PRIMARY KEY in `LTV_AGGREGATE` may be hurting.

Did you want 8 days? Or just one week?

There is a small chance that running ANALYZE TABLE on each table will recalculate the statistics in a way that makes it start with the other table first. (STRAIGHT_JOIN is an ugly way to force such.)

I do not know if this is a regression in 5.6 that may be fixed in 5.7.

This index _might_ trick the optimizer into using the la table first:
INDEX(product_id, DATA_INIZIO)