Optimize the SmarterTrack Database in Higher Load Environments

Applies to SmarterTrack 6.x - 12.x

In most small installations, SmarterTrack will perform as expected with no specific modification to the database. However, in higher load environments it is recommended to run the following queries to create the indexes needed to ensure the best performance.

Run these queries one at a time so that if your installation already has an index defined, you can still run the remaining queries.

MySQL

CREATE INDEX IDX_st_Chats_ChatNumber on st_Chats (ChatNumber);

CREATE INDEX IDX_st_Users_LoweredUserName on st_Users (LoweredUserName);

CREATE INDEX IDX_st_Users_LoweredEmail on st_Users (LoweredEmail(50));

CREATE INDEX IDX_st_Tickets_CustomerEmailAddress on st_Tickets (CustomerEmailAddress(50), IsDeleted, TicketStatusID);

CREATE INDEX IDX_st_Tickets_TicketNumber on st_Tickets (TicketNumber);

CREATE INDEX IDX_st_Tickets_TicketStatus_1 on st_Tickets (TicketStatusID, UserID);

CREATE INDEX IDX_st_Tickets_TicketStatus_2 on st_Tickets (TicketStatusID, ShowInFollowUpView, IsDeleted, IsSpam, UserID, GroupID);

CREATE INDEX IDX_st_TimeLogs_CallLogID on st_TimeLogs (CallLogID);

CREATE INDEX IDX_st_TimeLogs_ChatID on st_TimeLogs (ChatID);

CREATE INDEX IDX_st_SurveysOffered_SurveyGuid on st_SurveysOffered (SurveyGuid);

 

SQL Server

CREATE INDEX IDX_st_Chats_ChatNumber on st_Chats (ChatNumber);

CREATE INDEX IDX_st_Users_LoweredUserName on st_Users (LoweredUserName);

CREATE INDEX IDX_st_Users_LoweredEmail on st_Users (LoweredEmail);

CREATE INDEX IDX_st_Tickets_CustomerEmailAddress on st_Tickets (CustomerEmailAddress, IsDeleted, TicketStatusID);

CREATE INDEX IDX_st_Tickets_TicketNumber on st_Tickets (TicketNumber);

CREATE INDEX IDX_st_Tickets_TicketStatus_1 on st_Tickets (TicketStatusID, UserID);

CREATE INDEX IDX_st_Tickets_TicketStatus_2 on st_Tickets (TicketStatusID, ShowInFollowUpView, IsDeleted, IsSpam, UserID, GroupID);

CREATE INDEX IDX_st_TimeLogs_CallLogID on st_TimeLogs (CallLogID);

CREATE INDEX IDX_st_TimeLogs_ChatID on st_TimeLogs (ChatID);

CREATE INDEX IDX_st_SurveysOffered_SurveyGuid on st_SurveysOffered (SurveyGuid);

 

Learn more about SmarterTrack’s helpdesk system, which includes live chat, a self service portal and knowledge base software.

Add Feedback