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)
Best Answer
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' :
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.