1

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                     |
New contributor
Brad Bell is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • really interesting question. What does the EXPLAIN look like on the query without the siteId in the where clause? Which MySQL version? Try leaving out AND ( content.siteId = elements_sites.siteId)` as the siteId is ready fixed. – danblack Feb 14 at 2:39
  • @danblack thanks, Dan. Updated the question with the additional info. – Brad Bell Feb 14 at 4:18
  • CREATE INDEX idx ON elements (archived, dateDeleted, draftId, revisionId, id, postDate). Первые 4 поля можно менять местами. – Akina Feb 14 at 5:08
  • @Akina Unfortunately postDate is on the entries and all the other columns are on the elements table. – Brad Bell Feb 14 at 17:47
  • did you try to change column order in elements_sites_elementId_siteId_unq_idx index to (siteId, elementId)? – Nikita Feb 14 at 21:28

Your Answer

Brad Bell is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.