2

I am executing a SELECT on 2 Hosts, "old db" running mysql 5.5.54 and "new db" running mariadb 5.5.56. Both the runtime and the EXPLAIN show a difference, with the mariadb being significantly slower. The data on "new db" is a very recent dump of "old db"

EXPLAIN on "old db":

explain SELECT 
    b.tariff as tariff, b.benefitType as benefitType, b.name as name,
    b.code as code, SUM(b.effQuantPckType) as `count`, 
    SUM(effCalcAmtVat) as `amount`, 
    SUM(effAmt) as `amountAL`, SUM(effAmt2) as `amountTL`  
FROM 
    benefitCombined AS b 
    LEFT OUTER JOIN invoice AS inv ON inv.id = b.invoice  
WHERE (invoicePart != 'aaaaaaaaaaaaaaaaaaaa' OR invoice != 'aaaaaaaaaaaaaaaaaaaa')
  AND executingDoctor = 'dqwjvgbskiaaaacsonmj'
  AND b.begin >= '2018-06-16 00:00:00' AND 
      b.begin <= '2018-07-16 23:59:57'
  AND 1=1 
GROUP BY 
    benefitType, code;

+----+--------------+---------------+--------+---------------+------+---------+-----------+--------+----------------------------------------------+
| id | select_type  | table         | type   | possible_keys | key  | key_len | ref       | rows   | Extra                                        |
+----+--------------+---------------+--------+---------------+------+---------+-----------+--------+----------------------------------------------+
|  1 | PRIMARY      | <derived2>    | ALL    | NULL          | NULL | NULL    | NULL      | 287344 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY      | invoiceBase   | eq_ref | id            | id   | 20      | b.invoice |      1 | Using index                                  |
|  2 | DERIVED      | benefitBase   | ALL    | NULL          | NULL | NULL    | NULL      | 285328 | Using where                                  |
|  3 | UNION        | benefitTarmed | ALL    | NULL          | NULL | NULL    | NULL      |      1 | Using where                                  |
| NULL | UNION RESULT | <union2,3>    | ALL    | NULL          | NULL | NULL    | NULL      |   NULL |                                              |
+----+--------------+---------------+--------+---------------+------+---------+-----------+--------+----------------------------------------------+
5 rows in set (3.34 sec)

SHOW CREATE on "old db":

