0

I have these tables:

DROP TABLE IF EXISTS books;
CREATE TABLE `books` (
  `bookId` mediumint(8) UNSIGNED NOT NULL,
  `title` varchar(10) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `books`
  ADD PRIMARY KEY (`bookId`),

DROP TABLE IF EXISTS movements;
CREATE TABLE `movements` (
  `movementId` mediumint(8) UNSIGNED NOT NULL,
  `movementTypeId` tinyint(3) UNSIGNED NOT NULL,
  `deletedFlag` tinyint(3) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `movements`
  ADD PRIMARY KEY (`movementId`),
  ADD KEY `movementId` (`movementTypeId`,`deletedFlag`) USING BTREE;

DROP TABLE IF EXISTS movements_types;
CREATE TABLE `movements_types` (
  `movementTypeId` mediumint(8) UNSIGNED NOT NULL,
  `title` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `movements_types`
  ADD PRIMARY KEY (`movementTypeId`);

DROP TABLE IF EXISTS movements_books;
CREATE TABLE `movements_books` (
  `movementId` mediumint(8) UNSIGNED NOT NULL,
  `bookId` mediumint(8) UNSIGNED NOT NULL,
  `bookSize` tinyint(3) UNSIGNED NOT NULL,
  `quantity` smallint(5) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `books` (`bookId`, `title`) VALUES
(1, 'Harry Potter'),
(2, 'Mysql Join'),
(3, 'Comedy');

INSERT INTO `movements` (`movementId`, `movementTypeId`, `deletedFlag`) VALUES
(7, 1, 0),
(8, 2, 0),
(9, 2, 0);

INSERT INTO `movements_types` (`movementTypeId`, `title`) VALUES
(1, "Bought"),
(2, "Sold");

INSERT INTO `movements_books` (`movementId`, `bookId`, `bookSize`, `quantity`) VALUES
(7, 1, 1, 3),
(7, 1, 2, 3),
(7, 2, 1, 3),
(7, 2, 2, 3),
(8, 1, 1, 2),
(8, 1, 1, 2),
(9, 2, 1, 3);

bookSize is just an integer indicating the size of the book.

movements_books actually has 111824 records, movements has 4534. deletedFlag is 1 for a deleted movement (I prefer to keep them flagged) and 0 otherwise.

I need help creating indexes and I need these results:

1) sizes sold/bought/available for each book (bookId, bookSize, booksSold, booksBought, booksBought minus booksSold)

2) books sold/bought/available for each book of all size (bookId, booksSold, booksBought, booksBought minus booksSold)

To get the one of the statistics I tried:

SELECT 
   books.title
   movements_books_grouped.bookId,
   SUM(IF(movements.movementTypeId=1, movements_books_grouped.quantity, NULL)) AS booksBought,
   SUM(IF(movements.movementTypeId=2, movements_books_grouped.quantity, NULL)) AS booksSold,
   (
       SUM(IF(movements.movementTypeId=1, movements_books_grouped.quantity, 0))- 
       SUM(IF(movements.movementTypeId=2, movements_books_grouped.quantity, 0)) 
   ) AS booksAvailability
   FROM 
   (
   SELECT bookId,quantity,movementId FROM movements_books GROUP BY bookId
   ) AS movements_books_grouped 
   JOIN movements ON movements.movementId=movements_books_grouped.movementId
   JOIN books ON books.bookId=movements_books_grouped.bookId
   GROUP BY movements_books_grouped.bookId
   ORDER BY books.title

but it's very slow. Edit: I needed to add another table to the example because that's the one that makes really slow the last query. I need to join to this table because I need to order the result by title.

0

I suggest the schema is "over-normalized". I would suggest any table that lists a 'movement' should include the direction (movementTypeId). Perhaps it could be done with a sign instead of an extra column? (Positive for Bought, negative for Sold.)

There are no indexes on movements_books; maybe other tables?

The GROUP BY bookId in the subquery is probably wrong.

(cleaned up a little, and fixed missing comma:)

SELECT  b.title, mbg.bookId,
        SUM(IF(m.movementTypeId=1, mbg.quantity, NULL)) AS booksBought,
        SUM(IF(m.movementTypeId=2, mbg.quantity, NULL)) AS booksSold,
        ( SUM(IF(m.movementTypeId=1, mbg.quantity, 0))-
          SUM(IF(m.movementTypeId=2, mbg.quantity, 0)) 
        ) AS booksAvailability,
    FROM (
        SELECT  bookId, quantity, movementId
            FROM  movements_books AS mb AS b
            GROUP BY  bookId 
         ) AS  AS mbg
    JOIN  movements AS m  ON m.movementId = mbg.movementId
    JOIN  books AS b2  ON b.bookId = mbg.bookId
    GROUP BY  mbg.bookId
    ORDER BY  b2.title 

movements_books needs

PRIMARY KEY(bookId, movementId),
INDEX(movementId, bookId)
4
  • Hi Rick, thanks for the reply. The sign suggestion is very clever but I already have lots of records, in this case I’m worried to risk a change. movements_books has no index because I don’t know which one to create for those “statistics query”. Can you tell me why you think that GROUP BY is wrong? Thank you for the fix and clean up. May 27 '19 at 0:26
  • @silentheaven - Research ONLY_FULL_GROUP_BY.
    – Rick James
    May 27 '19 at 17:53
  • Rick, I need more help, please. May 29 '19 at 8:55
  • I added indexes for movements_books,
    – Rick James
    May 29 '19 at 14:28

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.