Mysql – Why does MySQL take so long to execute this query on a large dataset

Been banging my head against the wall on this one for a while now and hoping someone can help me out.

The SQL query:

SELECT
    `elements`.`id` AS `elementsId`,
    `elements_sites`.`id` AS `elementsSitesId`,
    `content`.`id` AS `contentId`
FROM
    `elements` `elements`                                                            
    INNER JOIN `entries` `entries` ON `entries`.`id` = `elements`.`id`
    INNER JOIN `elements_sites` `elements_sites` ON `elements_sites`.`elementId` = `elements`.`id`
    INNER JOIN `content` `content` ON ( `content`.`elementId` = `elements`.`id` ) AND ( `content`.`siteId` = `elements_sites`.`siteId` )
WHERE
    `elements_sites`.`siteId` = '1'
    AND `elements`.`archived` = FALSE
    AND `elements`.`dateDeleted` IS NULL
    AND `elements`.`draftId` IS NULL
    AND `elements`.`revisionId` IS NULL
ORDER BY
    `entries`.`postDate` DESC 
LIMIT 100

The table schemas with indexes:

CREATE TABLE `content` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `elementId` int(11) NOT NULL,
  `siteId` int(11) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `dateCreated` datetime NOT NULL,
  `dateUpdated` datetime NOT NULL,
  `uid` char(36) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `content_elementId_siteId_unq_idx` (`elementId`,`siteId`),
  KEY `content_siteId_idx` (`siteId`),
  KEY `content_title_idx` (`title`),
  CONSTRAINT `content_elementId_fk` FOREIGN KEY (`elementId`) REFERENCES `elements` (`id`) ON DELETE CASCADE,
  CONSTRAINT `content_siteId_fk` FOREIGN KEY (`siteId`) REFERENCES `sites` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `elements_sites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `elementId` int(11) NOT NULL,
  `siteId` int(11) NOT NULL,
  `slug` varchar(255) DEFAULT NULL,
  `uri` varchar(255) DEFAULT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  `dateCreated` datetime NOT NULL,
  `dateUpdated` datetime NOT NULL,
  `uid` char(36) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `elements_sites_elementId_siteId_unq_idx` (`elementId`,`siteId`),
  KEY `elements_sites_siteId_idx` (`siteId`),
  KEY `elements_sites_slug_siteId_idx` (`slug`,`siteId`),
  KEY `elements_sites_enabled_idx` (`enabled`),
  KEY `elements_sites_uri_siteId_idx` (`uri`,`siteId`),
  CONSTRAINT `elements_sites_elementId_fk` FOREIGN KEY (`elementId`) REFERENCES `elements` (`id`) ON DELETE CASCADE,
  CONSTRAINT `elements_sites_siteId_fk` FOREIGN KEY (`siteId`) REFERENCES `sites` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `entries` (
  `id` int(11) NOT NULL,
  `sectionId` int(11) NOT NULL,
  `parentId` int(11) DEFAULT NULL,
  `typeId` int(11) NOT NULL,
  `authorId` int(11) DEFAULT NULL,
  `postDate` datetime DEFAULT NULL,
  `expiryDate` datetime DEFAULT NULL,
  `deletedWithEntryType` tinyint(1) DEFAULT NULL,
  `dateCreated` datetime NOT NULL,
  `dateUpdated` datetime NOT NULL,
  `uid` char(36) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `entries_postDate_idx` (`postDate`),
  KEY `entries_expiryDate_idx` (`expiryDate`),
  KEY `entries_authorId_idx` (`authorId`),
  KEY `entries_sectionId_idx` (`sectionId`),
  KEY `entries_typeId_idx` (`typeId`),
  KEY `entries_parentId_fk` (`parentId`),
  CONSTRAINT `entries_authorId_fk` FOREIGN KEY (`authorId`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `entries_id_fk` FOREIGN KEY (`id`) REFERENCES `elements` (`id`) ON DELETE CASCADE,
  CONSTRAINT `entries_parentId_fk` FOREIGN KEY (`parentId`) REFERENCES `entries` (`id`) ON DELETE SET NULL,
  CONSTRAINT `entries_sectionId_fk` FOREIGN KEY (`sectionId`) REFERENCES `sections` (`id`) ON DELETE CASCADE,
  CONSTRAINT `entries_typeId_fk` FOREIGN KEY (`typeId`) REFERENCES `entrytypes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `elements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `draftId` int(11) DEFAULT NULL,
  `revisionId` int(11) DEFAULT NULL,
  `fieldLayoutId` int(11) DEFAULT NULL,
  `type` varchar(255) NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  `archived` tinyint(1) NOT NULL DEFAULT '0',
  `dateCreated` datetime NOT NULL,
  `dateUpdated` datetime NOT NULL,
  `dateDeleted` datetime DEFAULT NULL,
  `uid` char(36) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `elements_dateDeleted_idx` (`dateDeleted`),
  KEY `elements_fieldLayoutId_idx` (`fieldLayoutId`),
  KEY `elements_type_idx` (`type`),
  KEY `elements_enabled_idx` (`enabled`),
  KEY `elements_archived_dateCreated_idx` (`archived`,`dateCreated`),
  KEY `elements_draftId_fk` (`draftId`),
  KEY `elements_revisionId_fk` (`revisionId`),
  KEY `elements_uid_idx` (`uid`),
  KEY `elements_dd_draftId_revisionId_idx` (`archived`,`dateDeleted`,`draftId`,`revisionId`),
  CONSTRAINT `elements_draftId_fk` FOREIGN KEY (`draftId`) REFERENCES `drafts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `elements_fieldLayoutId_fk` FOREIGN KEY (`fieldLayoutId`) REFERENCES `fieldlayouts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `elements_revisionId_fk` FOREIGN KEY (`revisionId`) REFERENCES `revisions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