show create table invoice \G;
*************************** 1. row ***************************
                View: invoice
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`c0515_dwh`@`%` SQL SECURITY DEFINER VIEW `invoice` AS (
select  `invoiceBase`.`id` AS `id`,`invoiceBase`.`created` AS `created`,
    `invoiceBase`.`begin` AS `begin`,`invoiceBase`.`end` AS `end`,
    `invoiceBase`.`formatType` AS `formatType`,`invoiceBase`.`invUID` AS `invUID`,
    `invoiceBase`.`traStat` AS `traStat`,`invoiceBase`.`invStat` AS `invStat`,
    `invoiceBase`.`patient` AS `patient`,`invoiceBase`.`provider` AS `provider`,
    `invoiceBase`.`biller` AS `biller`,`invoiceBase`.`debitor` AS `debitor`,
    `invoiceBase`.`name` AS `name`,`invoiceBase`.`amtDue` AS `amtDue`,
    `invoiceBase`.`law` AS `law`,`invoiceBase`.`reason` AS `reason`,
    `invoiceBase`.`amtTotal` AS `amtTotal`,`invoiceBase`.`amtPrepaid` AS `amtPrepaid`,
    `invoiceBase`.`amtPaid` AS `amtPaid`,`invoiceBase`.`amtPaidDirect` AS `amtPaidDirect`,
    `invoiceBase`.`amtFee` AS `amtFee`,`invoiceBase`.`splitFac` AS `splitFac`,
    `invoiceBase`.`invoiceType` AS `invoiceType`,`invoiceBase`.`invoiceOptimal` AS `invoiceOptimal`,
    `invoiceBase`.`patientCopy` AS `patientCopy`,`invoiceBase`.`diagnoses` AS `diagnoses`,
    `invoiceBase`.`customerNumber` AS `customerNumber`,`invoiceBase`.`traStatDate` AS `traStatDate`,
    `invoiceBase`.`invStatDate` AS `invStatDate`,`invoiceBase`.`oldInvoiceNr` AS `oldInvoiceNr`,
    `invoiceBase`.`newInvoiceNr` AS `newInvoiceNr`,`invoiceBase`.`mandator` AS `mandator`,
    `invoiceBase`.`account` AS `account`,`invoiceBase`.`cancReqFlag` AS `cancReqFlag`,
    `invoiceBase`.`cancReqDate` AS `cancReqDate`,`invoiceBase`.`claimNr` AS `claimNr`,
    `invoiceBase`.`claimReqFlag` AS `claimReqFlag`,`invoiceBase`.`claimReqDate` AS `claimReqDate`,
    `invoiceBase`.`depReqFlag` AS `depReqFlag`,`invoiceBase`.`depReqDate` AS `depReqDate`,
    `invoiceBase`.`canton` AS `canton`,`invoiceBase`.`amtObligations` AS `amtObligations`,
    `invoiceBase`.`amtTax` AS `amtTax`,`invoiceBase`.`remarks` AS `remarks`,
    `invoiceBase`.`cancRemarks` AS `cancRemarks`,`invoiceBase`.`claimRemarks` AS `claimRemarks`,
    `invoiceBase`.`depRemarks` AS `depRemarks`,`invoiceBase`.`p02InvNr` AS `p02InvNr`,
    `invoiceBase`.`p02InvDate` AS `p02InvDate`,`invoiceBase`.`invoiceDate` AS `invoiceDate`,
`invoiceBase`.`debMngmt` AS `debMngmt`,
    (round(((`invoiceBase`.`amtTotal` * `invoiceBase`.`splitFac`) * 20),
                0) / 20) AS `calcAmtTotal`,round((`invoiceBase`.`amtPrepaid` * `invoiceBase`.`splitFac`),
                        2) AS `calcAmtPrepaid`,round((`invoiceBase`.`amtTax` * `invoiceBase`.`splitFac`),
                2) AS `calcAmtTax`,round((`invoiceBase`.`amtObligations` * `invoiceBase`.`splitFac`),
        2
    ) AS `calcAmtObligations`,
        2) + `invoiceBase`.`amtPaid`) + `invoiceBase`.`amtPaidDirect`) AS `calcAmtPaid`,

character_set_client: latin1
collation_connection: latin1_swedish_ci

...

show create table benefitCombined \G;
*************************** 1. row ***************************
                View: benefitCombined
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`c0515_dwh`@`%` SQL SECURITY DEFINER VIEW `benefitCombined` AS (
select  `benefitBase`.`id` AS `id`,`benefitBase`.`additionType` AS `additionType`,
    `benefitBase`.`end` AS `end`,`benefitBase`.`eanExecuting` AS `eanExecuting`,
    `benefitBase`.`eanResponsible` AS `eanResponsible`,`benefitBase`.`unitFac` AS `unitFac`,
    `benefitBase`.`extFac` AS `extFac`,`benefitBase`.`amountQty1` AS `amountQty1`,
    `benefitBase`.`orderNumber` AS `orderNumber`,`benefitBase`.`srvAtts` AS `srvAtts`,
    `benefitBase`.`tariff` AS `tariff`,`benefitBase`.`benefitType` AS `benefitType`,
    `benefitBase`.`code` AS `code`,`benefitBase`.`executingDoctor` AS `executingDoctor`,
    `benefitBase`.`responsibleDoctor` AS `responsibleDoctor`,
    `benefitBase`.`obligation` AS `obligation`,`benefitBase`.`refCode` AS `refCode`,
    `benefitBase`.`quantity` AS `quantity`,`benefitBase`.`effQuant` AS `effQuant`,
    `benefitBase`.`begin` AS `begin`,`benefitBase`.`unit` AS `unit`,
    `benefitBase`.`scaleFac` AS `scaleFac`,`benefitBase`.`amount` AS `amount`,
    `benefitBase`.`effAmt` AS `effAmt`,`benefitBase`.`vatRate` AS `vatRate`,
    `benefitBase`.`negObligation` AS `negObligation`,`benefitBase`.`sessionNr` AS `sessionNr`,
    `benefitBase`.`pckMult` AS `pckMult`,`benefitBase`.`pckType` AS `pckType`,
    `benefitBase`.`pckUnits` AS `pckUnits`,`benefitBase`.`srvMin` AS `srvMin`,
    `benefitBase`.`name` AS `name`,`benefitBase`.`invoicePart` AS `invoicePart`,
    `benefitBase`.`created` AS `created`,`benefitBase`.`session` AS `session`,
    `benefitBase`.`patient` AS `patient`,`benefitBase`.`medicalCase` AS `medicalCase`,
    `benefitBase`.`invoice` AS `invoice`,`benefitBase`.`owner` AS `owner`,
    0 AS `unitFac2`,0 AS `extFac2`,0 AS `amount2Qty1`,NULL AS `bodyLoc`,
    0 AS `unit2`,0 AS `scaleFac2`,0 AS `amount2`,0 AS `effAmt2`,
    `benefitBase`.`amount` AS `calcAmtVat`,`benefitBase`.`effAmt` AS `effCalcAmtVat`,
            (`benefitBase`.`amount` / (1 + (`benefitBase`.`vatRate` / 100))) AS `calcAmtWoVat`,
            (`benefitBase`.`effAmt` / (1 + (`benefitBase`.`vatRate` / 100))) AS `effCalcAmtWoVat`,
NULL AS `calcMinTot`,NULL AS `effCalcMinTot`,
ifnull(round((`benefitBase`.`effAmt` / nullif(`benefitBase`.`amountQty1`,
                                0)),3),0
      ) AS `effQuantPckType` from  `benefitBase`
    where  (((`benefitBase`.`invoicePart` <> 'aaaaaaaaaaaaaaaaaaaa')
                      and  (`benefitBase`.`invoicePart` is not null))
              or  ((`benefitBase`.`invoice` <> 'aaaaaaaaaaaaaaaaaaaa')
                      and  (`benefitBase`.`invoice` is not null))
           )
    )
