Sum between multiple joined tables

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.

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)