2

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.

  • Specify your MySQL version. It is critical. Look fiddle results, and compare with the results when 1) change MySQL version 2) add some data. – Akina Dec 15 '19 at 14:17
  • Hi @Akina, the version is "5.6.45-log - MySQL Community Server". That's what I read in phpMyAdmin. I don't have the rights to change the version. :( – silentheaven Dec 15 '19 at 14:19
  • While using view the timee is ~0.2s. While replacing view name with its code the time is ??? – Akina Dec 15 '19 at 14:20
  • Bypassing the view the time is basically the same, 0.180s. If I make the GROUP BY on the 'movements_items' table alone using the same field (itemId) the time is 0.0005s. The problem is in the JOIN with movements. – silentheaven Dec 15 '19 at 14:28
  • @silentheaven Your query with the GROUP BY doesn't make much logical sense. Grouping by itemId means just one row for each itemId is returned, but you are selecting the qty from the movement_items table. Which row should it choose to report the qty from? In later versions of MySQL it prevents you (by default) from running such ambiguous queries. The qty value 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 itemId has more than one movementId in that table. – Willem Renzema Dec 15 '19 at 15:10
3

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.

  • @William it's so complicated to reply to you via comments. Should I reply to you with a new answer or editing the original post? Anyway, in the 'movements_items' table there is no unique key because there's no combination of data that can be unique, there can be duplicates rows. I didin't think about putting a primary key in that table because I don't need it in PHP. Should I add that? The query is also slow if I bypass the view. It has to be something about joining on 'movements' table while requesting a GROUP BY. – silentheaven Dec 15 '19 at 14:07
  • I didn't need a primary key because when I have to read the data I use that view. When I have to write, I simply delete all the rows from the 'movements_items' for that movementId and then I write all the new data. I thought this was a goog idea because I didn't have to worry about an auto_increment index. Don't know if it's actually a good idea right now. – silentheaven Dec 15 '19 at 14:22
  • @silentheaven With regards to how to reply, you should never create a new answer (unless it is actually an answer that describes how you solved your problem, but that's not the case here). Either reply in a comment, or edit information into your question. If you edit information into your question that another person requested, it helps to create a comment directed to them (using the @ NameGoesHere feature) mentioning that, so they can see a notification and return to your question on this site. – Willem Renzema Dec 15 '19 at 14:58
  • @silentheaven It sounds like you have some misconceptions about what a primary key is and what it does. It is more than simply providing a way to identify each row (although that is one thing.) Also, a view simply runs the underlying query, and uses the underlying tables, so it will still be affected by whether or not a primary key exists. That said, in your particular use case I'm not sure how much a PK would change things. The index I suggested was expected to provide the biggest improvement. Have you tried that yet? – Willem Renzema Dec 15 '19 at 15:06
  • I created the index you said (itemId,movementId,qty) but that didn't help. :( The EXPLAIN still shows "Using where; Using temporary; Using filesort" on movements table like the one in the original post. I didn't make a PK because I wanted to wait for your final "decision". – silentheaven Dec 15 '19 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.