I have a query in my DB that is taking 25 seconds to return results, which is way too long. It seems like it should be pretty simple. Two tables; the main table (document) is a standard table with some data columns, the join table is a mapping table with only two columns (parent_id, division_id). Previously there wasn't an index on the mapping table so I added one and that changed the "explain" to include the index but doesn't seem to have had an impact on the performance.
The query looks like this:
explain SELECT DISTINCT doc.*
FROM document doc
LEFT JOIN multi_division_mapper divisions ON doc.id = divisions.parent_id
WHERE doc.clientId = 'SOME_GUID'
AND (divisions.division_id IS NULL OR divisions.division_id IN ('SOME_GUID'));
and the results of explain are:
Total number of rows in document: 6720 Total number of rows in mapper: 6173
From what I've been able to gather I need to improve either the "type" or the "extra" to make the query faster. What can I do here?
Create table statements:
CREATE TABLE `document` (
`id` varchar(36) NOT NULL,
`addedBy` varchar(255) DEFAULT NULL,
`addedDate` datetime NOT NULL,
`editedBy` varchar(255) DEFAULT NULL,
`editedDate` datetime NOT NULL,
`deleted` bit(1) DEFAULT NULL,
`clientId` varchar(36) NOT NULL,
`departmentId` varchar(36) DEFAULT NULL,
`documentParentId` varchar(36) DEFAULT NULL,
`documentParent` varchar(50) DEFAULT NULL,
`fileId` varchar(255) DEFAULT NULL,
`fileUrl` varchar(600) DEFAULT NULL,
`documentName` varchar(500) NOT NULL,
`displayName` varchar(255) NOT NULL,
`documentId` varchar(45) DEFAULT NULL,
`notes` varchar(1000) DEFAULT NULL,
`visibility` varchar(45) NOT NULL DEFAULT 'PRIVATE',
`documentType` varchar(45) NOT NULL,
`restrictDelete` bit(1) NOT NULL,
`customData` text,
`releaseDate` datetime NOT NULL,
`expirationDate` datetime NOT NULL,
`isApproved` bit(1) NOT NULL DEFAULT b'0',
`userSupplier` varchar(36) DEFAULT NULL,
`complianceCertificateId` varchar(36) DEFAULT NULL,
`Status` varchar(50) DEFAULT 'NEUTRAL',
PRIMARY KEY (`id`),
KEY `idx_client` (`clientId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `multi_division_mapper` (
`parent_id` varchar(36) NOT NULL,
`division_id` varchar(36) NOT NULL,
PRIMARY KEY (`parent_id`,`division_id`),
KEY `idx_parent` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CodePudding user response:
I was able to get a more favorable EXPLAIN report in a test by creating the following index:
ALTER TABLE multi_division_mapper
DROP INDEX idx_parent,
ADD INDEX (division_id, parent_id);
I also dropped idx_parent
because it's redundant; it's a prefix of the primary key.
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | doc | NULL | ref | idx_client | idx_client | 110 | const | 1 | 100.00 | Using temporary |
1 | SIMPLE | divisions | NULL | ref | PRIMARY,division_id | division_id | 38 | const | 1 | 100.00 | Using where; Using index; Distinct |
The type: ref
is better than type: index
.
The query I tested is slightly different, but I believe it returns the same result:
SELECT DISTINCT doc.*
FROM document doc
LEFT JOIN multi_division_mapper divisions
ON doc.id = divisions.parent_id AND divisions.division_id in ('SOME_GUID')
WHERE doc.clientId = 'SOME_GUID'