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

Question

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                     |

Answer 1

ON `entries`.`id` = `elements`.`id`

implies that those tables are 1:1. If so, I think it would make a lot of sense, and allow you to better optimize your query (and probably other queries).

Meanwhile, these indexes may help:

elements_sites:  (siteId, elementId, id)
content:  (siteId, elementId, id)

Consider getting rid of id in any table that has a UNIQUE key. Then change the PRIMARY KEY to be what was the UNIQUE key.