I have these tables.
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`);
and this view called "movements_items_view".
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)
The first table has 5913 rows, the second one has 144992.
The view is very fast, it loads 20 result in PhpMyAdmin in 0.0011s but as soon as I ask for a GROUP BY on it (I need it to do statistics with SUM()) es:
SELECT * FROM movements_items_view GROUP BY itemId LIMIT 0,20
time jumps to 0.2s or more and it causes "Using where; Using temporary; Using filesort" on movements join.
Edit: Bypassing the view.
I also run via phpMyAdmin this query to try to not use the view:
SELECT movements.movementId, movements.movementType, movements_items.qty FROM movements_items JOIN movements ON movements.movementId=movements_items.movementId GROUP BY itemId LIMIT 0,20
And the performance is basically the same.
Edit. Here is the EXPLAIN
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE movements index PRIMARY,movementid movement_type 1 NULL 5913 Using index; Using temporary; Using filesort 1 SIMPLE movements_items ref movementId,itemId,movementId_2 movementId_2 3 movements.movementId 12 Using index
Any help appreciated, thanks.
GROUP BYdoesn't make much logical sense. Grouping by
itemIdmeans just one row for each
itemIdis returned, but you are selecting the
movement_itemstable. Which row should it choose to report the
qtyfrom? In later versions of MySQL it prevents you (by default) from running such ambiguous queries. The
qtyvalue will effectively be random (not reliably random, but non-deterministic), making it useless to have. Likewise, the other columns selected will be "random" too, if any
itemIdhas more than one
movementIdin that table.