How to improove performance for text datatype column in table
Hello all mysql DBA Guys,
My database contain some tables based on text based column to store large metadata - large string of characters are stored ,i have implemented indexes but i want to know how can i improove performance of query retrivel. and one more thing when i call stored procedure so it also take more than 5 hours to update these text datatype columns.i can't change datatype from text to varchar type and normalization is also not possible.
table structure is,
CREATE TABLE rep_metadata (
"document_id" int(11) NOT NULL AUTO_INCREMENT,
"shahash" varchar(100) DEFAULT NULL,
"em_sourcetype" varchar(50) DEFAULT NULL,
"em_child_conv_index" text,
"em_family_conv_index" varchar(255) DEFAULT NULL,
"em_parent_entryid" varchar(255) DEFAULT NULL,
"em_entryid" varchar(255) DEFAULT NULL,
"ed_trk_chngs_enbl" tinyint(1) NOT NULL,
"ed_has_trk_chngs" tinyint(1) NOT NULL,
"ed_has_show_rev_enbl" tinyint(1) NOT NULL,
"ed_has_prnt_rev_enbl" tinyint(1) NOT NULL,
"ed_has_hdn_rws_clmns" tinyint(1) NOT NULL,
"ed_has_speaker_notes" tinyint(1) NOT NULL,
"ed_has_hdn_slides" tinyint(1) NOT NULL,
"ed_no_of_hdn_slides" int(11) DEFAULT NULL,
"ed_has_proctd_shts" tinyint(1) NOT NULL,
"ezr_hier1" varchar(255) DEFAULT NULL,
"ezr_hier2" varchar(255) DEFAULT NULL,
"ezr_hier3" varchar(255) DEFAULT NULL,
"ezr_hier4" varchar(255) DEFAULT NULL,
"ezr_hier5" varchar(255) DEFAULT NULL,
"ezr_hier6" varchar(255) DEFAULT NULL,
"ezr_hier7" varchar(255) DEFAULT NULL,
"ezr_hier8" varchar(255) DEFAULT NULL,
"begdoc" varchar(50) DEFAULT NULL,
"enddoc" varchar(50) DEFAULT NULL,
"begatt" varchar(50) DEFAULT NULL,
"endatt" varchar(50) DEFAULT NULL,
"doctype" varchar(255) DEFAULT NULL,
"volume" varchar(255) DEFAULT NULL,
"custodian" varchar(50) DEFAULT NULL,
"appname" text,
"load_date" datetime DEFAULT NULL,
"sort_date" datetime DEFAULT NULL,
"media_type" varchar(255) DEFAULT NULL,
"media_desc" varchar(255) DEFAULT NULL,
"client_name" varchar(255) DEFAULT NULL,
"client_desc" varchar(255) DEFAULT NULL,
"location" varchar(255) DEFAULT NULL,
"language_1" varchar(255) DEFAULT NULL,
"language_2" varchar(255) DEFAULT NULL,
"privilege_keywords" text,
"responsive_keywords" text,
"keywords" text,
"search_words" text,
"category" varchar(255) DEFAULT NULL,
"md5hash" varchar(255) DEFAULT NULL,
"em_from" varchar(255) DEFAULT NULL,
"em_from_smtp" text,
"em_to" text,
"em_to_smtp" text,
"em_cc" text,
"em_cc_smtp" text,
"em_bcc" text,
"em_bcc_smtp" text,
"em_attach_cnt" int(11) DEFAULT NULL,
"em_attach_list" text,
"em_subject" text,
"em_sentdate" datetime DEFAULT NULL,
"em_received_date" datetime DEFAULT NULL,
"em_on_behalf_of" varchar(255) DEFAULT NULL,
"em_on_behalf_of_smtp" text,
"em_recd_by" varchar(255) DEFAULT NULL,
"em_recd_by_smtp" text,
"em_zarchiveinetrecip" text,
"em_sensitivity" varchar(255) DEFAULT NULL,
"em_importance" varchar(255) DEFAULT NULL,
"em_categories" varchar(255) DEFAULT NULL,
"em_flagstatus" varchar(255) DEFAULT NULL,
"em_read" tinyint(1) NOT NULL,
"em_read_receipt" tinyint(1) NOT NULL,
"em_delivery_receipt" tinyint(1) NOT NULL,
"em_int_msg_id" varchar(255) DEFAULT NULL,
"em_folderpath" text,
"em_int_msg_header" text,
"em_reply_to" text,
"em_bdy_encrypted" tinyint(1) NOT NULL,
"em_mailboxname" text,
"ed_creator" text,
"ed_author" text,
"ed_last_author" text,
"ed_subject" text,
"ed_title" text,
"ed_company" text,
"ed_comments" text,
"ed_manager" varchar(255) DEFAULT NULL,
"ed_category" text,
"ed_keywords" text,
"ed_create_date" datetime DEFAULT NULL,
"ed_last_access_date" datetime DEFAULT NULL,
"ed_last_mod_date" datetime DEFAULT NULL,
"ed_print_date" datetime DEFAULT NULL,
"org_source" varchar(255) DEFAULT NULL,
"org_file_path" text,
"org_filename" text,
"hc_boxno" int(11) DEFAULT NULL,
"hc_number" int(11) DEFAULT NULL,
"hc_docnumber" int(11) DEFAULT NULL,
"hc_source_location" varchar(50) DEFAULT NULL,
"hc_orignl_filename" varchar(50) DEFAULT NULL,
"hc_date_collected" datetime DEFAULT NULL,
"error_comments" text,
"sender_domain" text,
"receiver_domain" text,
PRIMARY KEY ("document_id"),
KEY "idx_repdocstdmetd_shah" ("shahash"),
KEY "idx_cov_ezr" ("ezr_hier1","ezr_hier2","ezr_hier3","ezr_hier4"),
KEY "idx_cov_em" ("em_family_conv_index","em_parent_entryid","em_entryid"),
KEY "idx_cov_doc" ("begdoc","enddoc","begatt","endatt"),
CONSTRAINT "FK_rep_document_standard_metadata_rep_document" FOREIGN KEY ("document_id") REFERENCES "rep_document" ("document_id")
);