0

I have the following MySQL table:

    CREATE TABLE IF NOT EXISTS `customer_info` (
  `auto_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` varchar(20) CHARACTER SET latin1 NOT NULL,
  `apply_dt` date DEFAULT NULL,
  `priority_opt` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `cust_apl_by` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `cust_upd_by` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `cust_upd_dt` datetime DEFAULT NULL,
  `agent` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `second_agent` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `follow_up` date DEFAULT NULL,
  `funding_dt` date DEFAULT NULL,
  `amt_granted` float DEFAULT NULL,
  `cred_review_fee_amt` float DEFAULT NULL,
  `cred_review_fee_dt_paid` date DEFAULT NULL,
  `cred_estab_fee_amt` float DEFAULT NULL,
  `cred_estab_fee_dt_paid` date DEFAULT NULL,
  `liq_fee_amt` float DEFAULT NULL,
  `liq_fee_dt_paid` date DEFAULT NULL,
  `miscel_fee_amt` float DEFAULT NULL,
  `miscel_fee_dt_paid` date DEFAULT NULL,
  `df_cnslt_fee_amt` float DEFAULT NULL,
  `df_cnslt_fee_date_paid` date DEFAULT NULL,
  `cred_repair_fee_amt` float DEFAULT NULL,
  `cred_repair_fee_dt_paid` date DEFAULT NULL,
  `lead_src` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `bst_time_to_call` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p_psn2` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p_fl_nm` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p_ph1` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p_ph2` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p_eml1` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p_eml2` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p_amt_req` float DEFAULT NULL,
  `p_cmt` text CHARACTER SET latin1,
  `p_fr_nm` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p_mi_nm` varchar(30) CHARACTER SET latin1 DEFAULT '',
  `p_la_nm` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p_hm_ph` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p_hm_addr` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p_ye_addr` int(11) DEFAULT NULL,
  `p_city` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p_state` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p_zip` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p_dob` date DEFAULT NULL,
  `p_ss` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p_is_us` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
  `p_mam_maiden_nm` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
  `p_drv_lic` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p_unq_id` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p_hv_af` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
  `p_wh_af` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
  `p_hv_dod` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
  `p_wh_dod` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
  `p_bnk_nm` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `b_stg` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_leg_nm` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `b_ent_typ` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_ind_typ` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_fed_tax_id` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_ph` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_fax` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_addr` varchar(30) CHARACTER SET latin1 DEFAULT '',
  `b_city` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_state` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_zip` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_ye_busi` int(10) unsigned DEFAULT '1',
  `b_empl` int(10) unsigned DEFAULT NULL,
  `b_reg_state` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_wb_site` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_bnk_nm` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_acpt_cred_card` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_hv_cred_card` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_seeking` float DEFAULT NULL,
  `b_hv_not_show_cred_card` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_wht_bnk_issu_thm` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_hv_401k_ira` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_how_much` float DEFAULT NULL,
  `b_cred_premis` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `b_cred_usr` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_cred_pwd` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p2_ph1` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p2_ph2` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p2_eml` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `manager` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p2_relation` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p2_dob` date DEFAULT NULL,
  `p2_ss` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p2_cred_usr` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p2_cred_pwd` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_new_buss` varchar(5) CHARACTER SET latin1 DEFAULT NULL,
  `p2_hm_addr` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `p2_city` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p2_state` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `remarks` text CHARACTER SET utf8,
  `p_psn3` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p3_ph1` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p3_ph2` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p3_relation` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p3_dob` date DEFAULT NULL,
  `p3_ss` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p3_eml` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `p3_cred_usr` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p3_cred_pwd` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p3_hm_addr` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `p3_city` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p3_state` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p1_cred_usr` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p1_cred_pwd` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_facs_no` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_eml` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `b_acc` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `b_acc_ph` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `b_own_lease` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_type` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `b_addition_addr` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `b_landlord` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `b_landlord_ph` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `b_landlord_fr` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `b_landlord_to` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `b_landlord_month_pmt` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `b_landlord_renew_opt` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `b_landlord_renew_ye` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `b_landlord_payment` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `b_landlord_approx` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `b_cash` float unsigned DEFAULT NULL,
  `b_amex` float DEFAULT NULL,
  `b_vs_mc` float DEFAULT NULL,
  `b_other` float DEFAULT NULL,
  `b_is_season` varchar(10) CHARACTER SET latin1 DEFAULT NULL,
  `b_month_season_begin` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `b_month_season_to` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `is_opportunity_yes` int(11) DEFAULT NULL,
  `p2_zip` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `p3_zip` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `drip` varchar(3) CHARACTER SET utf8 DEFAULT NULL,
  `last_action_day` int(11) DEFAULT NULL,
  `drip_start_dt` date DEFAULT NULL,
  `language` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `opt_yes_no` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `opportunity` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sms_log_agent` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `call_log_agent` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mail_log_agent` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `quali_guide_result` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `quali2_guide_result` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `p2_quali_guide_result` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `p2_quali2_guide_result` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `p3_quali_guide_result` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `p3_quali2_guide_result` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `quali_result` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `rev_total_bal` float DEFAULT NULL,
  `rev_total_limit` float DEFAULT NULL,
  `rev_total_ups` float DEFAULT NULL,
  `rev_total_downs` float DEFAULT NULL,
  `rev_fund_needed` float DEFAULT NULL,
  `p2_rev_total_bal` float DEFAULT NULL,
  `p2_rev_total_limit` float DEFAULT NULL,
  `p2_rev_total_ups` float DEFAULT NULL,
  `p2_rev_total_downs` float DEFAULT NULL,
  `p2_rev_fund_needed` float DEFAULT NULL,
  `p3_rev_total_bal` float DEFAULT NULL,
  `p3_rev_total_limit` float DEFAULT NULL,
  `p3_rev_total_ups` float DEFAULT NULL,
  `p3_rev_total_downs` float DEFAULT NULL,
  `p3_rev_fund_needed` float DEFAULT NULL,
  `keepaneye` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `p_agency` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `p2_agency` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `p3_agency` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `vendor_priority_opt` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cur_credit_report1` int(11) DEFAULT NULL,
  `cur_credit_report2` int(11) DEFAULT NULL,
  `cur_credit_report3` int(11) DEFAULT NULL,
  `quali_range` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `quali_cond` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `quali_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `quali_state` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `p2_quali_range` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `p2_quali_cond` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `p2_quali_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `p2_quali_state` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `p3_quali_range` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `p3_quali_cond` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `p3_quali_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `p3_quali_state` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `quali_range_value` int(255) DEFAULT NULL,
  `p2_quali_range_value` int(255) DEFAULT NULL,
  `p3_quali_range_value` int(255) DEFAULT NULL,
  `max_quali_range_value` int(11) DEFAULT NULL,
  `max_quali_cond` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `max_quali_range` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`auto_id`),
  KEY `is_opportunity_yes` (`is_opportunity_yes`) USING HASH,
  KEY `funding_dt` (`funding_dt`) USING BTREE,
  KEY `priority_opt` (`priority_opt`) USING HASH,
  KEY `follow_priority` (`priority_opt`,`follow_up`) USING BTREE,
  KEY `funding_priority` (`priority_opt`,`funding_dt`) USING BTREE,
  KEY `p_ph1` (`p_ph1`) USING HASH,
  KEY `p_ph2` (`p_ph2`) USING HASH,
  KEY `p_eml1` (`p_eml1`) USING HASH,
  KEY `follow_up` (`follow_up`) USING HASH,
  KEY `customer_id` (`customer_id`) USING HASH,
  KEY `auto_id` (`auto_id`),
  KEY `p2_ph1` (`p2_ph1`) USING HASH,
  KEY `p3_ph1` (`p3_ph1`) USING HASH,
  KEY `p_eml2` (`p_eml2`) USING HASH,
  KEY `p_fl_nm` (`p_fl_nm`) USING HASH,
  KEY `p2_ph2` (`p2_ph2`) USING HASH,
  KEY `p3_ph2` (`p3_ph2`) USING HASH,
  KEY `p2_eml` (`p2_eml`) USING HASH,
  KEY `p3_eml` (`p3_eml`) USING HASH,
  KEY `credit_repair` (`opt_yes_no`) USING HASH,
  KEY `sms_log_agent` (`sms_log_agent`) USING BTREE,
  KEY `call_log_agent` (`call_log_agent`) USING HASH,
  KEY `mail_log_agent` (`mail_log_agent`) USING HASH,
  KEY `quali_result` (`quali_result`) USING BTREE,
  KEY `opportunity` (`opportunity`) USING HASH,
  KEY `lead_src` (`lead_src`) USING HASH,
  KEY `cust_upd_dt` (`cust_upd_dt`) USING BTREE,
  KEY `apply_dt` (`apply_dt`) USING BTREE,
  KEY `agent` (`agent`) USING HASH,
  KEY `keepaneye` (`keepaneye`) USING HASH,
  KEY `second_agent` (`second_agent`) USING BTREE,
  KEY `max_quali_range_value` (`max_quali_range_value`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1567531 ;

I need to improve the speed of queries like:

Select * from customer_info

I want to know how we can apply techniques to improve the speed by using any method. But it's a third party tool and we have to care about our running application as well.

  • 2
    You can`t improve the speed since you are selecting all the.data,except getting better hardware. – Mihai Feb 27 '17 at 11:56
  • It would be helpful if following info are provided: Number of records in the table, current performance metrices and expected metrics and also do you have pagination concept while querying records – SQL.RK Feb 27 '17 at 12:14
  • It takes time to shovel million(s) of quite wide rows across the network. What will you do with gigabyte(s) of output?? My question is a serious one -- if you will be filtering or summarizing it, maybe it can be done before sending the results to the client. – Rick James Feb 27 '17 at 18:50
  • HASH ignored; you get BTREE. Have you heard of 'composite' indexes? – Rick James Feb 27 '17 at 18:51
1

Does the tool need to see all of the columns initially?

If not, then redesign the table into two or more. This will reduce the amount of data that is pulled off disk and projected to the tool. The other table data is a join away when it is needed.

The structure as described does not appear to be normalized. Eliminating redundancy will reduce the amount of storage space consumed.

https://support.microsoft.com/en-us/help/283878/description-of-the-database-normalization-basics

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.