VERIFIED SOLUTION i

E messaging SQL installer / Index default

Product Feature: Installation / Upgrade

Operating System: Windows 2008

Database: Sql


 

Issue

New installation upgrade

Cause

 Our customer re installed the Emessaging and followed the install guide, The DBAs from  client reported that before the reinstall Emessaging the database had  indexes that does not appear on the new installation. 

Resolution

UPDATED: September 7, 2017


Indexes which are shown as missing, are not really part of standard e-messaging DB scripts. Might be customer had created their own indexes.

 attached emessaging DB creation script file, which comes within installer.

Indexes can be searched in this file by searching word : CREATE INDEX

Below are the standard indexes which e-messaging scripts creates: 

CREATE INDEX IX1_secret_question_history ON secret_question_history(language_iso); 
CREATE INDEX IX2_secret_question_history ON secret_question_history(vendor_id); 
CREATE INDEX IX1_secret_question ON secret_question(language_iso); 
CREATE INDEX IX2_secret_question ON secret_question(vendor_id); 
CREATE INDEX IX1_appuser ON appuser(role_id); 
CREATE INDEX IX2_appuser ON appuser(language_iso); 
CREATE INDEX IX3_appuser ON appuser(vendor_id); 
CREATE INDEX IX4_appuser ON appuser(country_iso); 
CREATE INDEX IX5_appuser ON appuser(secret_question_id); 
CREATE INDEX IX1_appuser_history ON appuser_history(role_id); 
CREATE INDEX IX2_appuser_history ON appuser_history(language_iso); 
CREATE INDEX IX3_appuser_history ON appuser_history(vendor_id); 
CREATE INDEX IX4_appuser_history ON appuser_history(country_iso); 
CREATE INDEX IX5_appuser_history ON appuser_history(secret_question_id); 
CREATE INDEX IX1_msgindex_people ON msgindex_people(msgIndex_id); 
CREATE INDEX IX2_msgindex_people ON msgindex_people(people_email); 
CREATE INDEX IX1_role_privilege ON role_privilege(roles_id); 
CREATE INDEX IX2_role_privilege ON role_privilege(privileges_id); 
CREATE INDEX IX1_profilepriv_role ON profilepriv_role(privilege_id); 
CREATE INDEX IX2_profilepriv_role ON profilepriv_role(entity_id); 
CREATE INDEX IX1_profilepriv_user ON profilepriv_user(privilege_id); 
CREATE INDEX IX2_profilepriv_user ON profilepriv_user(entity_id); 
CREATE INDEX IX1_outbound_profile_history ON outbound_profile_history(gateway_id); 
CREATE INDEX IX2_outbound_profile_history ON outbound_profile_history(message_type_id); 
CREATE INDEX IX3_outbound_profile_history ON outbound_profile_history(vendor_id); 
CREATE INDEX IX1_process_error ON process_error(error_code); 
CREATE INDEX IX2_process_error ON process_error(vendor_id); 
CREATE INDEX IX1_out_profile ON out_profile(userLastUpdate_id); 
CREATE INDEX IX2_out_profile ON out_profile(gateway_id); 
CREATE INDEX IX3_out_profile ON out_profile(message_type_id); 
CREATE INDEX IX4_out_profile ON out_profile(creator_id); 
CREATE INDEX IX5_out_profile ON out_profile(vendor_id); 
CREATE INDEX IX1_outbound_attachment_history ON outbound_attachment_history(outboundProfileHistory_id); 
CREATE INDEX IX1_outbound_attachment ON outbound_attachment(outboundProfile_id); 
CREATE INDEX IX1_vendor_message_type ON vendor_message_type(message_type_id); 
CREATE INDEX IX2_vendor_message_type ON vendor_message_type(vendor_id); 
CREATE INDEX IX1_data_flow_plan ON data_flow_plan(userLastUpdate_id); 
CREATE INDEX IX2_data_flow_plan ON data_flow_plan(creator_id); 
CREATE INDEX IX3_data_flow_plan ON data_flow_plan(vendor_id); 
CREATE INDEX IX1_in_profile ON in_profile(userLastUpdate_id); 
CREATE INDEX IX2_in_profile ON in_profile(gateway_id); 
CREATE INDEX IX3_in_profile ON in_profile(creator_id); 
CREATE INDEX IX4_in_profile ON in_profile(vendor_id); 
CREATE INDEX IX5_in_profile ON in_profile(dqConnector_id); 
CREATE INDEX IX6_in_profile ON in_profile(outboundGateway_id); 
CREATE INDEX IX1_dq_connector ON dq_connector(vendor_id); 
CREATE INDEX IX1_data_flow_plan_history ON data_flow_plan_history(vendor_id); 
CREATE INDEX IX1_doc1_template ON doc1_template(outboundProfile_id); 
CREATE INDEX IX2_doc1_template ON doc1_template(userLastUpdate_id); 
CREATE INDEX IX3_doc1_template ON doc1_template(creator_id); 
CREATE INDEX IX4_doc1_template ON doc1_template(vendor_id); 
CREATE INDEX IX1_inbound_profile_history ON inbound_profile_history(gateway_id); 
CREATE INDEX IX2_inbound_profile_history ON inbound_profile_history(vendor_id); 
CREATE INDEX IX3_inbound_profile_history ON inbound_profile_history(dqConnector_id); 
CREATE INDEX IX4_inbound_profile_history ON inbound_profile_history(outboundGateway_id); 
CREATE INDEX IX1_send_job ON send_job(userLastUpdate_id); 
CREATE INDEX IX2_send_job ON send_job(outboundProfile_id); 
CREATE INDEX IX3_send_job ON send_job(dataFlowPlan_id); 
CREATE INDEX IX4_send_job ON send_job(doc1Template_id); 
CREATE INDEX IX5_send_job ON send_job(userLastUpdateUI_id); 
CREATE INDEX IX6_send_job ON send_job(creator_id); 
CREATE INDEX IX7_send_job ON send_job(vendor_id); 
CREATE INDEX IX1_send_job_history ON send_job_history(doc1Template_id); 
CREATE INDEX IX2_send_job_history ON send_job_history(dataFlowPlan_id); 
CREATE INDEX IX3_send_job_history ON send_job_history(vendor_id); 
CREATE INDEX IX1_gateway ON gateway(vendor_id); 
CREATE INDEX IX1_role ON role(vendor_id); 
CREATE INDEX IX1_workflow_history ON workflow_history(user_id); 
CREATE INDEX IX2_workflow_history ON workflow_history(workflowStage_id); 
CREATE INDEX IX3_workflow_history ON workflow_history(workflowItem_id); 
CREATE INDEX IX1_workflow_item ON workflow_item(user_id); 
CREATE INDEX IX2_workflow_item ON workflow_item(workflowStage_id); 
CREATE INDEX IX3_workflow_item ON workflow_item(msgIndex_id); 
CREATE INDEX IX1_audit_message ON audit_message(user_id); 
CREATE INDEX IX1_msg_part_index ON msg_part_index(userLastUpdate_id); 
CREATE INDEX IX2_msg_part_index ON msg_part_index(msgIndex_id); 
CREATE INDEX IX3_msg_part_index ON msg_part_index(creator_id); 
CREATE INDEX IX1_outbound_message ON outbound_message(gateway_id); 
CREATE INDEX IX2_outbound_message ON outbound_message(userLastUpdate_id); 
CREATE INDEX IX3_outbound_message ON outbound_message(workflowitem_id); 
CREATE INDEX IX4_outbound_message ON outbound_message(creator_id); 
CREATE INDEX IX5_outbound_message ON outbound_message(send_job_id); 
CREATE INDEX IX6_outbound_message ON outbound_message(outbound_profile_id); 
CREATE INDEX IX7_outbound_message ON outbound_message(smsc_message_id); 
CREATE INDEX IX8_outbound_message ON outbound_message(pdu_sequence_number); 
CREATE INDEX IX9_outbound_message ON outbound_message(doc_instance_id); 
CREATE INDEX IX1_message_attachment ON message_attachment(message_id); 
CREATE INDEX IX1_output ON output(message_type_id); 
CREATE INDEX IX2_output ON output(doc1Template_id); 
CREATE INDEX IX1_msg_index ON msg_index(outboundMessage_id); 
CREATE INDEX IX2_msg_index ON msg_index(inbound_profile_id); 
CREATE INDEX IX3_msg_index ON msg_index(category_id); 
CREATE INDEX IX1_in_profile_role_privs ON in_profile_role_privs(profilepriv_role_id); 
CREATE INDEX IX2_in_profile_role_privs ON in_profile_role_privs(in_profile_id); 
CREATE INDEX IX1_in_profile_user_privs ON in_profile_user_privs(profilepriv_user_id); 
CREATE INDEX IX2_in_profile_user_privs ON in_profile_user_privs(in_profile_id); 
CREATE INDEX IX1_msg_part_index_history ON msg_part_index_history(msgIndex_id); 
CREATE INDEX IX1_out_profile_role_privs ON out_profile_role_privs(profilepriv_role_id); 
CREATE INDEX IX2_out_profile_role_privs ON out_profile_role_privs(out_profile_id); 
CREATE INDEX IX1_out_profile_user_privs ON out_profile_user_privs(profilepriv_user_id); 
CREATE INDEX IX2_out_profile_user_privs ON out_profile_user_privs(out_profile_id); 
CREATE INDEX IX1_message_category ON message_category(category_id); 
CREATE INDEX IX1_vendor ON vendor(country_iso); 
CREATE INDEX IX1_vendor_history ON vendor_history(country_iso); 
CREATE INDEX IX1_gateway_history ON gateway_history(vendor_id); 
CREATE INDEX IX1_appuser_role ON appuser_role(appuser_id); 
CREATE INDEX IX2_appuser_role ON appuser_role(roles_id); 
CREATE INDEX IX1_appuser_category ON [appuser_category](appuser_id); 
CREATE INDEX IX2_appuser_category ON [appuser_category](categories_id); 
CREATE INDEX IX1_role_category ON role_category(categories_id); 
CREATE INDEX IX2_role_category ON role_category(role_id);