title: Query optimization of Mysql stored-procedure
body: <p>I'm working in a <strong>RDBMS (MYSQL)</strong> where we have approx <strong>15 to 20</strong> tables major tables having more than <strong>4 lacks rows and 70 columns</strong> in each table.while retrieving data most of the time i have to use the <strong>left join</strong> that results delaying in operation. We use stored procedures kindly suggest any best method to fast operation.</p>
  
  <pre><code>CREATE TABLE `patient_data` (
      `p_id` INT(11) NOT NULL AUTO_INCREMENT,
      `entry_date` DATETIME NOT NULL COMMENT 'Registration Date',
      `hr_id` INT(11) NOT NULL,
      `ua_id` INT(11) NOT NULL,
      `mrn1` VARCHAR(20) NOT NULL COMMENT 'mrn initial',
      `mrn2` INT(20) NOT NULL DEFAULT '0' COMMENT 'mrn counter',
      `title` VARBINARY(50) NULL DEFAULT NULL,
      `fname` VARBINARY(50) NOT NULL,
      `lname` VARBINARY(50) NOT NULL,
      `mname` VARBINARY(50) NULL DEFAULT NULL,
      `suffix` VARBINARY(50) NULL DEFAULT NULL,
      `dob` VARBINARY(50) NOT NULL,
      `pat_photo` VARBINARY(50) NULL DEFAULT NULL,
      `blood_group` VARBINARY(50) NULL DEFAULT NULL,
      `street` VARBINARY(255) NULL DEFAULT NULL,
      `postal_code` VARBINARY(50) NULL DEFAULT NULL,
      `city` VARBINARY(50) NULL DEFAULT NULL,
      `state` VARBINARY(50) NULL DEFAULT NULL,
      `country` VARBINARY(50) NULL DEFAULT NULL,
      `drivers_license` VARBINARY(50) NULL DEFAULT NULL,
      `ss` VARBINARY(20) NULL DEFAULT NULL COMMENT 'adhar no',
      `occupation` VARBINARY(50) NULL DEFAULT NULL,
      `home_phone` VARBINARY(50) NULL DEFAULT NULL,
      `work_phone` VARBINARY(50) NULL DEFAULT NULL,
      `mobile_no` VARBINARY(50) NULL DEFAULT NULL,
      `emergency_no` VARBINARY(50) NULL DEFAULT NULL,
      `m_status` VARBINARY(50) NULL DEFAULT NULL,
      `emergency_contact` VARBINARY(50) NULL DEFAULT NULL,
      `sex` VARBINARY(50) NOT NULL,
      `email` VARBINARY(50) NULL DEFAULT NULL,
      `alternate_email` VARBINARY(50) NULL DEFAULT NULL,
      `race` VARBINARY(50) NULL DEFAULT NULL,
      `financial` VARBINARY(50) NULL DEFAULT NULL,
      `ethnicity` VARBINARY(50) NULL DEFAULT NULL,
      `interpreter` VARBINARY(50) NULL DEFAULT NULL,
      `migrantseasonal` VARBINARY(50) NULL DEFAULT NULL,
      `family_size` VARBINARY(50) NULL DEFAULT NULL,
      `monthly_income` VARBINARY(50) NULL DEFAULT NULL,
      `homeless` VARBINARY(50) NULL DEFAULT NULL,
      `financial_review` VARBINARY(50) NULL DEFAULT NULL,
      `referral_source` VARBINARY(30) NULL DEFAULT NULL,
      `vfc` VARBINARY(50) NULL DEFAULT NULL,
      `admit_flag` INT(2) NOT NULL DEFAULT '0' COMMENT '0-Not Admit,1-admitted',
      `select_reason` VARCHAR(20) NULL DEFAULT NULL,
      `delete_reason` VARCHAR(150) NULL DEFAULT NULL,
      `relation_with_patient` VARBINARY(50) NULL DEFAULT NULL,
      `relative_name` VARBINARY(100) NULL DEFAULT NULL,
      `referred_by` VARBINARY(50) NULL DEFAULT NULL,
      `referred_no` VARBINARY(20) NULL DEFAULT NULL,
      `flag` VARCHAR(2) NOT NULL DEFAULT 'c',
      `update_date` DATETIME NULL DEFAULT NULL COMMENT 'Last Updation of Date',
      `update_ua_id` INT(11) NOT NULL DEFAULT '0',
      `tpa` VARCHAR(50) NULL DEFAULT NULL,
      `age` VARBINARY(50) NULL DEFAULT NULL,
      `opd_no` VARBINARY(50) NULL DEFAULT NULL,
      `duplicate_flag` VARBINARY(50) NULL DEFAULT NULL,
      `department` VARBINARY(50) NULL DEFAULT NULL,
      `patient_type` VARBINARY(50) NULL DEFAULT NULL,
      `revisit` INT(2) NULL DEFAULT '0',
      `simul_flag` INT(2) NULL DEFAULT '0' COMMENT '1= duplicate(simulation)',
      `tags` VARCHAR(50) NULL DEFAULT NULL,
      `balance_amount` FLOAT NULL DEFAULT NULL,
      `opd_visit_counter` INT(50) NULL DEFAULT NULL,
      `patient_camera_pic` VARCHAR(255) NULL DEFAULT NULL,
      `baby_birth_time` VARBINARY(50) NULL DEFAULT NULL,
      `location` VARBINARY(50) NULL DEFAULT NULL,
      `aadhaar_no` VARBINARY(50) NULL DEFAULT NULL,
      `old_uhid` VARCHAR(50) NULL DEFAULT NULL,
      `er_id` INT(11) NULL DEFAULT NULL COMMENT 'For current er id',
      `patient_pancardno` VARBINARY(50) NULL DEFAULT NULL,
      `district` VARBINARY(50) NULL DEFAULT NULL,
      `religion` VARBINARY(50) NULL DEFAULT NULL,
      `vulnerable_type` INT(11) NULL DEFAULT '0',
      `vulnerable_data` VARCHAR(255) NULL DEFAULT NULL,
      `weight` FLOAT NULL DEFAULT NULL,
      `insurance_type` INT(11) NULL DEFAULT NULL,
      PRIMARY KEY (`p_id`),
      INDEX `hr_id` (`hr_id`),
      INDEX `u_id` (`ua_id`)
  )
  COLLATE='utf8_general_ci'
  ENGINE=InnoDB
  
  ;
  </code></pre>
  
  <p><strong>This is my stored routines</strong></p>
  
  <pre><code>BEGIN
  SELECT pd.p_id, er.er_id, pd.flag, pd.delete_reason, pd.select_reason,
         AES_DECRYPT(pd.fname, encryptkey) AS fname,
         AES_DECRYPT(pd.age, encryptkey) AS age,
         AES_DECRYPT(pd.lname, encryptkey) AS lname,
         AES_DECRYPT(pd.home_phone, encryptkey) AS home_phone,
         AES_DECRYPT(pd.mobile_no, encryptkey) AS mobile_phone,
         AES_DECRYPT(pd.relation_with_patient, encryptkey) AS relation,
         AES_DECRYPT(pd.relative_name, encryptkey) AS relative_name,
         AES_DECRYPT(pd.street, encryptkey) AS street,
         AES_DECRYPT(pd.title, encryptkey) AS title,
         cl.city_name AS city,
         sl.state AS state,
         AES_DECRYPT(pd.sex, encryptkey) AS gender,
         AES_DECRYPT(pd.dob, encryptkey) AS dob,
         AES_DECRYPT(pd.email, encryptkey) AS email, pd.admit_flag, pd.entry_date, pd.mrn1,
         pd.mrn2, id.insurance_type, fcm.f_cm_name
  FROM   patient_data AS pd
  LEFT JOIN insurance_data AS id ON id.p_id = pd.p_id
  LEFT   JOIN state_list AS sl ON sl.sl_id = AES_DECRYPT(pd.state,encryptkey)
  LEFT   JOIN city_list AS cl ON cl.cl_id = AES_DECRYPT(pd.city,encryptkey)
  LEFT   JOIN ehr_reg AS er ON er.p_id = pd.p_id
  LEFT   JOIN facility_category_master AS fcm ON id.insurance_type = fcm.fc_m_id
  WHERE  pd.hr_id = proc_hrid
  AND    pd.flag &lt;&gt; '0'
  GROUP BY pd.p_id
  ORDER  BY pd.entry_date DESC, pd.p_id DESC ;
  END
  </code></pre>