left join vs inner join
It's not the same for me, MySql 5.7 8 vCPU, 52 GB RAM
The following query takes ~30 seconds, not sure why
The transactions table has 24,257,151 records
The activity table has 18,603,665 records
The purchases table has 13,911,705 records
All required indexes are in place
SELECT
`trx`.`transaction_pk`,
`trx`.`created`,
`trx`.`updated`,
`p`.`amount`,
`trxst`.`name`,
COALESCE ( a.units, 0 ) AS units
FROM
`transaction` AS `trx`
INNER JOIN `transaction_sub_type` AS `trxst` ON `trx`.`transaction_sub_type_fk`= `trxst`.`transaction_sub_type_pk`
left JOIN `activity` AS `a` ON `a`.`transaction_fk` = `trx`.`transaction_pk`
LEFT JOIN `purchases` AS `p` ON `p`.`transaction_fk` = `trx`.`transaction_pk`
WHERE
`trx`.`entity_fk` IN ( 1234)
AND `trx`.`transaction_sub_type_fk` IN (
2, 4, 5, 15, 16, 33, 37, 38, 85, 86, 87, 88, 102, 103
)
ORDER BY
`trx`.`transaction_pk` DESC LIMIT 100 OFFSET 0;
After replacing the following line:
INNER JOIN `transaction_sub_type` AS `trxst` ON `trx`.`transaction_sub_type_fk`= `trxst`.`transaction_sub_type_pk`
with LEFT JOIN
LEFT JOIN `transaction_sub_type` AS `trxst` ON `trx`.`transaction_sub_type_fk`= `trxst`.`transaction_sub_type_pk`
the same query takes ~0.046s
Explain before:
1 SIMPLE trxst ALL PRIMARY 101 37.62 Using where; Using temporary; Using filesort
1 SIMPLE trx ref transaction_sub_type_fk,entity_fk transaction_sub_type_fk 4 trxst.transaction_sub_type_pk 2548 0.36 Using where
1 SIMPLE a ref transaction_fk transaction_fk 4 trx.transaction_pk 1 100
1 SIMPLE p ref transaction_fk transaction_fk 4 trx.transaction_pk 1 100
Explain after:
1 SIMPLE trx ref transaction_sub_type_fk,entity_fk entity_fk 4 const 81474 83.65 Using where
1 SIMPLE trxst eq_ref PRIMARY PRIMARY 4 trx.transaction_sub_type_fk 1 100
1 SIMPLE a ref transaction_fk transaction_fk 4 trx.transaction_pk 1 100
1 SIMPLE p ref transaction_fk transaction_fk 4 trx.transaction_pk 1 100
