Performance improvement - add foreign key indexes #2159

Closed
danielkummer opened this Issue Dec 3, 2012 · 9 comments

Projects

None yet

3 participants

@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)
@randx
Member
randx commented Dec 3, 2012

Hi. Good tip!

@danielkummer danielkummer reopened this Dec 3, 2012
@danielkummer

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

@randx
Member
randx commented Dec 3, 2012

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

@randx randx added a commit that referenced this issue Dec 3, 2012
@randx randx Event indices related to #2159 8dec24a
@randx
Member
randx commented Dec 3, 2012

@danielkummer i've pushed indices for events

@riyad
Contributor
riyad commented Dec 3, 2012

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

@randx
Member
randx commented Dec 3, 2012

@riyad yes. at least AR associations

@randx
Member
randx commented Dec 3, 2012

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

@randx randx closed this Dec 3, 2012
@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...

@riyad riyad added a commit that referenced this issue Dec 5, 2012
@riyad riyad Add more indexes (see #2159) 8022628
@riyad
Contributor
riyad commented Dec 5, 2012

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. :(

@randx randx added a commit that referenced this issue Mar 21, 2015
@randx randx Merge branch 'add-sidetiq' into 'master'
Add sidetiq dependency

Add the sidetiq gem to the Gemfile to match EE.  See #2159.

cc @sytse

See merge request !1734
33a9f40
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment