Mysql – Optimizing slow query

For a few days now, i'm trying to optimize this query.
But still I haven't found the right solution in how to fix this.

The contents table contains about 20 million records. As the connect table holds about 60 million.

The query runs in about 20 seconds:

SELECT *
FROM `contents` AS `n`
INNER JOIN `connect` AS `a` ON `a`.`mcded_primary_id_mcdno_id` = `n`.`mcdno_id`
INNER JOIN `connect` AS `b` ON `b`.`mcded_primary_id_mcdno_id` = `n`.`mcdno_id`
INNER JOIN `connect` AS `c` ON `c`.`mcded_primary_id_mcdno_id` = `n`.`mcdno_id`
WHERE `a`.`mcded_child_id_mcdno_id` = '1375'
  AND `a`.`mcded_structure_mcdes_id` = '85'
  AND `b`.`mcded_child_id_mcdno_id` = '13'
  AND `b`.`mcded_structure_mcdes_id` = '187'
  AND `c`.`mcded_child_id_mcdno_id` IN ('500065',
                                    '500066',
                                    '500067',
                                    '500068')
  AND `c`.`mcded_structure_mcdes_id` = '211'
  AND `n`.`mcdno_structure_mcdns_id` IN ('1')
GROUP BY `n`.`mcdno_id`
ORDER BY `n`.`mcdno_id` DESC LIMIT 14
OFFSET 42

But it uses temp table every time.

This is the EXPLAIN:

+----+-------------+------+---------------+---------------------------------------------------------------------------------------------------+--------------------------------------------------+-----+-----------------------------------------+-------+-----------------------------------------------------------------------------------------------------------------+
| id | select_type | type | possible_keys |                                                key                                                |                     key_len                      | ref |                  rows                   | Extra |                                                                                                                 |
+----+-------------+------+---------------+---------------------------------------------------------------------------------------------------+--------------------------------------------------+-----+-----------------------------------------+-------+-----------------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | b    | index_merge   | UN_set,FK_primary_id_id,FK_structure_mcdns_id_id,FK_child_id_mcdno_id_id,IND_primary_id_structure | FK_structure_mcdns_id_id,FK_child_id_mcdno_id_id | 4,8 | NULL                                    | 37928 | Using intersect(FK_structure_mcdns_id_id,FK_child_id_mcdno_id_id); Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | c    | ref           | UN_set,FK_primary_id_id,FK_structure_mcdns_id_id,FK_child_id_mcdno_id_id,IND_primary_id_structure | IND_primary_id_structure                         | 12  | b.mcded_primary_id_mcdno_id,const       |     1 | Using where                                                                                                     |
|  1 | SIMPLE      | n    | eq_ref        | PRIMARY,FK_mcdno_structure_mcdns_id                                                               | PRIMARY                                          | 8   | c.mcded_primary_id_mcdno_id             |     1 | Using where                                                                                                     |
|  1 | SIMPLE      | a    | ref           | UN_set,FK_primary_id_id,FK_structure_mcdns_id_id,FK_child_id_mcdno_id_id,IND_primary_id_structure | UN_set                                           | 20  | b.mcded_primary_id_mcdno_id,const,const |     1 |                                                                                                                 |
+----+-------------+------+---------------+---------------------------------------------------------------------------------------------------+--------------------------------------------------+-----+-----------------------------------------+-------+-----------------------------------------------------------------------------------------------------------------+

I tried using connect (a) as primary table, but that is even worse.

Any suggestions?

—
CONTENTS

  CREATE TABLE `contents` (
  `mcdno_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `mcdno_created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `mcdno_changed_on` timestamp NULL DEFAULT NULL,
  `mcdno_structure_mcdns_id` int(11) NOT NULL,
  PRIMARY KEY (`mcdno_id`),
  KEY `FK_mcdno_structure_mcdns_id` (`mcdno_structure_mcdns_id`),
  KEY `IND_created_on` (`mcdno_created_on`),
  KEY `IND_changed_on` (`mcdno_changed_on`),
  KEY `IND_published_on` (`mcdno_published_on`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

—
CONNECT

CREATE TABLE `connect` (
 `mcded_id` bigint(11) NOT NULL AUTO_INCREMENT,
 `mcded_primary_id_mcdno_id` bigint(20) NOT NULL,
 `mcded_child_id_mcdno_id` bigint(20) NOT NULL,
 `mcded_changed_on` datetime DEFAULT NULL,
 `mcded_structure_mcdes_id` int(11) NOT NULL
 PRIMARY KEY (`mcded_id`),
 UNIQUE KEY `UN_set` (`mcded_primary_id_mcdno_id`,`mcded_child_id_mcdno_id`,`mcded_structure_mcdes_id`),
 KEY `IND_created_on` (`mcded_created_on`),
 KEY `FK_primary_id_id` (`mcded_primary_id_mcdno_id`),
 KEY `FK_structure_mcdns_id_id` (`mcded_structure_mcdes_id`),
 KEY `FK_child_id_mcdno_id_id` (`mcded_child_id_mcdno_id`),
 KEY `IND_primary_id_structure`         (`mcded_primary_id_mcdno_id`,`mcded_structure_mcdes_id`)
 ),     ENGINE=InnoDB DEFAULT CHARSET=utf8