I'm using following query of third party application that seems to be taking too time for executing in PHP. Table Structure
SELECT auto_id, customer_id, apply_dt, priority_opt, cust_apl_by, cust_upd_by, cust_upd_dt, agent, second_agent, follow_up, funding_dt, amt_granted, cred_review_fee_amt, cred_review_fee_dt_paid, cred_estab_fee_amt, cred_estab_fee_dt_paid, liq_fee_amt, liq_fee_dt_paid, miscel_fee_amt, miscel_fee_dt_paid, df_cnslt_fee_amt, df_cnslt_fee_date_paid, cred_repair_fee_amt, cred_repair_fee_dt_paid, lead_src, bst_time_to_call, p_psn2, p_fl_nm, p_ph1, p_ph2, p_eml1, p_eml2, p_amt_req, p_cmt, p_fr_nm, p_mi_nm, p_la_nm, p_hm_ph, p_hm_addr, p_ye_addr, p_city, p_state, p_zip, p_dob, p_ss, p_is_us, p_mam_maiden_nm, p_drv_lic, p_unq_id, p_hv_af, p_wh_af, p_hv_dod, p_wh_dod, p_bnk_nm, b_stg, b_leg_nm, b_ent_typ, b_ind_typ, b_fed_tax_id, b_ph, b_fax, b_addr, b_city, b_state, b_zip, b_ye_busi, b_empl, b_reg_state, b_wb_site, b_bnk_nm, b_acpt_cred_card, b_hv_cred_card, b_seeking, b_hv_not_show_cred_card, b_wht_bnk_issu_thm, b_hv_401k_ira, b_how_much, b_cred_premis, b_cred_usr, b_cred_pwd, p2_ph1, p2_ph2, p2_eml, manager, p2_relation, p2_dob, p2_ss, p2_cred_usr, p2_cred_pwd, b_new_buss, p2_hm_addr, p2_city, p2_state, remarks, p_psn3, p3_ph1, p3_ph2, p3_relation, p3_dob, p3_ss, p3_eml, p3_cred_usr, p3_cred_pwd, p3_hm_addr, p3_city, p3_state, p1_cred_usr, p1_cred_pwd, b_facs_no, b_eml, b_acc, b_acc_ph, b_own_lease, b_type, b_addition_addr, b_landlord, b_landlord_ph, b_landlord_fr, b_landlord_to, b_landlord_month_pmt, b_landlord_renew_opt, b_landlord_renew_ye, b_landlord_payment, b_landlord_approx, b_cash, b_amex, b_vs_mc, b_other, b_is_season, b_month_season_begin, b_month_season_to, is_opportunity_yes, p2_zip, p3_zip, drip, last_action_day, drip_start_dt, language, opt_yes_no, opportunity, sms_log_agent, call_log_agent, mail_log_agent, quali_guide_result, quali2_guide_result, p2_quali_guide_result, p2_quali2_guide_result, p3_quali_guide_result, p3_quali2_guide_result, quali_result, rev_total_bal, rev_total_limit, rev_total_ups, rev_total_downs, rev_fund_needed, p2_rev_total_bal, p2_rev_total_limit, p2_rev_total_ups, p2_rev_total_downs, p2_rev_fund_needed, p3_rev_total_bal, p3_rev_total_limit, p3_rev_total_ups, p3_rev_total_downs, p3_rev_fund_needed, keepaneye, p_agency, p2_agency, p3_agency, vendor_priority_opt, cur_credit_report1, cur_credit_report2, cur_credit_report3, quali_range, quali_cond, quali_name, quali_state, p2_quali_range, p2_quali_cond, p2_quali_name, p2_quali_state, p3_quali_range, p3_quali_cond, p3_quali_name, p3_quali_state, quali_range_value, p2_quali_range_value, p3_quali_range_value, max_quali_range_value, max_quali_cond, max_quali_range
FROM customer_info
USE INDEX (customer_id) where customer_id='xxxxx'
Explain result:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE customer_info ALL NULL NULL NULL NULL 1025952
This table is being used many times in PHP file.
A simple select without WHERE clause and without much data shouldn't be that much long to respond. Connectivity issues?
If the third party is taking long time to respond without connectivity issues, you won't be able to do anything on their side.
Just cache the returned data and refresh it once every x time independently of what you're currently doing. And work only on your cached data. If the data is a DataSet from MySQL, you can use SqlChangeMonitor (an example here).
Your posted query has no WHERE or filter condition and as can be seen from explain result that it's not using any index. Include some filter condition (where condition on any indexed column) and see the query plan again