Mysql GROUP BY is really slow on a simple view

Question

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.

Answer 1

First things first: Does the query continue to perform slowly if you bypass the view and run the underlying query with the GROUP BY? In my experience, MySQL is notoriously bad with views that are used with anything except SELECT statements.

If that fixes the problem, then just create a second view with the actual information you want to query (that is, with the GROUP BY included in the view).

If the problem persists, then there's a few things that can be improved.

Add primary key

You have no primary key on the movement_items table. Add one. If each combination of movementId and itemId can only have on entry, then make the PK a composite key out of those columns. Otherwise, you can add a surrogate autoincrement primary key.

Add index

Try the following index:

movement_items: (itemId,movementId,qty)

Provide the actual query you want optimized

Query optimization is not a piecemeal process. You cannot improve one query, and then expect that improvement to necessarily carry through to related queries.

You say you want to do a SUM, well, show us that SUM query. What exactly you are SUMming will require different optimizations than a SELECT * will.

Also, provide your EXPLAIN plans. You mention that you've run them, but don't provide that extremely useful information to us.