[Mantisbt-dev] Database Speedups

[Kenzaburo I. <ke...@30...>]

All,

I don't think we're going to implement these right away (for 0.18.0)  but
here are a list of alerations that I believe will speedup performance
greatly.  My estimations based on counting seem to see somewhere between a
30-70% increase in response times.

The FULLTEXT helps on the searches immensely.  This is available from
Mysql 3.23.23 onwards.  Our current requirements are 3.23.2.  Maybe we
should bump the requirements.

The indexes I half guessed on.  I do know they work but some may be
excessive.  If any gurus can check them out it would help.  Otherwise I'll
be digging into SQL research when I get the chance.

I've adopted a convention of adding _idx to the index names and _ftxt to
the fulltext.  This should prevent name conflicts.

---------

ALTER TABLE mantis_bug_table ADD FULLTEXT(summary_ftxt);
ALTER TABLE mantis_bug_text_table ADD FULLTEXT(description_ftxt);
ALTER TABLE mantis_bug_text_table ADD FULLTEXT(steps_to_reproduce_ftxt);
ALTER TABLE mantis_bug_text_table ADD FULLTEXT(additional_information_ftxt);
ALTER TABLE mantis_bugnote_text_table ADD FULLTEXT(note_ftxt);

ALTER TABLE mantis_bug_file_table ADD INDEX(date_added_idx);
ALTER TABLE mantis_bug_file_table ADD INDEX(title_idx);
ALTER TABLE mantis_bug_file_table ADD INDEX(bug_id_idx);

ALTER TABLE mantis_bug_history_table ADD INDEX(bug_id_idx);
ALTER TABLE mantis_bug_history_table ADD INDEX(user_id_idx);

ALTER TABLE mantis_bug_relationship_table ADD INDEX(source_bug_id_idx);
ALTER TABLE mantis_bug_relationship_table ADD INDEX(destination_bug_id_idx);

ALTER TABLE mantis_project_file_table ADD INDEX(project_id_idx);
ALTER TABLE mantis_project_file_table ADD INDEX(filename_idx);
ALTER TABLE mantis_project_file_table ADD INDEX(date_added_idx);

ALTER TABLE mantis_bug_table ADD INDEX(priority_idx);
ALTER TABLE mantis_bug_table ADD INDEX(project_id_idx);
ALTER TABLE mantis_bug_table ADD INDEX(reporter_id_idx);
ALTER TABLE mantis_bug_table ADD INDEX(handler_id_idx);
ALTER TABLE mantis_bug_table ADD INDEX(severity_idx);
ALTER TABLE mantis_bug_table ADD INDEX(reproducibility_idx);
ALTER TABLE mantis_bug_table ADD INDEX(status_idx);
ALTER TABLE mantis_bug_table ADD INDEX(resolution_idx);
ALTER TABLE mantis_bug_table ADD INDEX(projection_idx);
ALTER TABLE mantis_bug_table ADD INDEX(category_idx);
ALTER TABLE mantis_bug_table ADD INDEX(last_updated_idx);

ALTER TABLE mantis_bugnote_table ADD INDEX(date_submitted_idx);
ALTER TABLE mantis_bugnote_table ADD INDEX(view_state_idx);
ALTER TABLE mantis_bugnote_table ADD INDEX(bug_id_idx);

ALTER TABLE mantis_news_table ADD INDEX(headline_idx);
ALTER TABLE mantis_news_table ADD INDEX(announcement_idx);
ALTER TABLE mantis_news_table ADD INDEX(project_id_idx);
ALTER TABLE mantis_news_table ADD INDEX(date_posted_idx);

ALTER TABLE mantis_project_table ADD INDEX(enabled_idx);
ALTER TABLE mantis_project_table ADD INDEX(view_state_idx);
ALTER TABLE mantis_project_table ADD INDEX(access_min_idx);

ALTER TABLE mantis_project_user_list_table ADD INDEX(user_id_idx);
ALTER TABLE mantis_project_user_list_table ADD INDEX(access_level_idx);
ALTER TABLE mantis_project_user_list_table ADD INDEX(project_id_idx);

ALTER TABLE mantis_user_table ADD INDEX(username_idx);
ALTER TABLE mantis_user_table ADD INDEX(date_created_idx);
ALTER TABLE mantis_user_table ADD INDEX(last_visit_idx);
ALTER TABLE mantis_user_table ADD INDEX(enabled_idx);
ALTER TABLE mantis_user_table ADD INDEX(access_level_idx);