How to optimize a select query with multiple left joins in a large database

Question

my query is

select  cp_emp_personal_detail.id
       ,cp_emp_personal_detail.emp_code
       ,cp_emp_personal_detail.emp_name
       ,cp_department.department
       ,GROUP_CONCAT(TIME(devicelogs_3_2018.LogDate) ORDER BY devicelogs_3_2018.LogDate) present
from    cp_emp_personal_detail
left join cp_emp_official_detail on cp_emp_official_detail.emp_id = cp_emp_personal_detail.id
left join devicelogs_3_2018 on devicelogs_3_2018.DeviceId = cp_emp_personal_detail.org_id and cp_emp_personal_detail.id AND date(devicelogs_3_2018.LogDate) = CURRENT_DATE()
left join cp_department on cp_emp_official_detail.department = cp_department.id
where cp_emp_personal_detail.org_id = 1
group by cp_emp_personal_detail.id

and database design is:

CREATE TABLE `cp_emp_personal_detail` (
  `id` int(11) NOT NULL,
  `org_id` int(11) NOT NULL,
  `emp_code` varchar(100) DEFAULT NULL,
  `emp_name` varchar(100) NOT NULL,
  `emp_guardian_name` varchar(100) DEFAULT NULL,
  `emp_dob` date NOT NULL,
  `emp_email` varchar(100) DEFAULT NULL,
  `emp_password` varchar(100) NOT NULL,
  `emp_phone` bigint(15) DEFAULT NULL,
  `emp_mobile` bigint(15) NOT NULL,
  `emp_emergency_no` int(11) DEFAULT NULL,
  `emp_address1` longtext NOT NULL,
  `emp_address2` longtext,
  `emp_city` int(11) NOT NULL,
  `emp_pan` varchar(30) NOT NULL,
  `emp_country` int(11) NOT NULL,
  `emp_state` int(11) NOT NULL,
  `emp_aadhar` varchar(30) DEFAULT NULL,
  `emp_tax_status` varchar(100) NOT NULL,
  `emp_pic` varchar(255) DEFAULT NULL,
  `emp_gender` varchar(50) NOT NULL,
  `emp_status` varchar(100) NOT NULL DEFAULT 'activate',
  `crm_status` varchar(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY LIST (org_id)
(
PARTITION P1 VALUES IN (1) ENGINE=InnoDB,
PARTITION P2 VALUES IN (2) ENGINE=InnoDB
);

CREATE TABLE `devicelogs_3_2018` (
  `DeviceLogId` bigint(20) NOT NULL,
  `DownloadDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `DeviceId` bigint(20) NOT NULL,
  `UserId` varchar(50) NOT NULL,
  `LogDate` timestamp NOT NULL DEFAULT '1970-12-31 18:30:01',
  `Direction` varchar(255) DEFAULT NULL,
  `AttDirection` varchar(255) DEFAULT NULL,
  `C1` varchar(255) DEFAULT NULL,
  `C2` varchar(255) DEFAULT NULL,
  `C3` varchar(255) DEFAULT NULL,
  `C4` varchar(255) DEFAULT NULL,
  `C5` varchar(255) DEFAULT NULL,
  `C6` varchar(255) DEFAULT NULL,
  `C7` varchar(255) DEFAULT NULL,
  `WorkCode` varchar(255) DEFAULT NULL,
  `hrapp_syncstatus` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `cp_department` (
  `id` int(11) NOT NULL,
  `org_id` int(11) NOT NULL,
  `department` varchar(20) NOT NULL,
  `depart_abb` varchar(10) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY LIST (org_id)
(
PARTITION P1 VALUES IN (1) ENGINE=InnoDB,
PARTITION P2 VALUES IN (2) ENGINE=InnoDB
);

CREATE TABLE `cp_emp_official_detail` (
  `id` int(11) NOT NULL,
  `emp_id` int(11) NOT NULL,
  `org_id` int(11) NOT NULL,
  `joining_date` date DEFAULT NULL,
  `confirmation_periode` int(11) DEFAULT NULL,
  `job_type` varchar(100) DEFAULT NULL,
  `payment_mode` varchar(100) DEFAULT NULL,
  `department` int(11) DEFAULT NULL,
  `acc_no` int(11) DEFAULT NULL,
  `designation` int(11) DEFAULT NULL,
  `ess_status` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY LIST (org_id)
(
PARTITION P1 VALUES IN (1) ENGINE=InnoDB,
PARTITION P2 VALUES IN (2) ENGINE=InnoDB
);

can someone explain me how to optimize th query currently it is taking 3 to 4 min to execute with more than 8k records

You do not have any indexes? [Add some.](https://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html) They are basically the most important tool you have to both ensure data integrity (primary/unique/foreign keys) and performance. Also remove the partitioning (as it has some requirements on the indexes, and will probably generate more problems here than it solves here, when you e.g. try to add a proper primary key). Partitioning is a tool for specific administration task, not to improve performance. — Solarflare, Mar 14 '18 at 13:20
thanks for your reply but idont know how and on which columns i have to apply indexes can you tell me about that. — vicky agrawal, Mar 15 '18 at 04:54
Start by reading about indexes, and especially primary keys. You really really need to know about this stuff. Then [remove the partitions](https://stackoverflow.com/a/18002014/6248528) (as you probably cannot add primary keys because of your partitions). Then [add primary keys](https://stackoverflow.com/q/5132923/6248528), probably for the columns id, id, and (for devicelogs_3_2018) DeviceLogId. "Probably", as we do not know your data model; the correct primary key might e.g. include `org_id` or `emp_id`. But since you read about primary keys in step 1, you can probably decide which it is. — Solarflare, Mar 15 '18 at 12:08

How to optimize a select query with multiple left joins in a large database

0 Answers0