825305 - query analysis for bugzilla query checksum 18433674312081415378

Status: RESOLVED WONTFIX

(Data & BI Services Team :: DB: MySQL, task)

Description

[Sheeri Cabral [:sheeri]]

checksum: 18433674312081415378
  sample: SELECT id,type_id,bug_id,attach_id,creation_date,modification_date,requestee_id,setter_id,status FROM flags WHERE bug_id = '574598' AND attach_id IS NOT NULL  ORDER BY id

Comment 1

[Sheeri Cabral [:sheeri]]

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: flags
         type: range
possible_keys: flags_bug_id_idx,fk_flags_attach_id_attachments_attach_id
          key: flags_bug_id_idx
      key_len: 7
          ref: NULL
         rows: 1
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

It's using this key:

 KEY `flags_bug_id_idx` (`bug_id`,`attach_id`),

I tested out indexes on (bug_id,attach_id,id) and even (bug_id,id,attach_id) but MySQL still used the same index with "Using filesort" to order by id.

I also experimented with using creation_date instead of id in the order by, and had the same outcome (including when trying to add indexes).

This is as optimal as we're going to get on this one, unless....is the "order by id" really necessary?

Comment 2

[Frédéric Buclin]

Fun, I cannot figure out what calls this query. I first thought about Bugzilla::Attachment::flag_types(), but the SQL query has attach_id IS NOT NULL instead of attach_id = 123456. This code isn't for new attachments either as it's looking into existing flags. Weird.

Comment 3

[Sheeri Cabral [:sheeri]]

Frederic, did you ever figure out where this code was called from?

Comment 4

[Brandon Johnson [:cyborgshadow]]

Just a refresh on this. Did we ever figure out where this query is called from?

Comment 5

[:glob ✱]

hitting Bugzilla::Flag->match() providing target_type set to 'bug' will generate that SQL.
my guess is the preload in Bugzilla::Attachments::get_attachments_by_bug

i can't see any easy way to drop that ORDER BY :(

Comment 6

[Brandon Johnson [:cyborgshadow]]

Thanks Byron. In that case, we'll close this out as is.