UNION all (
SELECT  `benefitTarmed`.`id` AS `id`,`benefitTarmed`.`additionType` AS `additionType`,
    `benefitTarmed`.`end` AS `end`,`benefitTarmed`.`eanExecuting` AS `eanExecuting`,
    `benefitTarmed`.`eanResponsible` AS `eanResponsible`,
    `benefitTarmed`.`unitFac` AS `unitFac`,`benefitTarmed`.`extFac` AS `extFac`,
    `benefitTarmed`.`amountQty1` AS `amountQty1`,`benefitTarmed`.`orderNumber` AS `orderNumber`,
    `benefitTarmed`.`srvAtts` AS `srvAtts`,`benefitTarmed`.`tariff` AS `tariff`,
    `benefitTarmed`.`benefitType` AS `benefitType`,`benefitTarmed`.`code` AS `code`,
    `benefitTarmed`.`executingDoctor` AS `executingDoctor`,
    `benefitTarmed`.`responsibleDoctor` AS `responsibleDoctor`,
    `benefitTarmed`.`obligation` AS `obligation`,`benefitTarmed`.`refCode` AS `refCode`,
    `benefitTarmed`.`quantity` AS `quantity`,`benefitTarmed`.`effQuant` AS `effQuant`,
    `benefitTarmed`.`begin` AS `begin`,`benefitTarmed`.`unit` AS `unit`,
    `benefitTarmed`.`scaleFac` AS `scaleFac`,`benefitTarmed`.`amount` AS `amount`,
    `benefitTarmed`.`effAmt` AS `effAmt`,`benefitTarmed`.`vatRate` AS `vatRate`,
    `benefitTarmed`.`negObligation` AS `negObligation`,`benefitTarmed`.`sessionNr` AS `sessionNr`,
    `benefitTarmed`.`pckMult` AS `pckMult`,`benefitTarmed`.`pckType` AS `pckType`,
    `benefitTarmed`.`pckUnits` AS `pckUnits`,`benefitTarmed`.`srvMin` AS `srvMin`,
    `benefitTarmed`.`name` AS `name`,`benefitTarmed`.`invoicePart` AS `invoicePart`,
    `benefitTarmed`.`created` AS `created`,`benefitTarmed`.`session` AS `session`,
    `benefitTarmed`.`patient` AS `patient`,`benefitTarmed`.`medicalCase` AS `medicalCase`,
    `benefitTarmed`.`invoice` AS `invoice`,`benefitTarmed`.`owner` AS `owner`,
    `benefitTarmed`.`unitFac2` AS `unitFac2`,`benefitTarmed`.`extFac2` AS `extFac2`,
    `benefitTarmed`.`amount2Qty1` AS `amount2Qty1`,`benefitTarmed`.`bodyLoc` AS `bodyLoc`,
    `benefitTarmed`.`unit2` AS `unit2`,`benefitTarmed`.`scaleFac2` AS `scaleFac2`,
    `benefitTarmed`.`amount2` AS `amount2`,`benefitTarmed`.`effAmt2` AS `effAmt2`,
        (`benefitTarmed`.`amount` + `benefitTarmed`.`amount2`) AS `calcAmtVat`,
        (`benefitTarmed`.`effAmt` + `benefitTarmed`.`effAmt2`) AS `effCalcAmtVat`,
        ((`benefitTarmed`.`amount` + `benefitTarmed`.`amount2`) / (1 + (`benefitTarmed`.`vatRate` / 100))) AS `calcAmtWoVat`,
        ((`benefitTarmed`.`effAmt` + `benefitTarmed`.`effAmt2`) / (1 + (`benefitTarmed`.`vatRate` / 100))) AS `effCalcAmtWoVat`,
        (`benefitTarmed`.`srvMin` * `benefitTarmed`.`quantity`) AS `calcMinTot`,
        (`benefitTarmed`.`srvMin` * `benefitTarmed`.`effQuant`) AS `effCalcMinTot`,ifnull(round(((`benefitTarmed`.`effAmt` + `benefitTarmed`.`effAmt2`) / nullif((`benefitTarmed`.`amountQty1` + `benefitTarmed`.`amount2Qty1`),0)),3),0) AS `effQuantPckType`
from  `benefitTarmed`
where  (((`benefitTarmed`.`invoicePart` <> 'aaaaaaaaaaaaaaaaaaaa')
                  and  (`benefitTarmed`.`invoicePart` is not null)
              )
          or  ((`benefitTarmed`.`invoice` <> 'aaaaaaaaaaaaaaaaaaaa')
                  and  (`benefitTarmed`.`invoice` is not null)))
      )
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.00 sec)

