Comments - slow select
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
Only with details. Table structure via SHOW CREATE TABLE, the query, and ANALYZE FORMAT=JSON {query} output.
Have you tried ANALYZE TABLE tbl PERSISTENT FOR ALL; for the tables of the query? Did it help?
In my query, I'm using the following tables:
telemarket_client client_phone client_address client_email_address client_role
Table: telemarket_client Create Table: CREATE TABLE `telemarket_client` ( `id` int(11) NOT NULL AUTO_INCREMENT, `deleted` bit(1) NOT NULL, `createdDate` datetime DEFAULT NULL, `updatedDate` datetime DEFAULT NULL, `clientType` varchar(255) DEFAULT NULL, `comboString1` varchar(255) DEFAULT NULL, `comboString2` varchar(255) DEFAULT NULL, `comboString3` varchar(255) DEFAULT NULL, `companyName` varchar(255) DEFAULT NULL, `date1` date DEFAULT NULL, `date2` date DEFAULT NULL, `date3` date DEFAULT NULL, `dateTime1` datetime DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `externalId` int(11) DEFAULT NULL, `fax` varchar(255) DEFAULT NULL, `firstName` varchar(255) DEFAULT NULL, `integer1` int(11) DEFAULT NULL, `integer10` int(11) DEFAULT NULL, `integer11` int(11) DEFAULT NULL, `integer2` int(11) DEFAULT NULL, `integer3` int(11) DEFAULT NULL, `integer4` int(11) DEFAULT NULL, `integer5` int(11) DEFAULT NULL, `integer6` int(11) DEFAULT NULL, `integer7` int(11) DEFAULT NULL, `integer8` int(11) DEFAULT NULL, `integer9` int(11) DEFAULT NULL, `lastName` varchar(255) DEFAULT NULL, `legalAddress` varchar(255) DEFAULT NULL, `mob` varchar(255) DEFAULT NULL, `parentId` int(11) NOT NULL, `personalOperatorId` int(11) DEFAULT NULL, `physicalAddress` varchar(255) DEFAULT NULL, `secondaryTel` varchar(255) DEFAULT NULL, `string1` varchar(760) DEFAULT NULL, `string10` varchar(255) DEFAULT NULL, `string11` varchar(255) DEFAULT NULL, `string12` varchar(255) DEFAULT NULL, `string13` varchar(255) DEFAULT NULL, `string14` varchar(255) DEFAULT NULL, `string15` varchar(255) DEFAULT NULL, `string16` varchar(255) DEFAULT NULL, `string17` varchar(255) DEFAULT NULL, `string18` varchar(255) DEFAULT NULL, `string19` varchar(255) DEFAULT NULL, `string2` varchar(255) DEFAULT NULL, `string20` varchar(255) DEFAULT NULL, `string21` varchar(255) DEFAULT NULL, `string3` varchar(255) DEFAULT NULL, `string4` varchar(255) DEFAULT NULL, `string5` varchar(255) DEFAULT NULL, `string6` varchar(255) DEFAULT NULL, `string7` varchar(255) DEFAULT NULL, `string8` varchar(255) DEFAULT NULL, `string9` varchar(255) DEFAULT NULL, `tel` varchar(255) DEFAULT NULL, `clientRole_id` int(11) DEFAULT NULL, `company_id` int(11) DEFAULT NULL, `type_id` int(11) DEFAULT NULL, `decimal1` decimal(16,2) DEFAULT NULL, `decimal2` decimal(16,2) DEFAULT NULL, `decimal3` decimal(16,2) DEFAULT NULL, `decimal4` decimal(16,2) DEFAULT NULL, `decimal5` decimal(16,2) DEFAULT NULL, `externalIdStr` varchar(255) DEFAULT NULL, `statusInteger1` int(11) DEFAULT NULL, `statusInteger2` int(11) DEFAULT NULL, `statusInteger3` int(11) DEFAULT NULL, `statusInteger4` int(11) DEFAULT NULL, `comboString4` varchar(255) DEFAULT NULL, `comboString5` varchar(255) DEFAULT NULL, `comboString6` varchar(255) DEFAULT NULL, `comboString7` varchar(255) DEFAULT NULL, `comboString8` varchar(255) DEFAULT NULL, `date10` date DEFAULT NULL, `date11` date DEFAULT NULL, `date12` date DEFAULT NULL, `date13` date DEFAULT NULL, `date4` date DEFAULT NULL, `date5` date DEFAULT NULL, `date6` date DEFAULT NULL, `date7` date DEFAULT NULL, `date8` date DEFAULT NULL, `date9` date DEFAULT NULL, `decimal10` decimal(16,2) DEFAULT NULL, `decimal11` decimal(16,2) DEFAULT NULL, `decimal12` decimal(16,2) DEFAULT NULL, `decimal13` decimal(16,2) DEFAULT NULL, `decimal14` decimal(16,2) DEFAULT NULL, `decimal15` decimal(16,2) DEFAULT NULL, `decimal6` decimal(16,2) DEFAULT NULL, `decimal7` decimal(16,2) DEFAULT NULL, `decimal8` decimal(16,2) DEFAULT NULL, `decimal9` decimal(16,2) DEFAULT NULL, `integer12` int(11) DEFAULT NULL, `integer13` int(11) DEFAULT NULL, `integer14` int(11) DEFAULT NULL, `integer15` int(11) DEFAULT NULL, `integer16` int(11) DEFAULT NULL, `integer17` int(11) DEFAULT NULL, `integer18` int(11) DEFAULT NULL, `string22` varchar(255) DEFAULT NULL, `string23` varchar(255) DEFAULT NULL, `string24` varchar(255) DEFAULT NULL, `string25` varchar(255) DEFAULT NULL, `string26` varchar(255) DEFAULT NULL, `string27` varchar(255) DEFAULT NULL, `string28` varchar(255) DEFAULT NULL, `string29` varchar(255) DEFAULT NULL, `string30` varchar(255) DEFAULT NULL, `string31` varchar(255) DEFAULT NULL, `timeZone` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK52C4E1C49D09C740` (`company_id`), KEY `FK52C4E1C41A64FA05` (`type_id`), KEY `FK52C4E1C4DF4615D4` (`clientRole_id`), KEY `ind_lastName` (`lastName`), KEY `ind_firstName` (`firstName`), KEY `ind_identifiers` (`string2`,`string1`(255)), KEY `ind_deleted` (`deleted`), KEY `ind_string3` (`string3`), CONSTRAINT `FK52C4E1C41A64FA05` FOREIGN KEY (`type_id`) REFERENCES `telemarket_client_type` (`id`), CONSTRAINT `FK52C4E1C49D09C740` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`), CONSTRAINT `FK52C4E1C4DF4615D4` FOREIGN KEY (`clientRole_id`) REFERENCES `client_role` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=19549277 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
Table: client_phone Create Table: CREATE TABLE `client_phone` ( `id` int(11) NOT NULL AUTO_INCREMENT, `deleted` bit(1) NOT NULL, `createdDate` datetime /* mariadb-5.3 */ DEFAULT NULL, `updatedDate` datetime /* mariadb-5.3 */ DEFAULT NULL, `active` bit(1) NOT NULL, `disabled` bit(1) NOT NULL, `disabledCallStatus` varchar(255) DEFAULT NULL, `disabledDate` datetime /* mariadb-5.3 */ DEFAULT NULL, `ignorePhone` bit(1) NOT NULL, `importId` bigint(20) DEFAULT NULL, `permanent` bit(1) NOT NULL, `phoneNumber` varchar(255) DEFAULT NULL, `sortIndex` int(11) NOT NULL, `type` varchar(255) DEFAULT NULL, `client_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FKBED94FFA9461E9FA` (`client_id`), KEY `ind_phoneNumber` (`phoneNumber`), KEY `ind_sortIndex` (`sortIndex`), KEY `ind_disabled` (`disabled`), KEY `ind_client_id` (`client_id`), CONSTRAINT `FKBED94FFA9461E9FA` FOREIGN KEY (`client_id`) REFERENCES `telemarket_client` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=26796416 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
Table: client_address Create Table: CREATE TABLE `client_address` ( `id` int(11) NOT NULL AUTO_INCREMENT, `deleted` bit(1) NOT NULL, `createdDate` datetime /* mariadb-5.3 */ DEFAULT NULL, `updatedDate` datetime /* mariadb-5.3 */ DEFAULT NULL, `active` bit(1) NOT NULL, `address` varchar(255) DEFAULT NULL, `permanent` bit(1) NOT NULL, `sortIndex` int(11) NOT NULL, `type` int(11) DEFAULT NULL, `client_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK4CDB3B809461E9FA` (`client_id`), CONSTRAINT `FK4CDB3B809461E9FA` FOREIGN KEY (`client_id`) REFERENCES `telemarket_client` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4494913 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
Table: client_email_address Create Table: CREATE TABLE `client_email_address` ( `id` int(11) NOT NULL AUTO_INCREMENT, `deleted` bit(1) NOT NULL, `createdDate` datetime /* mariadb-5.3 */ DEFAULT NULL, `updatedDate` datetime /* mariadb-5.3 */ DEFAULT NULL, `active` bit(1) NOT NULL, `email` varchar(255) DEFAULT NULL, `permanent` bit(1) NOT NULL, `sortIndex` int(11) NOT NULL, `client_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK11DAF8DD9461E9FA` (`client_id`), CONSTRAINT `FK11DAF8DD9461E9FA` FOREIGN KEY (`client_id`) REFERENCES `telemarket_client` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2252521 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
Table: client_role Create Table: CREATE TABLE `client_role` ( `TYPE` varchar(31) NOT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, `deleted` bit(1) NOT NULL, `createdDate` datetime /* mariadb-5.3 */ DEFAULT NULL, `updatedDate` datetime /* mariadb-5.3 */ DEFAULT NULL, `defaultRole` bit(1) NOT NULL, `multiRole` bit(1) NOT NULL, `roleName` varchar(255) DEFAULT NULL, `company_id` int(11) DEFAULT NULL, `formConfiguration_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FKF5A4EC6A9D09C740` (`company_id`), KEY `FKF5A4EC6AB618FBAF` (`formConfiguration_id`), CONSTRAINT `FKF5A4EC6A9D09C740` FOREIGN KEY (`company_id`) REFERENCES `company` (`id`), CONSTRAINT `FKF5A4EC6AB618FBAF` FOREIGN KEY (`formConfiguration_id`) REFERENCES `form_configuration` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
Part of the query looks like this
Select client.id clientId, client.deleted clientDeleted, client.parentId clientParentId, client.externalId clientExternalId, client.externalIdStr clientExternalIdStr, client.firstName clientFirstName, client.lastName clientLastName, clientType clientType, client.company_id companyId, client.clientRole_id clientRoleId, role.TYPE clientRoleType, role.defaultRole clientRoleDefaultRole, role.multiRole clientRoleMultiRole, client.personalOperatorId clientPersonalOperatorId, client.tel clientTel, client.mob clientMob, client.email clientEmail, client.fax clientFax, client.companyName clientCompanyName, client.physicalAddress clientPhysicalAddress, client.legalAddress clientLegalAddress, secondaryTel clientSecondaryTel, client.string1 clientString1, client.string2 clientString2, client.string3 clientString3, client.string4 clientString4, client.string5 clientString5, client.string6 clientString6, client.string7 clientString7, client.string8 clientString8, client.string9 clientString9, client.string10 clientString10, client.string11 clientString11, client.string12 clientString12, client.string13 clientString13, client.string14 clientString14, client.string15 clientString15, client.string16 clientString16, client.string17 clientString17, client.string18 clientString18, client.string19 clientString19, client.string20 clientString20, client.string21 clientString21, client.string22 clientString22, client.string23 clientString23, client.string24 clientString24, client.string25 clientString25, client.string26 clientString26, client.string27 clientString27, client.string28 clientString28, client.string29 clientString29, client.string30 clientString30, client.string31 clientString31, client.integer1 clientInteger1, client.integer2 clientInteger2, client.integer3 clientInteger3, client.integer4 clientInteger4, client.integer5 clientInteger5, client.integer6 clientInteger6, client.integer7 clientInteger7, client.integer8 clientInteger8, client.integer9 clientInteger9, client.integer10 clientInteger10, client.integer11 clientInteger11, client.integer12 clientInteger12, client.integer13 clientInteger13, client.integer14 clientInteger14, client.integer15 clientInteger15, client.integer16 clientInteger16, client.integer17 clientInteger17, client.integer18 clientInteger18, client.statusInteger1 clientStatusInteger1, client.statusInteger2 clientStatusInteger2, client.statusInteger3 clientStatusInteger3, client.statusInteger4 clientStatusInteger4, client.comboString1 clientComboString1, client.comboString2 clientComboString2, client.comboString3 clientComboString3, client.comboString4 clientComboString4, client.comboString5 clientComboString5, client.comboString6 clientComboString6, client.comboString7 clientComboString7, client.comboString8 clientComboString8, client.date1 clientDate1, client.date2 clientDate2, client.date3 clientDate3, client.date4 clientDate4, client.date5 clientDate5, client.date6 clientDate6, client.date7 clientDate7, client.date8 clientDate8, client.date9 clientDate9, client.date10 clientDate10, client.date11 clientDate11, client.date12 clientDate12, client.date13 clientDate13, client.dateTime1 clientDateTime1, client.decimal1 clientDecimal1, client.decimal2 clientDecimal2, client.decimal3 clientDecimal3, client.decimal4 clientDecimal4, client.decimal5 clientDecimal5, client.decimal6 clientDecimal6, client.decimal7 clientDecimal7, client.decimal8 clientDecimal8, client.decimal9 clientDecimal9, client.decimal10 clientDecimal10, client.decimal11 clientDecimal11, client.decimal12 clientDecimal12, client.decimal13 clientDecimal13, client.decimal14 clientDecimal14, client.decimal15 clientDecimal15, client.timeZone clientTimeZone, phone.id phoneId, phone.phoneNumber phoneNumber, phone.active phoneActive, phone.sortIndex phoneSortIndex, phone.deleted phoneDeleted, phone.disabled phoneDisabled, phone.ignorePhone phoneIgnore, phone.type phoneType, phone.disabledCallStatus disabledCallStatus, email.id emailId, email.email emailAddress, email.deleted emailDeleted, email.active emailActive, email.sortIndex emailSortIndex, address.id addressId, address.address address, address.deleted addressDeleted, address.active addressActive, address.sortIndex addressSortIndex, address.type addressType from telemarket_client client inner join (select id from telemarket_client where company_id=1 and ((id=3999014) or (id=3999015) or (id=3999012) or (id=3999013) or (id=3999010) or (id=3999011) or (id=3999008) or (id=3999009) or (id=3999022) or (id=3999023) or (id=3999020) or (id=3999021) or (id=3999018) or (id=3999019) or (id=3999016) or (id=3999017) or (id=3999030) or (id=3999031) or (id=3999028) or (id=3999029) or (id=3999026) or (id=3999027) or (id=3999024) or (id=3999025) or (id=3999038) or (id=3999039) or (id=3999036) or (id=3999037) or (id=3999034) or (id=3999035) or (id=3999032) or (id=3999033) or (id=3998982) or (id=3998983) or (id=3998980) or (id=3998981) or (id=3998978) or (id=3998979) or (id=3998976) or (id=3998977) or (id=3998990) or (id=3998991) or (id=3998988) or (id=3998989) or (id=3998986) or (id=3998987) or (id=3998984) or (id=3998985) or (id=3998998) or (id=3998999) or (id=3998996) or (id=3998997) or (id=3998994) or (id=3998995) or (id=3998992) or (id=3998993) or (id=3999006) or (id=3999007) or (id=3999004) or (id=3999005) or (id=3999002) or (id=3999003) or (id=3999000) or (id=3999001) or (id=3999078) or (id=3999079) or (id=3999076) or (id=3999077) or (id=3999074) or (id=3999075) or (id=3999072) or (id=3999073) or (id=3999086) or (id=3999087) or (id=3999084) or (id=3999085) or (id=3999082) or (id=3999083) or (id=3999080) or (id=3999081) or (id=3999094) or (id=3999095) or (id=3999092) or (id=3999093) or (id=3999090) or (id=3999091) or (id=3999088) or (id=3999089) or (id=3999102) or (id=3999103) or (id=3999100) or (id=3999101) or (id=3999098) or (id=3999099) or (id=3999096) or (id=3999097) or (id=3999046) or (id=3999047) or (id=3999044) or (id=3999045) or (id=3999042) or (id=3999043) or (id=3999040) or (id=3999041) or (id=3999054) or (id=3999055) or (id=3999052) or (id=3999053) or (id=3999050) or (id=3999051) or (id=3999048) or (id=3999049) or (id=3999062) or (id=3999063) or (id=3999060) or (id=3999061) or (id=3999058) or (id=3999059) or (id=3999056) or (id=3999057) or (id=3999070) or (id=3999071) or (id=3999068) or (id=3999069) or (id=3999066) or (id=3999067) or (id=3999064) or (id=3999065) or (id=3999142) or (id=3999143) or (id=3999140) or (id=3999141) or (id=3999138) or (id=3999139) or (id=3999136) or (id=3999137) or (id=3999150) or (id=3999151) or (id=3999148) or (id=3999149) or (id=3999146) or (id=3999147) or (id=3999144) or (id=3999145) or (id=3999158) or (id=3999159) or (id=3999156) or (id=3999157) or (id=3999154) or (id=3999155) or (id=3999152) or (id=3999153) or (id=3999166) or (id=3999167) or (id=3999164) or (id=3999165) or (id=3999162) or (id=3999163) or (id=3999160) or (id=3999161) or (id=3999110) or (id=3999111) or (id=3999108) or (id=3999109) or (id=3999106) or (id=3999107) or (id=3999104) or (id=3999105) or (id=3999118) or (id=3999119) or (id=3999116) or (id=3999117) or (id=3999114) or (id=3999115) or (id=3999112) or (id=3999113) or (id=3999126) or (id=3999127) or (id=3999124) or (id=3999125) or (id=3999122) or (id=3999123) or (id=3999120) or (id=3999121) or (id=3999134) or (id=3999135) or (id=3999132) or (id=3999133) or (id=3999130) or (id=3999131) or (id=3999128) or (id=3999129) or (id=3999206) or (id=3999207) or (id=3999204) or (id=3999205) or (id=3999202) or (id=3999203) or (id=3999200) or (id=3999201) or (id=3999214) or (id=3999215) or (id=3999212) or (id=3999213) or (id=3999210) or (id=3999211) or (id=3999208) or (id=3999209) or (id=3999222) or (id=3999223) or (id=3999220) or (id=3999221) or (id=3999218) or (id=3999219) or (id=3999216) or (id=3999217) or (id=3999230) or (id=3999231) or (id=3999228) or (id=3999229) or (id=3999226) or (id=3999227) or (id=3999224) or (id=3999225) or (id=3999174) or (id=3999175) or (id=3999172) or (id=3999173) or (id=3999170) or (id=3999171) or (id=3999168) or (id=3999169) or (id=3999182) or (id=3999183) or (id=3999180) or (id=3999181) or (id=3999178) or (id=3999179) or (id=3999176) or (id=3999177) or (id=3999190) or (id=3999191) or (id=3999188) or (id=3999189) or (id=3999186) or (id=3999187) or (id=3999184) or (id=3999185) or (id=3999198) or (id=3999199) or (id=3999196) or (id=3999197) or (id=3999194) or (id=3999195) or (id=3999192) or (id=3999193) or (id=3998758) or (id=3998759) or (id=3998756) or (id=3998757) or (id=3998754) or (id=3998755) or (id=3998752) or (id=3998753) or (id=3998766) or (id=3998767) or (id=3998764) or (id=3998765) or (id=3998762) or (id=3998763) or (id=3998760) or (id=3998761) or (id=3998774) or (id=3998775) or (id=3998772) or (id=3998773) or (id=3998770)) limit 0, 277 ) limitedClients on client.id=limitedClients.id left join client_phone phone on client.id = phone.client_id left join client_address address on client.id = address.client_id left join client_email_address email on client.id = email.client_id left join client_role role on client.clientRole_id = role.id;
Analysis of these tables has been conducted with the ANALYZE TABLE statement, showing that all tables are OK.
ANALYZE TABLE telemarket_client, client_phone, client_address, client_email_address, client_role; +---------------------------------+---------+----------+----------+
+---------------------------------+---------+----------+----------+
+---------------------------------+---------+----------+----------+
The query's EXPLAIN output for a dataset of 2968 IDs indicates how MySQL plans to execute the query, showing the use of various keys and indexes for optimization.
+------+-------------+-------------------+--------+----------------------------------+--------------------+---------+---------------------------------+------+--------------------------+
+------+-------------+-------------------+--------+----------------------------------+--------------------+---------+---------------------------------+------+--------------------------+
+------+-------------+-------------------+--------+----------------------------------+--------------------+---------+---------------------------------+------+--------------------------+
When the telemarket_client table contains a large number of records (more than 3 million), the query for 30,000 IDs runs very slowly. However, with fewer records (up to 1.5 million), it executes much faster.
The main concern is that this query, which used to run within 5 minutes on MariaDB 5 with telemarket_client having over 3 million records, now takes hours after updating to a newer version of MariaDB. This significant performance degradation is the core issue.