query analysis for bugzilla query checksum 1300338521566888094

RESOLVED INVALID

Status

Data & BI Services Team
DB: MySQL
RESOLVED INVALID
6 years ago
4 years ago

People

(Reporter: sheeri, Assigned: sheeri)

Tracking

Details

(Whiteboard: change so query matches index order)

(Assignee)

Description

6 years ago
sample: SELECT DISTINCT grantor_id, member_id
           FROM group_group_map
          WHERE grant_type = 0
(Assignee)

Comment 1

6 years ago
mysql> explain SELECT DISTINCT grantor_id, member_id
    ->            FROM group_group_map
    ->           WHERE grant_type = 0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: group_group_map
         type: range
possible_keys: NULL
          key: group_group_map_member_id_idx
      key_len: 7
          ref: NULL
         rows: 345
        Extra: Using where; Using index for group-by; Using temporary
1 row in set (0.00 sec)
(Assignee)

Comment 2

6 years ago
group_group_map_member_id_idx is:
  UNIQUE KEY `group_group_map_member_id_idx` (`member_id`,`grantor_id`,`grant_type`),

you could get rid of the "Using temporary" - which is the only "bad" thing in the Extra field - by selecting the fields in the same order as the index....

So I'd recommend *either* changing the unique key to be (grantor_id,member_id,grant_type) to match the query, XOR changing the query to match the order of the index:

mysql> explain SELECT DISTINCT member_id ,grantor_id
           FROM group_group_map
           WHERE grant_type = 0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: group_group_map
         type: range
possible_keys: NULL
          key: group_group_map_member_id_idx
      key_len: 7
          ref: NULL
         rows: 345
        Extra: Using where; Using index for group-by
1 row in set (0.00 sec)
(Assignee)

Updated

6 years ago
Whiteboard: change so query matches index order
(Assignee)

Updated

6 years ago
Assignee: server-ops-database → scabral

Comment 3

6 years ago
I get something different:

MariaDB [bugs_gcc_test]> explain SELECT DISTINCT grantor_id, member_id FROM group_group_map WHERE grant_type = 0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: group_group_map
         type: index
possible_keys: NULL
          key: fk_group_group_map_grantor_id_groups_id
      key_len: 3
          ref: NULL
         rows: 32
        Extra: Using where; Using index
1 row in set (0.00 sec)

Comment 4

6 years ago
And for the record, this SQL query comes from Bugzilla::User::groups().
(Assignee)

Comment 5

6 years ago
The difference is you're using MariaDB 5.5. I first optimize against currently running production, which is MySQL 5.1 right now. MariaDB has a different optimizer.

Comment 6

6 years ago
(In reply to Sheeri Cabral [:sheeri] from comment #2)
> So I'd recommend *either* changing the unique key to be
> (grantor_id,member_id,grant_type) to match the query

As the WHERE part is done against grant_type, I would guess that grant_type should be the first key of the index, so that the optimizer can quickly restrict the list. Wouldn't this make more sense?
(Assignee)

Comment 7

6 years ago
Hrm, the cardinality would suggest otherwise, but it's worth a try:

mysql> select count(*),grant_type from group_group_map group by grant_type;
+----------+------------+
| count(*) | grant_type |
+----------+------------+
|      149 |          0 |
|      102 |          1 |
|       93 |          2 |
+----------+------------+
3 rows in set (0.00 sec)

Over 43% of the grant_types are 0, so it doesn't make sense that MySQL would choose that index (mysql usually uses an index if about 15-20% or less matches, otherwise it just does a full index scan because it's faster).

But it's worth a shot, I'll experiment and see what I come up with.
(Assignee)

Comment 8

6 years ago
I played with the following indexes:

  KEY `grant_type` (`grant_type`),
  KEY `grant_type_2` (`grant_type`,`member_id`,`grantor_id`),
  KEY `grant_type_3` (`grant_type`,`grantor_id`,`member_id`),

MySQL would choose (grant_type):

mysql> explain SELECT DISTINCT grantor_id, member_id FROM group_group_map WHERE grant_type = 0\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: group_group_map
         type: ref
possible_keys: grant_type,grant_type_2,grant_type_3
          key: grant_type
      key_len: 1
          ref: const
         rows: 149
        Extra: Using where; Using index; Using temporary
1 row in set (0.00 sec)

This looks at more rows (149 vs. 32 rows) and requires the use of a temporary table for more sorting. My original recommendation stands.
(Assignee)

Comment 9

5 years ago
Last night we upgraded to MariaDB 5.5 as part of the maintenance, so this query checksum is now invalid.
Status: NEW → RESOLVED
Last Resolved: 5 years ago
Resolution: --- → INVALID
Product: mozilla.org → Data & BI Services Team
You need to log in before you can comment on or make changes to this bug.