SHOW INDEX on "old db":

show index from benefitBase from c0515_dwh;
+-------------+------------+-------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name                            | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| benefitBase |          0 | id                                  |            1 | id                | A         |      275913 |     NULL | NULL   |      | BTREE      |         |               |
| benefitBase |          1 | benefitBase_created_index           |            1 | created           | A         |      275913 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitBase |          1 | benefitBase_tariff_index            |            1 | tariff            | A         |          13 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitBase |          1 | benefitBase_code_index              |            1 | code              | A         |          15 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitBase |          1 | benefitBase_executingDoctor_index   |            1 | executingDoctor   | A         |         249 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitBase |          1 | benefitBase_responsibleDoctor_index |            1 | responsibleDoctor | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitBase |          1 | benefitBase_obligation_index        |            1 | obligation        | A         |          13 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitBase |          1 | benefitBase_begin_index             |            1 | begin             | A         |       39416 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitBase |          1 | benefitBase_negObligation_index     |            1 | negObligation     | A         |          13 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitBase |          1 | benefitBase_name_index              |            1 | name              | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitBase |          1 | benefitBase_owner_index             |            1 | owner             | A         |         226 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitBase |          1 | benefitBase_patient_index           |            1 | patient           | A         |       30657 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitBase |          1 | benefitBase_session_index           |            1 | session           | A         |      275913 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+-------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

show index from benefitTarmed from c0515_dwh;
+---------------+------------+---------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name                              | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+---------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| benefitTarmed |          0 | id                                    |            1 | id                | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| benefitTarmed |          1 | benefitTarmed_created_index           |            1 | created           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitTarmed |          1 | benefitTarmed_tariff_index            |            1 | tariff            | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitTarmed |          1 | benefitTarmed_code_index              |            1 | code              | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitTarmed |          1 | benefitTarmed_executingDoctor_index   |            1 | executingDoctor   | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitTarmed |          1 | benefitTarmed_responsibleDoctor_index |            1 | responsibleDoctor | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitTarmed |          1 | benefitTarmed_obligation_index        |            1 | obligation        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitTarmed |          1 | benefitTarmed_begin_index             |            1 | begin             | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitTarmed |          1 | benefitTarmed_negObligation_index     |            1 | negObligation     | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitTarmed |          1 | benefitTarmed_name_index              |            1 | name              | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitTarmed |          1 | benefitTarmed_patient_index           |            1 | patient           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| benefitTarmed |          1 | benefitTarmed_owner_index             |            1 | owner             | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------------+------------+---------------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

