526142 - [MS-SQL] missing multi-field index on flags table

Status: ASSIGNED

(Bugzilla :: Database, defect)

Description

[Michael Thomas (Mockodin)]

Flags table should have a multi-field index on flags.bug_id, flags.type_id

Under MSSQL adding this index decreased execution time of Boolean searches by approximately 70%. This issue was encountered with Bugzilla CVS tip against a MSSQL 2008 Database. Buglist.cgi processing time dropped to 5 second from 32 seconds after applying the index. Additionally bug 521882 should be reevaluated as the same query gets an equal performance increase by changing the join in Search.pm. After applying both correction buglist.cgi returned results in under 3 seconds.

Independently either fix reduced the execution time by 75-85%, combined the reduction was 90-95%.

Comment 1

[Michael Thomas (Mockodin)]

Attachment: v1

Comment 2

[Max Kanat-Alexander]

Comment on attachment 409968 [details] [diff] [review]
v1

>Index: bugzilla/Bugzilla/DB/Schema.pm
>+            flags_bug_id_type_id_idx => [qw(bug_id type_id)],

  Indexes get only the name of their *first* field as part of their name. If there is already an index with that name, it needs to be dropped and replaced.

Comment 3

[Michael Thomas (Mockodin)]

Given that 'flags_bug_id_idx' is already in use for a different multi-field index deleting it would not seem a good option.

        INDEXES => [
            flags_bug_id_idx       => [qw(bug_id attach_id)],
            flags_setter_id_idx    => ['setter_id'],
            flags_requestee_id_idx => ['requestee_id'],
            flags_type_id_idx      => ['type_id'],
        ]

Reversing the field order and replacing the 'flags_type_id_idx' index is not a good idea either as field order in a composite index makes a difference, least ways is does in mssql.

Suggestion?

Comment 4

[Max Kanat-Alexander]

(In reply to comment #3)
> Suggestion?

  Make an attach_id_idx and remove attach_id from the bug_id index, and put type_id there instead.

  Note that I haven't evaluated any of this yet, so I may change my mind about how these indexes should work.

Comment 5

[Michael Thomas (Mockodin)]

Attachment: v2

Done, I have a concern however that this will merely move issue to a different place now. What queries are relying on the existing index?:

     flags_bug_id_idx       => [qw(bug_id attach_id)]

Comment 6

[Max Kanat-Alexander]

(In reply to comment #5)
> What queries are relying on the existing index?:

  It shouldn't matter, because attach_id should be smaller than bug_id.

Comment 7

[Max Kanat-Alexander]

Comment on attachment 410094 [details] [diff] [review]
v2

I haven't tested this, but even if I do, there needs to be code in Bugzilla::Install::DB that actually updates these indexes.