MySQL Forums
Forum List  »  Performance

Re: query hangs after migration from mysql 5.5 to mysql 5.6
Posted by: Rick James
Date: December 20, 2015 07:31PM

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

    select  la.`provider` as `LTV_AGGREGATE_provider`,
            glp.`prodotto_liv_2` as `GES_L_PRODOTTO_prodotto_liv_2`,
            glp.`prodotto_liv_1` as `GES_L_PRODOTTO_prodotto_liv_1`
        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',
        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)

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.