show index from invoiceBase from c0515_dwh;
+-------------+------------+----------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name                         | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| invoiceBase |          0 | id                               |            1 | id             | A         |       40574 |     NULL | NULL   |      | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_created_index        |            1 | created        | A         |       40574 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_begin_index          |            1 | begin          | A         |       40574 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_end_index            |            1 | end            | A         |       40574 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_traStat_index        |            1 | traStat        | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_invStat_index        |            1 | invStat        | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_patient_index        |            1 | patient        | A         |       40574 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_provider_index       |            1 | provider       | A         |          34 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_biller_index         |            1 | biller         | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_name_index           |            1 | name           | A         |       40574 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_law_index            |            1 | law            | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_reason_index         |            1 | reason         | A         |          25 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_amtPrepaid_index     |            1 | amtPrepaid     | A         |          73 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_amtPaid_index        |            1 | amtPaid        | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_amtPaidDirect_index  |            1 | amtPaidDirect  | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_amtFee_index         |            1 | amtFee         | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_invoiceType_index    |            1 | invoiceType    | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_invoiceOptimal_index |            1 | invoiceOptimal | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_patientCopy_index    |            1 | patientCopy    | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_diagnoses_index      |            1 | diagnoses      | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_customerNumber_index |            1 | customerNumber | A         |          25 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_traStatDate_index    |            1 | traStatDate    | A         |       40574 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_invStatDate_index    |            1 | invStatDate    | A         |       40574 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_oldInvoiceNr_index   |            1 | oldInvoiceNr   | A         |          19 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_cancReqFlag_index    |            1 | cancReqFlag    | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_cancReqDate_index    |            1 | cancReqDate    | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_claimNr_index        |            1 | claimNr        | A         |          19 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_claimReqFlag_index   |            1 | claimReqFlag   | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_claimReqDate_index   |            1 | claimReqDate   | A         |          19 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_depReqFlag_index     |            1 | depReqFlag     | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_depReqDate_index     |            1 | depReqDate     | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_cancRemarks_index    |            1 | cancRemarks    | A         |        2386 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_depRemarks_index     |            1 | depRemarks     | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_p02InvNr_index       |            1 | p02InvNr       | A         |       40574 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_p02InvDate_index     |            1 | p02InvDate     | A         |       40574 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_newInvoiceNr_index   |            1 | newInvoiceNr   | A         |        4508 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_mandator_index       |            1 | mandator       | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_account_index        |            1 | account        | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_invoiceDate_index    |            1 | invoiceDate    | A         |       40574 |     NULL | NULL   | YES  | BTREE      |         |               |
| invoiceBase |          1 | invoiceBase_debMngmt_index       |            1 | debMngmt       | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+----------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

EXPLAIN on "new db"

explain SELECT 
    b.tariff as tariff, b.benefitType as benefitType, b.name as name, b.code as code, SUM(b.effQuantPckType) as `count`, 
    SUM(effCalcAmtVat) as `amount`, 
    SUM(effAmt) as `amountAL`, SUM(effAmt2) as `amountTL`  
FROM 
    benefitCombined AS b 
    LEFT OUTER JOIN invoice AS inv ON inv.id = b.invoice  
WHERE 
    (invoicePart != 'aaaaaaaaaaaaaaaaaaaa' OR invoice != 'aaaaaaaaaaaaaaaaaaaa') AND 
    executingDoctor = 'dqwjvgbskiaaaacsonmj' AND b.begin >= '2018-06-16 00:00:00' AND 
    b.begin <= '2018-07-16 23:59:57' AND 1=1 
GROUP BY 
    benefitType, code;

+------+--------------+---------------+------+---------------+------+---------+------+--------+----------------------------------------------+
| id   | select_type  | table         | type | possible_keys | key  | key_len | ref  | rows   | Extra                                        |
+------+--------------+---------------+------+---------------+------+---------+------+--------+----------------------------------------------+
|    1 | PRIMARY      | <derived2>    | ALL  | NULL          | NULL | NULL    | NULL | 281550 | Using where; Using temporary; Using filesort |
|    2 | DERIVED      | benefitBase   | ALL  | NULL          | NULL | NULL    | NULL | 281549 | Using where                                  |
|    3 | UNION        | benefitTarmed | ALL  | NULL          | NULL | NULL    | NULL |      1 | Using where                                  |
| NULL | UNION RESULT | <union2,3>    | ALL  | NULL          | NULL | NULL    | NULL |   NULL |                                              |
+------+--------------+---------------+------+---------------+------+---------+------+--------+----------------------------------------------+
7 rows in set (5.53 sec)

