I have a query in my database that takes 25 seconds to return results, which is too long. It seems like it should be pretty simple. Two tables; the main table (document) is a standard table with some data columns, and the connection table is a mapping table with only two columns (parent_id, division_id). Previously there was no index on the mapping table, so I added an index and changed the "interpretation" to include the index, but it seems to have no impact on performance.
The query is as follows:
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'));
The result of the explanation is:
Total number of lines in document: 6720 Total number of rows in mapper: 6173
Based on the information I gathered, I need to improve "type" or "extra" to make the query faster. What can I do here?
Create table statement:
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;
I was able to get a more favorable EXPLAIN report in my tests by creating the following index:
I also removed
idx_parent
because it was redundant; it is a prefix for the primary key.