Add Index to issue.parent_id and Re-write SQL.

#17 Merged
Repository
akahane92
Branch
RewriteSQL
Repository
tkusukawa
Branch
RewriteSQL
Author
  1. Kuniharu Akahane
Reviewers
Description

Problem

  • Worktime plugin responce becomes quite slow when it handles many rows on issues and journals table. For example, it takes 10 to 12sec to get a page response from the top page and most operation of work_time on our Redmine instance.

  • Our Redmine tables and rows as follows:

        +-------------------------------------+--------+
        | Redmine v2.5 Tables                 | Rows   |
        +-------------------------------------+--------+
        | projects                            |     144|
        | issues                              |  131859|
        | journals                            |  436642|
        | time_entries                        |  132542|
        | user_issue_months                   |    1306|
        | wt_daily_memos                      |      24|
        | wt_holidays                         |       0|
        | wt_member_orders                    |    1009|
        | wt_project_orders                   |      93|
        | wt_ticket_relays                    |    7922|
        +-------------------------------------+--------+

Reasons

  • [1] Lack of table index.
  • [2] Inefficient SQL.

Changes

  • [1] Add a index on issues.parent_id.
  • [2] Re-write SQL from dependent subquery to simple UNION statement.
SELECT 
    issues.*
FROM
    issues
WHERE
    issues.author_id = 6
        AND issues.created_on >= '2014-10-30 00:00:00'
        AND issues.created_on <  '2014-10-31 00:00:00' 
UNION
SELECT DISTINCT
    issues.*
FROM
    issues
        INNER JOIN
    journals ON issues.id = journals.journalized_id
WHERE
    journals.journalized_type = 'Issue'
        AND journals.user_id = 6
        AND journals.created_on >= '2014-10-30 00:00:00'
        AND journals.created_on <  '2014-10-31 00:00:00' ;

Results

Two changes reduce the work_time operation response time from approx. 12sec to 1sec.

  • BEFORE 12226 ms
     Completed 302 Found in 9624.6ms (ActiveRecord: 9262.6ms)
     Completed 200 OK    in 2601.4ms (Views: 2237.0ms | ActiveRecord: 151.7ms)
  • AFTER 4693.3 ms - Change[1] ( cut 7532.7 ms)
     Completed 302 Found in 1862.3ms (ActiveRecord: 1645.6ms)
     Completed 200 OK    in 2831.0ms (Views: 2460.6ms | ActiveRecord: 164.1ms)
  • AFTER 951.1 ms - Change[2] ( cit 3742.2 ms)
     Completed 302 Found in 124.0ms (ActiveRecord: 44.8ms)
     Completed 200 OK    in 827.1ms (Views: 709.4ms | ActiveRecord: 46.0ms)

Notes

  • Tested only MySQL 5.6, Ruby 2.1.3 .
  • Need more feed-back from other DBMS Users.

Comments (0)