SHOW CREATE on "new db":

Identical to "old db".

I've done an "analyze table" on all tables / dbs on "new db" and also tried setting the same optimizer_switches, but the execution plan doesn't seem to change. mariadb.com mentions that "For all practical purposes, MariaDB is a binary drop in replacement of the same MySQL version...". My goal is to make the query execute at the same speed (at least) on "new db". I am looking for an explanation for the difference in the plans show above.

Edit: I've added index / view creation info, but the character limit is reached. The indexes show a difference in cardinality only, eg:

benefitBase_owner_index (old db: 233, new db: 17)
invoiceBase_newInvoiceNr_index (old db: 4647, new db: 1)
2

After setting the optimizer_switch (in this case 'table_elimination=on' was to blame for the difference in the execution plan) in the mariadb config file the execution plan changed and now resembles the one seen from 'old db' :

+------+--------------+---------------+--------+---------------+------+---------+-----------+--------+----------------------------------------------+
| id   | select_type  | table         | type   | possible_keys | key  | key_len | ref       | rows   | Extra                                        |
+------+--------------+---------------+--------+---------------+------+---------+-----------+--------+----------------------------------------------+
|    1 | PRIMARY      | <derived2>    | ALL    | NULL          | NULL | NULL    | NULL      | 306922 | Using where; Using temporary; Using filesort |
|    1 | PRIMARY      | invoiceBase   | eq_ref | id            | id   | 20      | b.invoice |      1 | Using where; Using index                     |
|    2 | DERIVED      | benefitBase   | ALL    | NULL          | NULL | NULL    | NULL      | 306921 | Using where                                  |
|    3 | UNION        | benefitTarmed | ALL    | NULL          | NULL | NULL    | NULL      |      1 | Using where                                  |
| NULL | UNION RESULT | <union2,3>    | ALL    | NULL          | NULL | NULL    | NULL      |   NULL |                                              |
+------+--------------+---------------+--------+---------------+------+---------+-----------+--------+----------------------------------------------+

I expected a change in execution time but the query still clocks in at about 6 seconds on a considerably faster (concerning CPU) host. As there might have been a difference in I/O speed or latency, i've mounted the tmpdir (which is used for writing the disk-bound temp tables) as a ramdisk, but there was no significant change in execution speed.

| improve this answer | |
0

Why did it change between versions? Did the phase of the moon change? I am only half joking. EXPLAIN depends on "statistics" that are approximate. Probably what happened is that the stats changed, leading to a different query plan. And, since the Optimizer is not perfect, perhaps it misread the stats and picked the wrong index (or whatever).

5.5.54 is long after development ceased on 5.5; only critical bugs were being fixed. So, I don't believe any deliberate changes were made in the Optimizer, only bug fixes.

Please learn the benefits of "composite" indexes.

  AND executingDoctor = 'dqwjvgbskiaaaacsonmj'
  AND b.begin >= '2018-06-16 00:00:00' AND 
      b.begin <= '2018-07-16 23:59:57'

begs for

INDEX(executingDoctor, `begin`)  -- in this order

This is assuming executingDoctor is in b. Please qualify all columns when using a JOIN. And provide SHOW CREATE TABLE.

Caveat: since a VIEW is involved, it may not be possible to make such an index and/or it may not get used.

Having dozens of indexes is usually a waste. It is extremely likely that none of the indexes with Cardinality=1 will ever be used. DROP them. And probably those will 'small' cardinality are also unused.

VIEWs are still somewhat in their infancy in MySQL, especially back in 5.5. Views are syntactic sugar around SELECTs and provide no performance benefit. And sometimes they are not as well optimized.

For that kind of date range, consider

  AND b.begin >= '2018-06-16' AND 
  AND b.begin  < '2018-06-16' + INTERVAL 1 MONTH

Hmmm... invoiceBase is in the first EXPLAIN but not in the second. Did the query change? Did the row(s) needed from that able vanish? After all it is a LEFT JOIN.

| improve this answer | |

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.