Explain on the query:

| id | select_type | table          | type   | possible_keys                                                                                                                                                      | key                                     | key_len | ref                     | rows    | filtered | Extra                           |
|----|-------------|----------------|--------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------|-----------------------------------------|---------|-------------------------|---------|----------|---------------------------------|
| 1  | SIMPLE      | content        | ref    | content_elementId_siteId_unq_idx,content_siteId_idx                                                                                                                | content_siteId_idx                      | 4       | const                   | 1534674 | 100.00   | Using temporary; Using filesort |
| 1  | SIMPLE      | elements       | eq_ref | PRIMARY,elements_dateDeleted_idx,elements_archived_dateCreated_idx,elements_draftId_fk,elements_revisionId_fk,elements_archived_dateDeleted_draftId_revisionId_idx | PRIMARY                                 | 4       | content.elementId       | 1       | 6.25     | Using where                     |
| 1  | SIMPLE      | entries        | eq_ref | PRIMARY                                                                                                                                                            | PRIMARY                                 | 4       | content.elementId       | 1       | 100.00   |                                 |
| 1  | SIMPLE      | elements_sites | eq_ref | elements_sites_elementId_siteId_unq_idx,elements_sites_siteId_idx                                                                                                  | elements_sites_elementId_siteId_unq_idx | 8       | content.elementId,const | 1       | 100.00   | Using where; Using index        |

If I comment out this one line in the WHERE clause, it executes in < 1 second. If I leave it in, it takes a minute.

`elements_sites`.`siteId` = '1'

There is an index on elements_sites.siteId and I think I understand why MySQL can't use it, but is there an index I can add somewhere so that both tables can be used in the where clause? Or perhaps the order of some items in the query is incorrect?

Updated Info

This is MySQL 5.7.28.

Here is the EXPLAIN with the siteId removed from the WHERE clause:

WHERE
    ( `elements`.`archived` = FALSE ) 
    AND ( `elements`.`dateDeleted` IS NULL ) 
    AND ( `elements`.`draftId` IS NULL ) 
    AND ( `elements`.`revisionId` IS NULL )

| id | select_type | table          | type   | possible_keys                                                                                                                                                      | key                                     | key_len | ref                       | rows | filtered | Extra       |
|----|-------------|----------------|--------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------|-----------------------------------------|---------|---------------------------|------|----------|-------------|
| 1  | SIMPLE      | entries        | index  | PRIMARY                                                                                                                                                            | entries_postDate_idx                    | 6       |                           | 100  | 100.00   | Using index |
| 1  | SIMPLE      | elements       | eq_ref | PRIMARY,elements_dateDeleted_idx,elements_archived_dateCreated_idx,elements_draftId_fk,elements_revisionId_fk,elements_archived_dateDeleted_draftId_revisionId_idx | PRIMARY                                 | 4       | entries.id                | 1    | 6.25     | Using where |
| 1  | SIMPLE      | content        | ref    | content_elementId_siteId_unq_idx,content_siteId_idx                                                                                                                | content_elementId_siteId_unq_idx        | 4       | entries.id                | 1    | 100.00   | Using index |
| 1  | SIMPLE      | elements_sites | eq_ref | elements_sites_elementId_siteId_unq_idx,elements_sites_siteId_idx                                                                                                  | elements_sites_elementId_siteId_unq_idx | 8       | entries.id,content.siteId | 1    | 100.00   | Using index |

And here it is with AND ( content.siteId = elements_sites.siteId) removed:

| id | select_type | table          | type   | possible_keys                                                                                                                                                      | key                              | key_len | ref                      | rows    | filtered | Extra                           |
|----|-------------|----------------|--------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------------------------------|---------|--------------------------|---------|----------|---------------------------------|
| 1  | SIMPLE      | elements_sites | ref    | elements_sites_elementId_siteId_unq_idx,elements_sites_siteId_idx                                                                                                  | elements_sites_siteId_idx        | 4       | const                    | 2062694 | 100.00   | Using temporary; Using filesort |
| 1  | SIMPLE      | elements       | eq_ref | PRIMARY,elements_dateDeleted_idx,elements_archived_dateCreated_idx,elements_draftId_fk,elements_revisionId_fk,elements_archived_dateDeleted_draftId_revisionId_idx | PRIMARY                          | 4       | elements_sites.elementId | 1       | 6.25     | Using where                     |
| 1  | SIMPLE      | entries        | eq_ref | PRIMARY                                                                                                                                                            | PRIMARY                          | 4       | elements_sites.elementId | 1       | 100.00   |                                 |
| 1  | SIMPLE      | content        | ref    | content_elementId_siteId_unq_idx                                                                                                                                   | content_elementId_siteId_unq_idx | 4       | elements_sites.elementId | 1       | 100.00   | Using index                     |