Performance improvement - add foreign key indexes

Created by: danielkummer

Hi We're using gitlabhq quite intensively at our company. I notices increasingly bad performance directly related to the mysql database.

After analyzing the queries with the slow log i came to the conclusion that a few extra indices would speed up the application significantly.

I suggest adding indices to the foreign keys of the tables.

I got a massive performance gain just by adding an index to the project_id column of the events table:

ALTER TABLE events ADD INDEX project_id (project_id)

Created by: dzaporozhets

Hi. Good tip!

By Administrator on 2012-12-03T15:28:36 (imported from GitLab project)

By Administrator on 2012-12-03T15:28:36 (imported from GitLab)

Created by: danielkummer

Sorry, closed it by accident... Still in favor of additional indices ;)

By Administrator on 2012-12-03T15:29:35 (imported from GitLab project)

By Administrator on 2012-12-03T15:29:35 (imported from GitLab)

Created by: dzaporozhets

Can you make a short list of indices you think we should apply?

By Administrator on 2012-12-03T15:30:28 (imported from GitLab project)

By Administrator on 2012-12-03T15:30:28 (imported from GitLab)

Created by: dzaporozhets

@danielkummer i've pushed indices for events

By Administrator on 2012-12-03T15:50:50 (imported from GitLab project)

By Administrator on 2012-12-03T15:50:50 (imported from GitLab)

Created by: riyad

I learned that there should be basically indices for anything that is used in a WHERE clause. IMHO AR associations would be obvious.

By Administrator on 2012-12-03T16:02:33 (imported from GitLab project)

By Administrator on 2012-12-03T16:02:33 (imported from GitLab)

Created by: dzaporozhets

@Riyad yes. at least AR associations

By Administrator on 2012-12-03T16:03:14 (imported from GitLab project)

By Administrator on 2012-12-03T16:03:14 (imported from GitLab)

Created by: dzaporozhets

I think its enough for now. Feel free to reopen issue if you have more suggestions

By Administrator on 2012-12-03T16:12:49 (imported from GitLab project)

By Administrator on 2012-12-03T16:12:49 (imported from GitLab)

Created by: danielkummer

Sorry for the late reply. I'm totally in agreement with @Riyad AR associations are always good to be covered by indices... So your changes @randx are right...

If a lot of ordering is applied to the timestamp fields then the may also qualify for a simple index...

By Administrator on 2012-12-05T13:16:40 (imported from GitLab project)

By Administrator on 2012-12-05T13:16:40 (imported from GitLab)

Created by: riyad

I went through all where and order statements and added indexes for them. There was only one problem with keys.key because MySQL/InnoDB doesn't support indexing text columns. :(

By Administrator on 2012-12-05T20:53:18 (imported from GitLab project)

By Administrator on 2012-12-05T20:53:18 (imported from GitLab)