title: How can I optimize this query further?
body: <p>This is mysql query to which I have added the indexes wherever possible. This is written by some previous coders and I need to optimize it as coming in slow logs. I have following stats for this query:</p>
  
  <pre><code>     rows_sent: 1
      query_time: 00:10:31
       lock_time: 00:00:00
   rows_examined: 628241089
              db: singledb_ed
  last_insert_id: 0
       insert_id: 0
       server_id: 1789791470
        sql_text: SELECT cs.delivery_consultant_id AS user_id, 
  interview.id AS interview_id,
  date(interview.interview_date) AS interview_date, 
  date(interview.mod_date) AS interview_mod_date,
  count(interview.id) AS kpi_item_count,
  cu.division_id,
  bu.organisation_id,
  ctu.team_id
   FROM es_shortlist AS cs
   LEFT JOIN tms_vacancies AS vac ON cs.vacancy_id = vac.id
   LEFT JOIN es_applications AS app ON app.vacancy_id = vac.id AND app.candidate_id = cs.candidate_id
   LEFT JOIN tms_interviews AS interview ON app.id = interview.application_id
   LEFT JOIN company_users AS cu ON cu.id = cs.delivery_consultant_id
   LEFT JOIN base_users AS bu ON bu.company_user_id = cs.delivery_consultant_id AND bu.company_id = cu.base_company_id
   LEFT JOIN company_team_users AS ctu ON ctu.user_id = cs.delivery_consultant_id
  WHERE (ctu.end_date is null
    AND cs.delivery_consultant_id ='53521')
    AND (ctu.end_date IS NULL)
    AND (ctu.status = 2)
  GROUP BY cs.delivery_consultant_id, ctu.team_id
  </code></pre>
  
  <p>EXPLAIN RESULT:</p>
  
  <pre><code>+----+-------------+-----------+--------+-----------------------------------------------------------+------------------------+---------+---------------------------------------+------+------------------------------------+
  | id | select_type | table     | type   | possible_keys                                             | key                    | key_len | ref                                   | rows | Extra                              |
  +----+-------------+-----------+--------+-----------------------------------------------------------+------------------------+---------+---------------------------------------+------+------------------------------------+
  |  1 | SIMPLE      | cs        | ref    | delivery_consultant_id                                    | delivery_consultant_id | 5       | const                                 | 1421 | Using temporary; Using filesort    |
  |  1 | SIMPLE      | vac       | eq_ref | PRIMARY                                                   | PRIMARY                | 4       | singledb_ed.cs.vacancy_id             |    1 | Using index                        |
  |  1 | SIMPLE      | app       | ref    | vacancy_id,candidate_stage_sync                           | vacancy_id             | 5       | singledb_ed.vac.id                    |    1 | Using where                        |
  |  1 | SIMPLE      | interview | ref    | application_id                                            | application_id         | 4       | singledb_ed.app.id                    |    1 | NULL                               |
  |  1 | SIMPLE      | cu        | eq_ref | PRIMARY                                                   | PRIMARY                | 4       | singledb_ed.cs.delivery_consultant_id |    1 | NULL                               |
  |  1 | SIMPLE      | bu        | ref    | company_user_id,company_id,getOrganisation,get_index_comp | company_user_id        | 5       | singledb_ed.cs.delivery_consultant_id |    1 | Using where                        |
  |  1 | SIMPLE      | ctu       | ref    | user_id,status,end_date                                   | end_date               | 6       | const                                 |   40 | Using index condition; Using where |
  +----+-------------+-----------+--------+-----------------------------------------------------------+------------------------+---------+---------------------------------------+------+------------------------------------+
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/J30y6.jpg" rel="nofollow noreferrer">Please see this image for EXPLAIN result of this query</a></p>
  
  <p>The CREATE INFO for all Tables</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>CREATE TABLE `es_shortlist` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `vacancy_id` int(11) DEFAULT NULL,
    `candidate_id` int(11) DEFAULT NULL,
    `shortlist_status` tinyint(2) DEFAULT NULL COMMENT '1 = open / 0 = closed / 2 = inprocess',
    `created_user_id` int(11) DEFAULT NULL,
    `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `entity_id` int(11) DEFAULT NULL,
    `base_company_id` int(11) DEFAULT NULL,
    `delivery_consultant_id` int(11) DEFAULT NULL,
    `is_cb_migrated` tinyint(2) DEFAULT NULL,
    `rs_app_id` int(11) DEFAULT NULL,
    `shortlist_source` tinyint(4) DEFAULT '1' COMMENT '1=''System'', 2=''Web Response''',
    `shortlist_source_id` int(11) DEFAULT NULL COMMENT '24=''job board'', 43=''indeed'', 44=''google'', ''other is direct''',
    PRIMARY KEY (`id`),
    KEY `delivery_consultant_id` (`delivery_consultant_id`),
    KEY `candidate_id` (`candidate_id`),
    KEY `vacancy_id` (`vacancy_id`),
    KEY `created_user_id` (`created_user_id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=39388 DEFAULT CHARSET=utf8;
  
  
  CREATE TABLE `tms_vacancies` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `base_company_id` int(11) NOT NULL,
    `organisation_id` int(11) DEFAULT NULL,
    `ref_id` int(11) DEFAULT NULL COMMENT 'ref for vacancy_id',
    `eb_reff_id` int(11) DEFAULT NULL,
    `team_id` int(11) DEFAULT '0' COMMENT 'attach id of tms_teams table',
    `function_id` int(11) DEFAULT NULL,
    `division_id` int(11) DEFAULT NULL,
    `qualified` tinyint(4) DEFAULT NULL,
    `lead_type` tinyint(4) DEFAULT '0' COMMENT '0 - was not a lead,1 - was a lead initially',
    `lead_category` tinyint(1) DEFAULT NULL COMMENT 'If in lead_type field value set as 1 then must be set 5 = Market Info, 6 = Hot Lead, 7 = Job Ad',
    `source` tinyint(2) DEFAULT NULL,
    `search_type` tinyint(4) DEFAULT NULL,
    `job_type` tinyint(4) DEFAULT NULL COMMENT '1-contract, 2-permanent and 3-part time etc\n',
    `reason` tinyint(4) DEFAULT NULL COMMENT 'Reason for vacancy, try to get that info at the time of creating lead\n\nEg.\nNew Role / \r\n\r\nReplacement - Resignation / Maternity / Sickness / Replacement - Internal Transfer / Replacement - Dismissed',
    `job_title` varchar(100) DEFAULT NULL,
    `account_id` int(11) DEFAULT NULL COMMENT 'look up - ACCOUNTS OBJECT (NOTICE. An account have to be created before adding a LEAD)',
    `start_date` timestamp NULL DEFAULT NULL,
    `end_date` timestamp NULL DEFAULT NULL,
    `base_salary` double DEFAULT NULL COMMENT 'This field is used to store min value of base salary for permanent type of job',
    `base_salary_upto` double DEFAULT NULL COMMENT 'This field is used to store max value of base salary for permanent type of job',
    `base_salary_to` double DEFAULT NULL COMMENT 'This field is used to store min value of total package for permanent type of job',
    `base_salary_to_upto` double DEFAULT NULL COMMENT 'This field is used to store max value of total package for permanent type of job',
    `salary_in_base_currency` double DEFAULT NULL COMMENT 'Base Salary converted into base currecy',
    `package_in_base_currency` double DEFAULT NULL COMMENT 'Total Package converted into base currecy',
    `application_form_status` tinyint(1) DEFAULT NULL COMMENT 'This Field will show if a vacancy has application created against it or not',
    `application_auto_reject_flag` tinyint(1) DEFAULT NULL,
    `application_reject_score` tinyint(3) DEFAULT NULL,
    `branch_office` int(11) DEFAULT NULL,
    `approval_status` tinyint(1) DEFAULT NULL COMMENT '0=&gt;Unapproved, 1=&gt;Approved',
    `role_id` int(11) DEFAULT NULL,
    `rs_sc_id` int(11) DEFAULT NULL,
    `rs_sc_name` varchar(255) DEFAULT NULL,
    `rs_sc_email` varchar(255) DEFAULT NULL,
    `endorsed_vacancy` tinyint(1) NOT NULL DEFAULT '0',
    `percentage_agreed` varchar(255) DEFAULT NULL,
    `fee_agreed` double DEFAULT NULL,
    `fee_agreed_in_base_currency` double DEFAULT NULL COMMENT 'Fee converted into base currecy',
    `excange_rate` double DEFAULT NULL COMMENT 'curreny exchange rate for current month',
    `fee_calculation_from` tinyint(2) DEFAULT NULL,
    `other_fee` varchar(55) DEFAULT NULL,
    `flag_qualified` varchar(55) DEFAULT NULL,
    `desc_background_cover` varchar(255) DEFAULT NULL,
    `currency` smallint(6) DEFAULT NULL,
    `bonus` varchar(250) DEFAULT NULL,
    `benefits` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `no_required` tinyint(4) DEFAULT NULL COMMENT 'how many positions are to fill',
    `placement_nos` int(11) DEFAULT '0' COMMENT 'no of placement done against vacancy_id',
    `summary` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
    `jobpost_summary` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
    `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1-poen, 2-closed, 3-placed',
    `priority` tinyint(1) DEFAULT NULL,
    `status_reason` tinyint(4) DEFAULT NULL,
    `3ff_status` tinyint(1) DEFAULT '0',
    `contract_length` tinyint(4) DEFAULT NULL,
    `base_rate` double DEFAULT NULL,
    `function` int(11) DEFAULT NULL,
    `duration` int(5) DEFAULT NULL,
    `expenses` varchar(255) DEFAULT NULL,
    `working_commitment` int(11) DEFAULT '100',
    `base_rate_to` double DEFAULT NULL,
    `daily_client_rate` double DEFAULT NULL COMMENT 'calculate if rate is hourly we save client rate * 8 otherwise as it is client rate',
    `daily_candidate_rate` double DEFAULT NULL COMMENT 'calculate if rate is hourly we save candidate rate * 8 otherwise as it is candidate rate',
    `client_rate_in_base_curreny` double DEFAULT NULL COMMENT 'Client rate converted into base currecy',
    `candidate_rate_in_base_currency` double DEFAULT NULL COMMENT 'Candidate rate converted into base currecy',
    `candidate_base_rate` double DEFAULT NULL,
    `rate_type` int(11) DEFAULT NULL,
    `exp_included` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
    `base_rate_currency` tinyint(4) DEFAULT NULL,
    `job_description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
    `client_job_description` text,
    `interview_template_id` int(11) DEFAULT NULL,
    `application_template_id` int(11) DEFAULT NULL,
    `last_mod_user_id` int(11) DEFAULT NULL,
    `mod_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `created_user_id` int(11) DEFAULT NULL,
    `created_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `closed_date` timestamp NULL DEFAULT NULL,
    `flag_post_jobboard` tinyint(1) DEFAULT '0',
    `flag_post_supplier` tinyint(1) DEFAULT '0',
    `flag_post_tpool` tinyint(1) DEFAULT '0',
    `flag_post_social` tinyint(1) DEFAULT '0',
    `flag_post_referral` tinyint(1) DEFAULT '0',
    `flag_relocate` int(11) DEFAULT '0' COMMENT '0=&gt; job will allow candidate that are not wiiling to relocate ,1=&gt; job will allow candidate that are willing to relocate',
    `rs_summary` text,
    `created_a_date` timestamp NULL DEFAULT NULL,
    `created_b_date` timestamp NULL DEFAULT NULL,
    `created_lead_date` timestamp NULL DEFAULT NULL,
    `is_teaser` tinyint(1) DEFAULT '0',
    `is_pitched` tinyint(4) NOT NULL DEFAULT '0',
    `is_block` tinyint(4) NOT NULL DEFAULT '0',
    `job_role_id` varchar(56) DEFAULT NULL COMMENT 'combination of jobrole_id and category_id(like 8117##8), ## is seperator',
    `search_by` tinyint(4) DEFAULT NULL COMMENT '1 : All of the Skill, 2: Any of the skill',
    `is_removed` tinyint(2) DEFAULT NULL,
    `is_third_rep` tinyint(1) DEFAULT '0',
    `is_cb_migrated` tinyint(4) DEFAULT '0',
    `is_eb_migrated` tinyint(4) DEFAULT '0',
    `is_posting_approved` tinyint(2) DEFAULT NULL,
    `is_posted` tinyint(2) DEFAULT NULL,
    `merge_account_id` int(11) DEFAULT NULL,
    `rs_vacancy_id` int(11) DEFAULT NULL,
    `migrated_date` datetime DEFAULT NULL,
    `is_onhold_closeplaced` tinyint(1) DEFAULT '0' COMMENT '0=Not, 1=Close Application of on hold &amp; closed placed jobs more than 30 days old',
    `cron_first_grade_a_job_dm` tinyint(4) NOT NULL,
    `cron_first_grade_a_job_acc` tinyint(4) NOT NULL,
    `fee_agreed_gbp` double DEFAULT NULL,
    `fee_agreed_usd` double DEFAULT NULL,
    `fee_agreed_eur` double DEFAULT NULL,
    `fee_cron_updated` tinyint(1) DEFAULT '0',
    `org_id_updated` tinyint(4) DEFAULT '0',
    `teamid_updated` tinyint(4) NOT NULL DEFAULT '0',
    `is_digital` int(4) NOT NULL DEFAULT '0' COMMENT '0=&gt;deactive,1=&gt;active',
    `is_new` tinyint(2) DEFAULT NULL COMMENT '1-New Job',
    PRIMARY KEY (`id`),
    KEY `base_company_id` (`base_company_id`),
    KEY `tms_vacancy_idx` (`function_id`,`status`,`flag_post_jobboard`,`flag_post_supplier`,`flag_post_tpool`),
    KEY `organisation_id` (`organisation_id`),
    KEY `status` (`status`),
    KEY `created_date` (`created_date`),
    KEY `job_close` (`organisation_id`,`status`,`created_date`),
    KEY `is_new` (`is_new`)
  ) ENGINE=InnoDB AUTO_INCREMENT=80611 DEFAULT CHARSET=utf8 COMMENT='Tabe for storing lead details by recruiters.\n\nA lead is a co';
  
  CREATE TABLE `es_applications` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `candidate_id` int(11) DEFAULT NULL,
    `base_company_id` int(11) DEFAULT NULL,
    `dm_id` int(11) DEFAULT NULL,
    `current_status` varchar(11) CHARACTER SET latin1 DEFAULT NULL,
    `is_active_batch` tinyint(11) DEFAULT NULL COMMENT '1 if current batch, 0 if batch deactive',
    `rejection_stage` tinyint(5) DEFAULT '0',
    `rejection_status` tinyint(5) DEFAULT NULL COMMENT 'reason for rejection',
    `rejection_status_reason` text CHARACTER SET latin1 COMMENT 'comments for rejection',
    `rejection_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `created_by` int(11) DEFAULT NULL,
    `to_user_id` int(11) DEFAULT NULL,
    `to_user_type` tinyint(4) DEFAULT NULL COMMENT 'dm/HR or Line Manager',
    `from_user_type` tinyint(5) DEFAULT '0' COMMENT 'dm/HR or Line Manager',
    `start_date` datetime DEFAULT NULL,
    `end_date` datetime DEFAULT NULL,
    `sharedby_user` int(11) DEFAULT NULL,
    `sharedby_date` timestamp NULL DEFAULT NULL,
    `created_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `mod_by` int(11) DEFAULT NULL,
    `mod_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `current_sub_status` tinyint(2) DEFAULT NULL,
    `application_id` int(11) DEFAULT '0',
    `app_stage` int(11) DEFAULT '0' COMMENT 'application stage',
    `enterprise_user_id` int(11) DEFAULT NULL,
    `enterprise_user_company_id` int(11) DEFAULT NULL,
    `cv_send_id` int(11) DEFAULT NULL,
    `edmgr_vacancy_id` int(11) DEFAULT NULL,
    `vacancy_id` int(11) DEFAULT NULL,
    `job_branch_id` int(11) DEFAULT NULL COMMENT 'Branch id of job',
    `talent_id` int(11) DEFAULT NULL,
    `app_status` int(4) DEFAULT NULL,
    `stage_reached` int(4) DEFAULT NULL,
    `stage_reached_date` datetime DEFAULT NULL,
    `summary` text CHARACTER SET latin1,
    `is_available` tinyint(5) DEFAULT '1' COMMENT '1 for available and 0 for not available',
    `candidate_type` tinyint(5) DEFAULT '2' COMMENT '2=permanent, 3= contract',
    `send_list_id` int(11) DEFAULT NULL COMMENT 'Primary id of es_endorsed_send_list table ',
    `is_direct` tinyint(1) DEFAULT '0' COMMENT '0 - not direct, 1 - direct application',
    `is_hot` tinyint(1) DEFAULT '0' COMMENT '1 - Hot, 0 - Not Hot',
    `organisation_id` int(11) DEFAULT NULL,
    `candidate_source` int(11) DEFAULT NULL,
    `candidate_source_id` int(11) DEFAULT NULL,
    `rs_app_id` int(11) DEFAULT NULL,
    `is_teaser` tinyint(1) DEFAULT '0',
    `branch_id` int(11) DEFAULT NULL,
    `merge_account_id` int(11) DEFAULT NULL,
    `entity_org_id` int(5) NOT NULL DEFAULT '0',
    `team_id` int(11) DEFAULT NULL,
    `orgid_teamid_updated` tinyint(4) DEFAULT '0',
    `application_type` smallint(4) DEFAULT NULL,
    `es_talent_profile` int(11) DEFAULT NULL,
    `entity_id` int(11) DEFAULT NULL,
    `is_sourcechain_account` int(11) DEFAULT NULL,
    `team_cron` tinyint(2) DEFAULT '0',
    `is_cb_migrated` tinyint(4) DEFAULT NULL,
    `is_eb_migrated` tinyint(5) NOT NULL DEFAULT '0',
    `is_tr_cron` tinyint(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `dm_id` (`dm_id`),
    KEY `current_status` (`current_status`,`cv_send_id`,`vacancy_id`),
    KEY `vacancy_id` (`vacancy_id`),
    KEY `candidate_stage_sync` (`candidate_id`,`app_status`,`current_sub_status`),
    KEY `start_date` (`base_company_id`,`dm_id`) USING BTREE,
    KEY `start_date_2` (`start_date`)
  ) ENGINE=InnoDB AUTO_INCREMENT=112073 DEFAULT CHARSET=utf8;
  
  CREATE TABLE `tms_interviews` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `base_company_id` int(11) NOT NULL,
    `stage` int(11) NOT NULL COMMENT 'whether it is 1st or 2nd or 3rd or final.',
    `sub_stage` int(11) DEFAULT NULL COMMENT 'it will hold total count of interview for same application',
    `ref_id` int(11) DEFAULT NULL COMMENT 'ref for interviewer_id',
    `interviewer_id` int(11) NOT NULL,
    `interview_date` datetime DEFAULT NULL COMMENT 'interview date .',
    `interview_time` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'interview time .',
    `interview_type` tinyint(2) DEFAULT NULL COMMENT '1=&gt;FTF, 2=&gt;telephonic',
    `status` int(11) DEFAULT NULL COMMENT 'could be accepted or rejected.',
    `status_date` datetime DEFAULT NULL,
    `status_reason` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'the reason why candidate is rejected at this stage.',
    `application_id` int(11) NOT NULL,
    `candidate_id` int(11) NOT NULL,
    `vacancy_id` int(11) NOT NULL,
    `account_id` int(11) NOT NULL,
    `account_table` varchar(45) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `client_feedback` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
    `candidate_feedback` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
    `cp_feedback` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `note` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
    `created_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `created_user_id` int(11) NOT NULL,
    `mod_date` timestamp NULL DEFAULT NULL,
    `last_mod_user_id` int(11) DEFAULT NULL,
    `reff_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `performance_cron_status` tinyint(1) NOT NULL DEFAULT '0',
    `time_zone` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
    `confirmation_flag` tinyint(2) DEFAULT '0',
    `interview_where` tinyint(4) DEFAULT NULL COMMENT 'used for where field in interview scheduling',
    `location` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
    `is_cancelled` int(1) NOT NULL DEFAULT '0',
    `no_of_reschedules` int(3) DEFAULT '0' COMMENT 'No of reschedules',
    `merge_account_id` int(11) DEFAULT NULL,
    `team_id` int(11) DEFAULT NULL,
    `team_cron` tinyint(2) DEFAULT '0',
    `is_cb_migrated` tinyint(4) DEFAULT NULL,
    `is_eb_migrated` tinyint(5) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `tms_interviews_idx` (`stage`,`interviewer_id`,`status`) USING BTREE,
    KEY `application_id` (`application_id`),
    KEY `candidate_id` (`candidate_id`),
    KEY `vacancy_id` (`vacancy_id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=60447 DEFAULT CHARSET=utf8 COMMENT='store interview stages for candidates for particular applica';
  
  CREATE TABLE `company_users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `dpn_sent` tinyint(4) DEFAULT '1' COMMENT 'Pending(1) | Sent(2) | Bounced(3) | Verified(4)',
    `email_verification_status` tinyint(4) DEFAULT NULL COMMENT '1:Good, 2: Bad',
    `dpn_due_date` datetime DEFAULT '2018-06-24 00:00:00',
    `base_company_id` int(11) DEFAULT NULL,
    `ref_id` int(11) DEFAULT NULL,
    `base_user_id` int(11) DEFAULT NULL,
    `branch_id` int(11) DEFAULT NULL,
    `client_portal_id` int(11) DEFAULT NULL COMMENT 'client portal id if user is of client portal',
    `crm_people_id` int(11) DEFAULT NULL COMMENT 'client user id-people_id',
    `education_level` int(11) DEFAULT NULL,
    `division_id` int(11) DEFAULT NULL,
    `job_level` int(11) DEFAULT NULL,
    `fname` varchar(50) DEFAULT NULL,
    `lname` varchar(50) DEFAULT NULL,
    `title` tinyint(4) DEFAULT NULL,
    `job_title` varchar(100) DEFAULT NULL,
    `sex` tinyint(1) DEFAULT NULL,
    `dob` datetime DEFAULT NULL,
    `country_id` int(4) DEFAULT NULL COMMENT 'no constraint should be made on this',
    `picture` varchar(255) DEFAULT NULL,
    `timezone` varchar(255) DEFAULT NULL,
    `function_id` int(11) DEFAULT NULL,
    `default_module_id` int(11) DEFAULT NULL,
    `status` tinyint(1) DEFAULT NULL COMMENT 'verified, activated, deactivated, approved',
    `endorsed_user` tinyint(1) DEFAULT '0',
    `endorsed_dm_status` int(11) DEFAULT '1',
    `created_user_id` int(11) DEFAULT NULL,
    `created_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `last_mod_user_id` int(11) DEFAULT NULL,
    `mod_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `is_onboarding_message` tinyint(1) DEFAULT '0' COMMENT '0=&gt;not onboarding,1=&gt;onboarded',
    `is_onboarding_connection` tinyint(1) DEFAULT '0' COMMENT '0=&gt;not onboarding,1=&gt;onboarded',
    `is_onboarding_profile` tinyint(1) DEFAULT '0' COMMENT '0=&gt;not onboarding,1=&gt;onboarded',
    `hub_integrated` tinyint(4) DEFAULT '0' COMMENT '0:NotIntegrated, 1:Integrated',
    `hub_id` bigint(20) DEFAULT NULL,
    `cloud_integrated` tinyint(4) DEFAULT '0',
    `cloud_contact_id` varchar(255) DEFAULT NULL,
    `cloud_integrated_eb` tinyint(4) DEFAULT '0',
    `cloud_contact_id_eb` varchar(255) DEFAULT NULL,
    `cloud_integrated_tr` tinyint(4) DEFAULT '0',
    `cloud_contact_id_tr` varchar(255) DEFAULT NULL,
    `is_cb_migrated` tinyint(4) DEFAULT '0',
    `is_eb_migrated` tinyint(5) NOT NULL DEFAULT '0',
    `eb_reff_id` int(11) DEFAULT NULL,
    `is_marketing_org_sync` tinyint(4) DEFAULT '2',
    `field_hub_sync` tinyint(4) DEFAULT '0' COMMENT '0:No;1:Yes',
    `is_jt_synced` tinyint(4) DEFAULT '0' COMMENT '0:no;1:yes',
    `cloud_integrated_vado` tinyint(4) DEFAULT '0',
    `cloud_contact_id_vado` int(11) DEFAULT NULL,
    `merge_account_id` int(11) DEFAULT NULL,
    `is_dm_emp_hub_sync` tinyint(4) DEFAULT '0',
    `flag_missing_name` tinyint(2) DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `status` (`status`,`client_portal_id`),
    KEY `mod_date` (`mod_date`),
    KEY `base_company_id` (`base_company_id`),
    KEY `created_user_id` (`created_user_id`),
    KEY `last_mod_user_id` (`last_mod_user_id`),
    KEY `hub_id` (`hub_id`),
    KEY `branch_id` (`branch_id`),
    KEY `endorsed_user_status` (`endorsed_user`,`status`),
    KEY `base_user_id` (`base_user_id`),
    KEY `endorsed_user` (`endorsed_user`),
    KEY `hub_integrated` (`hub_integrated`),
    KEY `dm_hub_employer` (`hub_id`,`is_dm_emp_hub_sync`,`endorsed_user`),
    KEY `cv_mod_created_date` (`created_date`,`is_eb_migrated`),
    KEY `job_level` (`job_level`),
    KEY `function_id` (`function_id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=555665 DEFAULT CHARSET=utf8;
  
  CREATE TABLE `base_users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `is_eb_migrated` tinyint(4) DEFAULT '0',
    `eb_reff_id` bigint(20) DEFAULT NULL,
    `account_number` varchar(255) DEFAULT NULL,
    `emp_uniq_id` varchar(255) DEFAULT NULL,
    `company_id` int(11) DEFAULT NULL,
    `company_user_id` int(11) DEFAULT NULL,
    `joblevel_id` int(11) DEFAULT NULL,
    `type` tinyint(4) DEFAULT NULL,
    `username` varchar(100) DEFAULT NULL,
    `password` char(32) DEFAULT NULL,
    `fname` varchar(45) DEFAULT NULL,
    `lname` varchar(45) DEFAULT NULL,
    `endorsed_user_email` varchar(255) DEFAULT NULL,
    `verification_code` varchar(100) DEFAULT NULL,
    `pass_recovery_code` varchar(100) DEFAULT NULL,
    `status` tinyint(4) DEFAULT NULL COMMENT 'verified, activated, deactivated, approved',
    `ip_addresses` text,
    `flag_eb` tinyint(1) DEFAULT NULL COMMENT '0 - NOT EB, 1 - EB User',
    `flag_ed_manual` tinyint(2) DEFAULT '0' COMMENT '0 - NOT READ, 1 - READ. Its for verifying whether the user/DM is aware of Endorsed client manual.',
    `ip_verify` tinyint(1) DEFAULT '0',
    `hubspot_flag` int(11) DEFAULT NULL COMMENT '1=active, 2=deactive',
    `organisation_id` int(11) DEFAULT NULL COMMENT 'xero organisation id for ed manager users',
    `last_mod_user_id` int(11) DEFAULT NULL,
    `mod_date` timestamp NULL DEFAULT NULL,
    `created_user_id` int(11) DEFAULT NULL,
    `created_date` datetime DEFAULT NULL,
    `rs_company_user_id` int(11) DEFAULT NULL,
    `rs_base_user_id` int(11) DEFAULT NULL,
    `firsttime_get_started` tinyint(1) NOT NULL DEFAULT '0',
    `is_cb_migrated` tinyint(4) DEFAULT '0',
    `zendesk_user_id` varchar(255) DEFAULT NULL,
    `is_sync_zendesk` tinyint(4) DEFAULT '0' COMMENT '0:not sync;1:synced;2:error',
    `zendesk_sync_date` timestamp NULL DEFAULT NULL,
    `flag_company` int(11) DEFAULT NULL COMMENT '1:CB;2:EB;3:TR',
    `merge_account_id` int(11) DEFAULT NULL,
    `interact_people_uid` varchar(255) DEFAULT NULL,
    `interact_people_id` varchar(255) DEFAULT NULL,
    `is_sync_to_interact` tinyint(4) DEFAULT '0' COMMENT '0:not synced;1:synced;2:xml-error',
    `sync_interact_date` timestamp NULL DEFAULT NULL,
    `update_current_team` tinyint(1) DEFAULT '0',
    `parsed_org_id` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `company_user_id` (`company_user_id`),
    KEY `org` (`organisation_id`),
    KEY `flag_company` (`flag_company`),
    KEY `company_id` (`company_id`,`company_user_id`),
    KEY `zendesk_user_id` (`zendesk_user_id`,`is_sync_zendesk`),
    KEY `zendesk_sync_date` (`zendesk_sync_date`),
    KEY `mod_date` (`mod_date`),
    KEY `sync_to_interact` (`is_sync_to_interact`,`interact_people_uid`,`status`) USING BTREE,
    KEY `sync_interact_date` (`sync_interact_date`,`mod_date`),
    KEY `status` (`account_number`,`status`) USING BTREE,
    KEY `username` (`status`,`username`(4)) USING BTREE,
    KEY `rs_comp_user` (`rs_company_user_id`,`company_id`,`is_eb_migrated`),
    KEY `getOrganisation` (`company_id`,`company_user_id`),
    KEY `username_2` (`username`),
    KEY `eb_reff_id` (`eb_reff_id`),
    KEY `get_index_comp` (`company_user_id`,`is_eb_migrated`)
  ) ENGINE=InnoDB AUTO_INCREMENT=557225 DEFAULT CHARSET=utf8;
  
  CREATE TABLE `company_team_users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) DEFAULT NULL,
    `base_company_id` int(11) DEFAULT NULL,
    `team_id` int(11) NOT NULL,
    `status` int(11) DEFAULT NULL,
    `start_date` datetime DEFAULT NULL,
    `end_date` datetime DEFAULT NULL,
    `last_mod_user_id` int(11) NOT NULL,
    `mod_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created_user_id` int(11) NOT NULL,
    `created_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `is_cb_migrated` tinyint(4) DEFAULT '0',
    `merge_account_id` int(11) DEFAULT NULL,
    `is_eb_migrated` tinyint(4) DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `teamID` (`team_id`),
    KEY `user_id` (`user_id`),
    KEY `status` (`status`),
    KEY `end_date` (`end_date`),
    KEY `getTeam` (`base_company_id`,`user_id`) USING BTREE
  ) ENGINE=InnoDB AUTO_INCREMENT=586851 DEFAULT CHARSET=utf8;</code></pre>
  </div>
  </div>
  </p>