Mysql GROUP BY is really slow on a simple view
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.