php - Query optimization of Mysql stored-procedure -

i'm working in rdbms (mysql) have approx 15 20 tables major tables having more 4 lacks rows , 70 columns in each table.while retrieving data of time have use left join results delaying in operation. use stored procedures kindly suggest best method fast operation.

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  ; 

this stored routines

begin select pd.p_id, er.er_id, pd.flag, pd.delete_reason, pd.select_reason,        aes_decrypt(pd.fname, encryptkey) fname,        aes_decrypt(pd.age, encryptkey) age,        aes_decrypt(pd.lname, encryptkey) lname,        aes_decrypt(pd.home_phone, encryptkey) home_phone,        aes_decrypt(pd.mobile_no, encryptkey) mobile_phone,        aes_decrypt(pd.relation_with_patient, encryptkey) relation,        aes_decrypt(pd.relative_name, encryptkey) relative_name,        aes_decrypt(pd.street, encryptkey) street,        aes_decrypt(pd.title, encryptkey) title,        cl.city_name city,        sl.state state,        aes_decrypt(pd.sex, encryptkey) gender,        aes_decrypt(pd.dob, encryptkey) dob,        aes_decrypt(pd.email, encryptkey) email, pd.admit_flag, pd.entry_date, pd.mrn1,        pd.mrn2, id.insurance_type, fcm.f_cm_name   patient_data pd left join insurance_data id on id.p_id = pd.p_id left   join state_list sl on sl.sl_id = aes_decrypt(pd.state,encryptkey) left   join city_list cl on cl.cl_id = aes_decrypt(pd.city,encryptkey) left   join ehr_reg er on er.p_id = pd.p_id left   join facility_category_master fcm on id.insurance_type = fcm.fc_m_id  pd.hr_id = proc_hrid ,    pd.flag <> '0' group pd.p_id order  pd.entry_date desc, pd.p_id desc ; end 

how many rows expecting query? if one, don't see why slow.

if getting thousands, accept takes time fetch thousands of rows.

this index might help:

index(hr_id, flag, p_id) 

or, maybe left joins many:1?? is, there only one insurance, state, city, ehr, , facility given p_id? if so, don't need group by. bypass wasted steps.

you replace

sl.state state, left   join state_list sl on sl.sl_id = aes_decrypt(pd.state,encryptkey) 

with

( select state state_list        sl_id = aes_decrypt(pd.state,encryptkey) ) state, 

i prefer push groups of things city & state off table, using single join rather separate normalizations.

i can't imagine binary flag (eg gender) least bit secure. state cracked looking @ relative populations.

i suggest collect columns don't need searched on , put them single json string, encrypt that. more secure.

and isn't there going around aes routines being cracked banned trivial uses. app looks more serious that.

i'm sorry, performance needs take seat security. hire security consultant.