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.
Best Answer
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 exceptSELECT
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 ofmovementId
anditemId
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:
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 thatSUM
query. What exactly you areSUM
ming will require different optimizations than aSELECT *
will.Also, provide your
EXPLAIN
plans. You mention that you've run them, but don't provide that extremely useful information to us.