824998 - query analysis for bugzilla query checksum 607464626713064978

Status: RESOLVED FIXED

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

Description

[Sheeri Cabral [:sheeri]]

checksum: 607464626713064978
fingerprint: select id,name,description,type,custom,mailhead,sortkey,obsolete,enter_bug,buglist,visibility_field_id,visibility_value_id,value_field_id,reverse_desc,is_mandatory from fielddefs where custom = ? and type = ? order by sortkey, name
     sample: SELECT id,name,description,type,custom,mailhead,sortkey,obsolete,enter_bug,buglist,visibility_field_id,visibility_value_id,value_field_id,reverse_desc,is_mandatory FROM fielddefs WHERE custom = '1' AND type = '3'  ORDER BY sortkey, name

Comment 1

[Sheeri Cabral [:sheeri]]

mysql> explain SELECT id,name,description,type,custom,mailhead,sortkey,obsolete,enter_bug,buglist,visibility_field_id,visibility_value_id,value_field_id,reverse_desc,is_mandatory FROM fielddefs WHERE custom = '1' AND type = '3'  ORDER BY sortkey, name\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fielddefs
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 190
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

It's doing a full table scan, and then having to sort. This isn't great. The only saving grace is that the table is only 190 rows.

Here's what the table itself looks like - there's a unique key on name, and there's an index on sortkey, but nothing on the filtering fields of custom or type:

       Table: fielddefs
Create Table: CREATE TABLE `fielddefs` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `description` tinytext NOT NULL,
  `mailhead` tinyint(4) NOT NULL DEFAULT '0',
  `sortkey` smallint(6) NOT NULL,
  `obsolete` tinyint(4) NOT NULL DEFAULT '0',
  `type` smallint(6) NOT NULL DEFAULT '0',
  `custom` tinyint(4) NOT NULL DEFAULT '0',
  `enter_bug` tinyint(4) NOT NULL DEFAULT '0',
  `is_relationship` tinyint(4) NOT NULL DEFAULT '0',
  `reverse_relationship_desc` tinytext,
  `visibility_field_id` mediumint(9) DEFAULT NULL,
  `visibility_value_id` smallint(6) DEFAULT NULL,
  `value_field_id` mediumint(9) DEFAULT NULL,
  `buglist` tinyint(4) NOT NULL DEFAULT '0',
  `reverse_desc` tinytext,
  `is_mandatory` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `fielddefs_name_idx` (`name`),
  KEY `fielddefs_sortkey_idx` (`sortkey`),
  KEY `fielddefs_value_field_id_idx` (`value_field_id`),
  KEY `fk_fielddefs_visibility_field_id_fielddefs_id` (`visibility_field_id`),
  KEY `fielddefs_is_mandatory_idx` (`is_mandatory`),
  CONSTRAINT `fk_fielddefs_value_field_id_fielddefs_id` FOREIGN KEY (`value_field_id`) 
REFERENCES `fielddefs` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_fielddefs_visibility_field_id_fielddefs_id` FOREIGN KEY (`visibility_f
ield_id`) REFERENCES `fielddefs` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=239 DEFAULT CHARSET=utf8 PACK_KEYS=1
1 row in set (0.00 sec)

Comment 2

[Sheeri Cabral [:sheeri]]

mysql> select custom,type,count(*) from fielddefs group by custom,type order by 3 desc; 
+--------+------+----------+
| custom | type | count(*) |
+--------+------+----------+
|      1 |    2 |      128 |
|      0 |    0 |       45 |
|      0 |    2 |        9 |
|      1 |    5 |        2 |
|      0 |    8 |        1 |
|      0 |    5 |        1 |
|      0 |    7 |        1 |
|      1 |    4 |        1 |
|      1 |    3 |        1 |
|      1 |    6 |        1 |
+--------+------+----------+
10 rows in set (0.00 sec)

If we added an index on (custom,type,sortkey), it would use that index (even for custom=1 and type=2, because of the sortkey), and the EXPLAIN would look like this:


*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fielddefs
         type: ref
possible_keys: custom
          key: custom
      key_len: 3
          ref: const,const
         rows: 1
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

Comment 3

[Sheeri Cabral [:sheeri]]

Recommendation: add an index:

alter table fielddefs add index (custom,type,sortkey);

Comment 4

[:glob ✱]

sweet, thanks sheeri.

as all schema changes to the bmo database need to happen via code, i've filed bug 825805 to implement this.

Comment 5

[Frédéric Buclin]

(In reply to Sheeri Cabral [:sheeri] from comment #0)
>      sample: SELECT
> id,name,description,type,custom,mailhead,sortkey,obsolete,enter_bug,buglist,
> visibility_field_id,visibility_value_id,value_field_id,reverse_desc,
> is_mandatory FROM fielddefs WHERE custom = '1' AND type = '3'  ORDER BY
> sortkey, name

I have a problem with this query: visibility_value_id doesn't exist in this table! Where does it come from?

Comment 6

[Frédéric Buclin]

(In reply to Frédéric Buclin from comment #5)
> I have a problem with this query: visibility_value_id doesn't exist in this
> table! Where does it come from?

Ah, scratch that! this column was still present in Bugzilla 4.0.

Comment 7

[Sheeri Cabral [:sheeri]]

(In reply to Byron Jones ‹:glob› from comment #4)
> sweet, thanks sheeri.
> 
> as all schema changes to the bmo database need to happen via code, i've
> filed bug 825805 to implement this.

Great! I will close this bug because there's no more work needed on it.