MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `movements` (
  `movementId` mediumint(8) UNSIGNED NOT NULL,
  `movementType` tinyint(3) UNSIGNED NOT NULL,
  `deleted` tinyint(1) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `movements`
  ADD PRIMARY KEY (`movementId`),
  ADD KEY `movementType` (`movementType`) USING BTREE,
  ADD KEY `deleted` (`deleted`),
  ADD KEY `movementId` (`movementId`,`deleted`);
CREATE TABLE `movements_items` (
  `movementId` mediumint(8) UNSIGNED NOT NULL,
  `itemId` mediumint(8) UNSIGNED NOT NULL,
  `qty` decimal(10,3) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `movements_items`
  ADD KEY `movementId` (`movementId`),
  ADD KEY `itemId` (`itemId`),
  ADD KEY `movementId_2` (`movementId`,`itemId`);
CREATE VIEW movements_items_view
AS
SELECT 
movements_items.itemId, movements_items.qty,
movements.movementId, movements.movementType
FROM movements_items
JOIN movements ON (movements.movementId=movements_items.movementId AND movements.deleted=0);
SET SESSION sql_mode='';
explain
SELECT * 
FROM movements_items_view 
GROUP BY itemId;


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
movements_items
null
ALL
movementId,itemId,movementId_2
null
null
null
1
100.00
Using temporary

1
SIMPLE
movements
null
eq_ref
PRIMARY,deleted,movementId
PRIMARY
3
fiddle_NGOYHDBMVKQVDSOGKXVZ.movements_items.movementId
1
100.00
Using where


explain
SELECT * 
FROM (
SELECT 
movements_items.itemId, movements_items.qty,
movements.movementId, movements.movementType
FROM movements_items
JOIN movements ON (movements.movementId=movements_items.movementId AND movements.deleted=0)
) AS movements_items_view 
GROUP BY itemId;


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
movements_items
null
ALL
movementId,itemId,movementId_2
null
null
null
1
100.00
Using temporary

1
SIMPLE
movements
null
eq_ref
PRIMARY,deleted,movementId
PRIMARY
3
fiddle_NGOYHDBMVKQVDSOGKXVZ.movements_items.movementId
1
100.00
Using where

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	movements_items	null	ALL	movementId,itemId,movementId_2	null	null	null	1	100.00	Using temporary
1	SIMPLE	movements	null	eq_ref	PRIMARY,deleted,movementId	PRIMARY	3	fiddle_NGOYHDBMVKQVDSOGKXVZ.movements_items.movementId	1	100.00	Using where