Query Planner chooses wrong index for ORDER BY ... LIMIT

Bug #1362212 reported by Markus Peter on 2014-08-27
18
This bug affects 4 people
Affects Status Importance Assigned to Milestone
Percona Server
Undecided
Unassigned

Bug Description

Used Percona mysqld Ver 5.6.19-67.0 for debian-linux-gnu on x86_64 (Percona Server (GPL), Release 67.0, Revision 618)

This is probably an upstream bug already (as it's clearly a query planner fail), but as we're using Percona I probably better report it here to avoid discussions with the MySQL maintainers on bug reports for software not "their own". Maybe you can report this upstream or so, don't know how issues like there are coordinated across the different MySQL forks.

We have a table

  CREATE TABLE `suggestion` (
  `user_id` int(10) unsigned NOT NULL,
  `type` enum('user','group','board','newbie','dating') COLLATE latin1_german2_ci NOT NULL,
  `obj_id` int(10) unsigned NOT NULL,
  `rejected` enum('y','n') COLLATE latin1_german2_ci NOT NULL DEFAULT 'n',
  `flags` int(10) unsigned NOT NULL DEFAULT '0',
  `data` varbinary(32768) NOT NULL DEFAULT '',
  `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`user_id`,`type`,`obj_id`),
  KEY `user_id` (`user_id`,`rejected`),
  KEY `type` (`type`,`created`),
  KEY `rejected` (`rejected`,`type`,`created`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;

The table is about 12 GB of data and 40 million rows.

The query:

   SELECT obj_id, flags, data, type FROM suggestion WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC LIMIT 50;

takes about 4 seconds on our system since a Percona 5.6 upgrade from 5.1 as the wrong index ('type') is chosen by the query planner instead of the more optimal PRIMARY KEY. Simply leaving out the "LIMIT 40" fixes the problem - then the proper index for that SELECT (the PRIMARY) will be used. We're currently working around the issue with a FORCE INDEX, but it'd be nice, if the problem could be properly fixed in the planner itself. The query planner obviously re-evaluates the chosen plan due to a low LIMIT value and then chooses a plan with a very high cost as evident from the plan trace:

Query Plan Trace is here:

| SELECT obj_id, flags, data, type FROM suggestion WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC LIMIT 50 | {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `suggestion`.`obj_id` AS `obj_id`,`suggestion`.`flags` AS `flags`,`suggestion`.`data` AS `data`,`suggestion`.`type` AS `type` from `suggestion` where ((`suggestion`.`user_id` = '7723036') and (`suggestion`.`type` in ('newbie','group')) and (`suggestion`.`rejected` = 'n') and (`suggestion`.`flags` = 0)) order by `suggestion`.`type` desc,`suggestion`.`created` desc limit 50"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`suggestion`.`user_id` = '7723036') and (`suggestion`.`type` in ('newbie','group')) and (`suggestion`.`rejected` = 'n') and (`suggestion`.`flags` = 0))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`suggestion`.`user_id` = '7723036') and (`suggestion`.`type` in ('newbie','group')) and (`suggestion`.`rejected` = 'n') and multiple equal(0, `suggestion`.`flags`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`suggestion`.`user_id` = '7723036') and (`suggestion`.`type` in ('newbie','group')) and (`suggestion`.`rejected` = 'n') and multiple equal(0, `suggestion`.`flags`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`suggestion`.`user_id` = '7723036') and (`suggestion`.`type` in ('newbie','group')) and (`suggestion`.`rejected` = 'n') and multiple equal(0, `suggestion`.`flags`))"
                }
              ]
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`suggestion`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`suggestion`",
                "field": "user_id",
                "equals": "'7723036'",
                "null_rejecting": false
              },
              {
                "table": "`suggestion`",
                "field": "user_id",
                "equals": "'7723036'",
                "null_rejecting": false
              },
              {
                "table": "`suggestion`",
                "field": "rejected",
                "equals": "'n'",
                "null_rejecting": false
              },
              {
                "table": "`suggestion`",
                "field": "rejected",
                "equals": "'n'",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`suggestion`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 40698474,
                    "cost": 8.55e6
                  },
                  "potential_range_indices": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "user_id",
                        "type",
                        "obj_id"
                      ]
                    },
                    {
                      "index": "user_id",
                      "usable": true,
                      "key_parts": [
                        "user_id",
                        "rejected",
                        "type",
                        "obj_id"
                      ]
                    },
                    {
                      "index": "type",
                      "usable": true,
                      "key_parts": [
                        "type",
                        "created",
                        "user_id",
                        "obj_id"
                      ]
                    },
                    {
                      "index": "rejected",
                      "usable": true,
                      "key_parts": [
                        "rejected",
                        "type",
                        "created",
                        "user_id",
                        "obj_id"
                      ]
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY",
                        "ranges": [
                          "7723036 <= user_id <= 7723036 AND group <= type <= group",
                          "7723036 <= user_id <= 7723036 AND newbie <= type <= newbie"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 62,
                        "cost": 14.522,
                        "chosen": true
                      },
                      {
                        "index": "user_id",
                        "ranges": [
                          "7723036 <= user_id <= 7723036 AND n <= rejected <= n AND group <= type <= group",
                          "7723036 <= user_id <= 7723036 AND n <= rejected <= n AND newbie <= type <= newbie"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 60,
                        "cost": 74.01,
                        "chosen": false,
                        "cause": "cost"
                      },
                      {
                        "index": "type",
                        "ranges": [
                          "group <= type <= group",
                          "newbie <= type <= newbie"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 39087668,
                        "cost": 4.69e7,
                        "chosen": false,
                        "cause": "cost"
                      },
                      {
                        "index": "rejected",
                        "ranges": [
                          "n <= rejected <= n AND group <= type <= group",
                          "n <= rejected <= n AND newbie <= type <= newbie"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 40698474,
                        "cost": 4.88e7,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "PRIMARY",
                      "rows": 62,
                      "ranges": [
                        "7723036 <= user_id <= 7723036 AND group <= type <= group",
                        "7723036 <= user_id <= 7723036 AND newbie <= type <= newbie"
                      ]
                    },
                    "rows_for_plan": 62,
                    "cost_for_plan": 14.522,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`suggestion`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "PRIMARY",
                      "rows": 62,
                      "cost": 74.4,
                      "chosen": true
                    },
                    {
                      "access_type": "ref",
                      "index": "user_id",
                      "rows": 60,
                      "cost": 72,
                      "chosen": true
                    },
                    {
                      "access_type": "ref",
                      "index": "rejected",
                      "rows": 4.07e7,
                      "cost": 9.38e6,
                      "chosen": false
                    },
                    {
                      "access_type": "range",
                      "rows": 60,
                      "cost": 26.922,
                      "chosen": true
                    }
                  ]
                },
                "cost_for_plan": 26.922,
                "rows_for_plan": 60,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`suggestion`.`flags` = 0) and (`suggestion`.`user_id` = '7723036') and (`suggestion`.`type` in ('newbie','group')) and (`suggestion`.`rejected` = 'n'))",
              "attached_conditions_computation": [
                {
                  "table": "`suggestion`",
                  "rechecking_index_usage": {
                    "recheck_reason": "low_limit",
                    "limit": 50,
                    "row_estimate": 60,
                    "range_analysis": {
                      "table_scan": {
                        "rows": 40698474,
                        "cost": 4.88e7
                      },
                      "potential_range_indices": [
                        {
                          "index": "PRIMARY",
                          "usable": false,
                          "cause": "not_applicable"
                        },
                        {
                          "index": "user_id",
                          "usable": false,
                          "cause": "not_applicable"
                        },
                        {
                          "index": "type",
                          "usable": true,
                          "key_parts": [
                            "type",
                            "created",
                            "user_id",
                            "obj_id"
                          ]
                        },
                        {
                          "index": "rejected",
                          "usable": true,
                          "key_parts": [
                            "rejected",
                            "type",
                            "created",
                            "user_id",
                            "obj_id"
                          ]
                        }
                      ],
                      "setup_range_conditions": [
                      ],
                      "group_index_range": {
                        "chosen": false,
                        "cause": "cannot_do_reverse_ordering"
                      },
                      "analyzing_range_alternatives": {
                        "range_scan_alternatives": [
                          {
                            "index": "type",
                            "ranges": [
                              "group <= type <= group",
                              "newbie <= type <= newbie"
                            ],
                            "index_dives_for_eq_ranges": true,
                            "rowid_ordered": false,
                            "using_mrr": false,
                            "index_only": false,
                            "rows": 39087668,
                            "cost": 4.69e7,
                            "chosen": true
                          },
                          {
                            "index": "rejected",
                            "ranges": [
                              "n <= rejected <= n AND group <= type <= group",
                              "n <= rejected <= n AND newbie <= type <= newbie"
                            ],
                            "index_dives_for_eq_ranges": true,
                            "rowid_ordered": false,
                            "using_mrr": false,
                            "index_only": false,
                            "rows": 40698474,
                            "cost": 4.88e7,
                            "chosen": false,
                            "cause": "cost"
                          }
                        ]
                      },
                      "chosen_range_access_summary": {
                        "range_access_plan": {
                          "type": "range_scan",
                          "index": "type",
                          "rows": 39087668,
                          "ranges": [
                            "group <= type <= group",
                            "newbie <= type <= newbie"
                          ]
                        },
                        "rows_for_plan": 39087668,
                        "cost_for_plan": 4.69e7,
                        "chosen": true
                      }
                    }
                  }
                }
              ],
              "attached_conditions_summary": [
                {
                  "table": "`suggestion`",
                  "attached": "((`suggestion`.`flags` = 0) and (`suggestion`.`user_id` = '7723036') and (`suggestion`.`type` in ('newbie','group')) and (`suggestion`.`rejected` = 'n'))"
                }
              ]
            }
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`suggestion`.`type` desc,`suggestion`.`created` desc",
              "items": [
                {
                  "item": "`suggestion`.`type`"
                },
                {
                  "item": "`suggestion`.`created`"
                }
              ],
              "resulting_clause_is_simple": true,
              "resulting_clause": "`suggestion`.`type` desc,`suggestion`.`created` desc"
            }
          },
          {
            "refine_plan": [
              {
                "table": "`suggestion`",
                "pushed_index_condition": "((`suggestion`.`user_id` = '7723036') and (`suggestion`.`type` in ('newbie','group')))",
                "table_condition_attached": "((`suggestion`.`flags` = 0) and (`suggestion`.`rejected` = 'n'))",
                "access_type": "range"
              }
            ]
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "index_order_summary": {
                "table": "`suggestion`",
                "index_provides_order": true,
                "order_direction": "desc",
                "index": "type",
                "plan_changed": false
              }
            }
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
} | 0 | 0 |

This is probably a yet another consequence of bad fix for http://bugs.mysql.com/bug.php?id=28404 that still remains (and gives preference to index that allows to avoid ORDER BY with LIMIT N when N is small). it is hard to say is it a new or known bug, at least without a complete repeatable test case.

I'll try to create one and report upstream bug for it, and then let Oracle engineers decide.

Changed in percona-server:
assignee: nobody → Muhammad Irfan (muhammad-irfan)
Muhammad Irfan (muhammad-irfan) wrote :
Download full text (17.2 KiB)

I populated table with test data but was not able to reproduce the problem you described. Can you please share your my.cnf file and some test data if possible.

mysql [localhost] {root} (test) > show global variables like '%version%';
+-------------------------+--------------------------------------------------+
| Variable_name | Value |
+-------------------------+--------------------------------------------------+
| innodb_version | 5.6.19-rel67.0 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.19-67.0 |
| version_comment | Percona Server (GPL), Release 67.0, Revision 618 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+--------------------------------------------------+

mysql [localhost] {root} (test) > show table status like 'suggestion'\G
*************************** 1. row ***************************
           Name: suggestion
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1021746
 Avg_row_length: 107
    Data_length: 109723648
Max_data_length: 0
   Index_length: 85671936
      Data_free: 7340032
 Auto_increment: NULL
    Create_time: 2014-09-16 03:15:39
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_german2_ci
       Checksum: NULL
 Create_options:
        Comment:

$ -rw-rw---- 1 root root 41K Sep 16 03:15 suggestion.frm
$ rw-rw---- 1 root root 224M Sep 16 11:23 suggestion.ibd

mysql [localhost] {root} (test) > EXPLAIN SELECT obj_id, flags, data, type FROM suggestion WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC LIMIT 50;
+----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+
| 1 | SIMPLE | suggestion | range | PRIMARY,user_id,type,rejected | PRIMARY | 5 | NULL | 2 | Using where; Using filesort |
+----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql [localhost] {root} (test) > EXPLAIN SELECT obj_id, flags, data, type FROM suggestion WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC;
+----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra ...

Changed in percona-server:
assignee: Muhammad Irfan (muhammad-irfan) → nobody
status: New → Incomplete
Markus Peter (q-warp) wrote :
Download full text (4.3 KiB)

I don't yet know how to best share example data as I cannot share the real data publicly. For completeness, here's some more data from our system:

mysql> show global variables like '%version%';
+-------------------------+--------------------------------------------------+
| Variable_name | Value |
+-------------------------+--------------------------------------------------+
| innodb_version | 5.6.19-67.0 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.19-67.0-log |
| version_comment | Percona Server (GPL), Release 67.0, Revision 618 |
| version_compile_machine | x86_64 |
| version_compile_os | debian-linux-gnu |
+-------------------------+--------------------------------------------------+

mysql> show table status like 'suggestion'\G
*************************** 1. row ***************************
           Name: suggestion
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 44442324
 Avg_row_length: 152
    Data_length: 6759219200
Max_data_length: 0
   Index_length: 5125160960
      Data_free: 7340032
 Auto_increment: NULL
    Create_time: 2014-08-27 11:08:14
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_german2_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

The explains you did:

mysql> EXPLAIN SELECT obj_id, flags, data, type FROM suggestion WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC;
+----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+
| 1 | SIMPLE | suggestion | range | PRIMARY,user_id,type,rejected | PRIMARY | 5 | NULL | 62 | Using where; Using filesort |
+----+-------------+------------+-------+-------------------------------+---------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT obj_id, flags, data, type FROM suggestion WHERE user_id='7723036' AND type IN ('newbie', 'group') AND rejected='n' AND flags=0 ORDER BY type DESC, created DESC LIMIT 50;
+----+-------------+------------+-------+-------------------------------+------+---------+------+----------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+-------------------------------+------+---------+------+----------+------------------------------------+
| 1 | SIMP...

Read more...

Changed in percona-server:
status: Incomplete → New
Muhammad Irfan (muhammad-irfan) wrote :

I did tried it again by experimenting different values for innodb_stats_persistent_sample_pages and run analyze table few times, different values for LIMIT clause but no luck so far. EXPLAIN output shows query always uses PRIMARY KEY.
Data distribution may be important here. Can you please show me output of

mysql> SELECT type, COUNT(type) FROM suggestion GROUP BY type;
mysql> SELECT rejected, COUNT(rejected) FROM suggestion GROUP BY rejected;
mysql> SELECT flags, COUNT(flags) FROM suggestion GROUP BY flags;

And
mysql> SHOW KEYS FROM suggestion;

Also, may be you can try to run ANALYZE TABLE and check if it improves query execution path.

Changed in percona-server:
status: New → Incomplete
Markus Peter (q-warp) wrote :
Download full text (4.0 KiB)

We tried ANALYZE TABLE first, before even submitting the bug report, did not help.

Here's more information about the table structure:
mysql> SELECT type, COUNT(type) FROM suggestion GROUP BY type;
+--------+-------------+
| type | COUNT(type) |
+--------+-------------+
| user | 12954761 |
| group | 12431541 |
| board | 1395314 |
| newbie | 11091696 |
| dating | 4094086 |
+--------+-------------+
5 rows in set (4 min 52.40 sec)

mysql> SELECT rejected, COUNT(rejected) FROM suggestion GROUP BY rejected;
+----------+-----------------+
| rejected | COUNT(rejected) |
+----------+-----------------+
| y | 1725700 |
| n | 40241894 |
+----------+-----------------+
2 rows in set (4 min 38.46 sec)

mysql> SELECT flags, COUNT(flags) FROM suggestion GROUP BY flags;
+-------+--------------+
| flags | COUNT(flags) |
+-------+--------------+
| 0 | 41959046 |
| 1 | 8152 |
| 2 | 567 |
| 3 | 99 |
+-------+--------------+
4 rows in set (9 min 39.81 sec)

mysql> select count(*) from suggestion where user_id=7723036;
+----------+
| count(*) |
+----------+
| 143 |
+----------+
1 row in set (0.01 sec)

mysql> show keys from suggestion;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| suggestion | 0 | PRIMARY | 1 | user_id | A | 1322871 | NULL | NULL | | BTREE | | |
| suggestion | 0 | PRIMARY | 2 | type | A | 4703541 | NULL | NULL | | BTREE | | |
| suggestion | 0 | PRIMARY | 3 | obj_id | A | 42331875 | NULL | NULL | | BTREE | | |
| suggestion | 1 | user_id | 1 | user_id | A | 1175885 | NULL | NULL | | BTREE | | |
| suggestion | 1 | user_id | 2 | rejected | A | 1209482 | NULL | NULL | | BTREE | | |
| suggestion | 1 | type | 1 | type | A | 2 | NULL | NULL | | BTREE | | |
| suggestion | 1 | type | 2 | created | A | 3848352 | NULL | NULL | | BTREE | | |
| suggestion | 1 | rejected | 1 | rejected | A | 2 | NULL | NULL | | BTREE | | |
| suggestion | 1 | rejected | 2 | type | A | 938 | NULL | NULL | | BTREE | | |
| suggestion | 1 | rejected | ...

Read more...

Changed in percona-server:
status: Incomplete → New
Changed in percona-server:
assignee: nobody → Muhammad Irfan (muhammad-irfan)
Muhammad Irfan (muhammad-irfan) wrote :
Download full text (6.5 KiB)

I still didn't able to reproduce it. I tried to use the same percentage of data distribution among different table fields but no luck yet. It uses the optimal index i.e. PRIMARY KEY for queries regardless used with LIMIT clause or not. It is possible for you to share test data to try to reproduce ?

mysql [localhost] {root} (test) > SHOW GLOBAL VARIABLES LIKE '%version%';
+-------------------------+--------------------------------------------------+
| Variable_name | Value |
+-------------------------+--------------------------------------------------+
| innodb_version | 5.6.19-rel67.0 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.19-67.0 |
| version_comment | Percona Server (GPL), Release 67.0, Revision 618 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+--------------------------------------------------+
7 rows in set (0.00 sec)

mysql [localhost] {root} (test) > show create table suggestion\G
*************************** 1. row ***************************
       Table: suggestion
Create Table: CREATE TABLE `suggestion` (
  `user_id` int(10) unsigned NOT NULL,
  `type` enum('user','group','board','newbie','dating') COLLATE latin1_german2_ci NOT NULL,
  `obj_id` int(10) unsigned NOT NULL,
  `rejected` enum('y','n') COLLATE latin1_german2_ci NOT NULL DEFAULT 'n',
  `flags` int(10) unsigned NOT NULL DEFAULT '0',
  `data` varbinary(32768) NOT NULL DEFAULT '',
  `created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`user_id`,`type`,`obj_id`),
  KEY `user_id` (`user_id`,`rejected`),
  KEY `type` (`type`,`created`),
  KEY `rejected` (`rejected`,`type`,`created`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci

mysql [localhost] {root} (test) > show table status like 'suggestion'\G
*************************** 1. row ***************************
           Name: suggestion
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1836214
 Avg_row_length: 75
    Data_length: 139132928
Max_data_length: 0
   Index_length: 102465536
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2014-10-31 09:10:04
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_german2_ci
       Checksum: NULL
 Create_options:
        Comment:

-rw-rw---- 1 root root 41K Oct 31 09:08 suggestion.frm
-rw-rw---- 1 root root 232M Oct 31 09:13 suggestion.ibd

mysql [localhost] {root} (test) > SELECT type, COUNT(type) FROM suggestion GROUP BY type;
+--------+-------------+
| type | COUNT(type) |
+--------+-------------+
| user | 183817 |
| group | 184566 |
| board | 184760 |
| newbie | 183507 |
| dating | 1107810 |
+--------+-------------+
5 rows in set (0.55 sec)

mysql [localhost] {root} (test) > SELECT rejected, COUNT(rejected) FROM suggestion G...

Read more...

Changed in percona-server:
status: New → Incomplete
assignee: Muhammad Irfan (muhammad-irfan) → nobody
Steffen Boehme (boemm) wrote :

I would like to warm up the issue since we hit the same (bug|behavior) with on of our databases.
I will provide the table structure as well as the current data we work with ...
Of course the queries to and hope you can get some more info out of this ... so here we go ...

Steffen Boehme (boemm) wrote :

This are the data we are hitting this problem ...

Steffen Boehme (boemm) wrote :

Some statements which are related ...
We got this both queries:

mysql> explain select count(downloadco0_.id) as col_0_0_ from DownloadConfirmation downloadco0_ where downloadco0_.platform='EU' and downloadco0_.fulfillerId='5' and (downloadco0_.downloadCompleted is null);
+----+-------------+--------------+------+--------------------------------+--------------------------------+---------+-------------+--------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+--------------------------------+--------------------------------+---------+-------------+--------+------------------------------------+
| 1 | SIMPLE | downloadco0_ | ref | platformFulfillerIdExecutionId | platformFulfillerIdExecutionId | 773 | const,const | 403364 | Using index condition; Using where |
+----+-------------+--------------+------+--------------------------------+--------------------------------+---------+-------------+--------+------------------------------------+
1 row in set (0.00 sec)

mysql> explain select downloadco0_.id as id1_9_, downloadco0_.attempts as attempts2_9_, downloadco0_.dateCreated as dateCrea3_9_, downloadco0_.dateModified as dateModi4_9_, downloadco0_.downloadCompleted as download5_9_, downloadco0_.downloadStarted as download6_9_, downloadco0_.executionId as executio7_9_, downloadco0_.fileId as fileId8_9_, downloadco0_.fileSize as fileSize9_9_, downloadco0_.fulfillerId as fulfill10_9_, downloadco0_.platform as platfor11_9_ from DownloadConfirmation downloadco0_ where downloadco0_.platform='EU' and downloadco0_.fulfillerId='12' and downloadco0_.executionId='103906' and (downloadco0_.downloadCompleted is not null) order by downloadco0_.downloadCompleted asc limit 1;
+----+-------------+--------------+------+--------------------------------+--------------------------------+---------+-------------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+--------------------------------+--------------------------------+---------+-------------------+------+----------------------------------------------------+
| 1 | SIMPLE | downloadco0_ | ref | platformFulfillerIdExecutionId | platformFulfillerIdExecutionId | 824 | const,const,const | 977 | Using index condition; Using where; Using filesort |
+----+-------------+--------------+------+--------------------------------+--------------------------------+---------+-------------------+------+----------------------------------------------------+
1 row in set (0.00 sec)

Steffen Boehme (boemm) wrote :

Ok, for better reading the both explains again:

mysql> explain select count(downloadco0_.id) as col_0_0_ from DownloadConfirmation downloadco0_ where downloadco0_.platform='EU' and downloadco0_.fulfillerId='5' and (downloadco0_.downloadCompleted is null)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: downloadco0_
         type: ref
possible_keys: platformFulfillerIdExecutionId
          key: platformFulfillerIdExecutionId
      key_len: 773
          ref: const,const
         rows: 403364
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

mysql> explain select downloadco0_.id as id1_9_, downloadco0_.attempts as attempts2_9_, downloadco0_.dateCreated as dateCrea3_9_, downloadco0_.dateModified as dateModi4_9_, downloadco0_.downloadCompleted as download5_9_, downloadco0_.downloadStarted as download6_9_, downloadco0_.executionId as executio7_9_, downloadco0_.fileId as fileId8_9_, downloadco0_.fileSize as fileSize9_9_, downloadco0_.fulfillerId as fulfill10_9_, downloadco0_.platform as platfor11_9_ from DownloadConfirmation downloadco0_ where downloadco0_.platform='EU' and downloadco0_.fulfillerId='12' and downloadco0_.executionId='103906' and (downloadco0_.downloadCompleted is not null) order by downloadco0_.downloadCompleted asc limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: downloadco0_
         type: ref
possible_keys: platformFulfillerIdExecutionId
          key: platformFulfillerIdExecutionId
      key_len: 824
          ref: const,const,const
         rows: 977
        Extra: Using index condition; Using where; Using filesort
1 row in set (0.00 sec)

Steffen Boehme (boemm) wrote :

The first query performs bad, so we added this index:

alter table DownloadConfirmation add index (downloadCompleted, platform, fulfillerId, executionId);

Afterwards the first query was pretty fast, but for the second one the unperformant index was used.

Steffen Boehme (boemm) wrote :

mysql> explain select count(downloadco0_.id) as col_0_0_ from DownloadConfirmation downloadco0_ where downloadco0_.platform='EU' and downloadco0_.fulfillerId='5' and (downloadco0_.downloadCompleted is null)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: downloadco0_
         type: ref
possible_keys: platformFulfillerIdExecutionId,downloadCompleted
          key: downloadCompleted
      key_len: 779
          ref: const,const,const
         rows: 8
        Extra: Using where; Using index
1 row in set (0.00 sec)

Steffen Boehme (boemm) wrote :

mysql> explain select downloadco0_.id as id1_9_, downloadco0_.attempts as attempts2_9_, downloadco0_.dateCreated as dateCrea3_9_, downloadco0_.dateModified as dateModi4_9_, downloadco0_.downloadCompleted as download5_9_, downloadco0_.downloadStarted as download6_9_, downloadco0_.executionId as executio7_9_, downloadco0_.fileId as fileId8_9_, downloadco0_.fileSize as fileSize9_9_, downloadco0_.fulfillerId as fulfill10_9_, downloadco0_.platform as platfor11_9_ from DownloadConfirmation downloadco0_ where downloadco0_.platform='EU' and downloadco0_.fulfillerId='12' and downloadco0_.executionId='103906' and (downloadco0_.downloadCompleted is not null) order by downloadco0_.downloadCompleted asc limit 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: downloadco0_
         type: range
possible_keys: platformFulfillerIdExecutionId,downloadCompleted
          key: downloadCompleted
      key_len: 6
          ref: NULL
         rows: 403364
        Extra: Using where
1 row in set (0.00 sec)

Steffen Boehme (boemm) wrote :

Without the limit 1 the second query performs well too ...

mysql> explain select downloadco0_.id as id1_9_, downloadco0_.attempts as attempts2_9_, downloadco0_.dateCreated as dateCrea3_9_, downloadco0_.dateModified as dateModi4_9_, downloadco0_.downloadCompleted as download5_9_, downloadco0_.downloadStarted as download6_9_, downloadco0_.executionId as executio7_9_, downloadco0_.fileId as fileId8_9_, downloadco0_.fileSize as fileSize9_9_, downloadco0_.fulfillerId as fulfill10_9_, downloadco0_.platform as platfor11_9_ from DownloadConfirmation downloadco0_ where downloadco0_.platform='EU' and downloadco0_.fulfillerId='12' and downloadco0_.executionId='103906' and (downloadco0_.downloadCompleted is not null) order by downloadco0_.downloadCompleted asc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: downloadco0_
         type: ref
possible_keys: platformFulfillerIdExecutionId,downloadCompleted
          key: platformFulfillerIdExecutionId
      key_len: 824
          ref: const,const,const
         rows: 977
        Extra: Using index condition; Using where; Using filesort
1 row in set (0.00 sec)

Steffen Boehme (boemm) wrote :

Even with bigger limit numbers it performs well:

mysql> explain select downloadco0_.id as id1_9_, downloadco0_.attempts as attempts2_9_, downloadco0_.dateCreated as dateCrea3_9_, downloadco0_.dateModified as dateModi4_9_, downloadco0_.downloadCompleted as download5_9_, downloadco0_.downloadStarted as download6_9_, downloadco0_.executionId as executio7_9_, downloadco0_.fileId as fileId8_9_, downloadco0_.fileSize as fileSize9_9_, downloadco0_.fulfillerId as fulfill10_9_, downloadco0_.platform as platfor11_9_ from DownloadConfirmation downloadco0_ where downloadco0_.platform='EU' and downloadco0_.fulfillerId='12' and downloadco0_.executionId='103906' and (downloadco0_.downloadCompleted is not null) order by downloadco0_.downloadCompleted asc limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: downloadco0_
         type: ref
possible_keys: platformFulfillerIdExecutionId,downloadCompleted
          key: platformFulfillerIdExecutionId
      key_len: 824
          ref: const,const,const
         rows: 977
        Extra: Using index condition; Using where; Using filesort
1 row in set (0.00 sec)

Steffen Boehme (boemm) wrote :

Our server information:

mysql> \s
--------------
mysql Ver 14.14 Distrib 5.6.32-78.1, for debian-linux-gnu (x86_64) using readline 6.3

Connection id: 12877
Current database: production_api
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.32-78.1-56-log Percona XtraDB Cluster (GPL), Release rel78.1, Revision 979409a, WSREP version 25.17, wsrep_25.17
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /local/mysql/spreadshirt_eu/mysql.sock
Uptime: 41 days 5 hours 9 min 22 sec

Threads: 2 Questions: 26231 Slow queries: 175 Opens: 84 Flush tables: 1 Open tables: 73 Queries per second avg: 0.007
--------------

Changed in percona-server:
status: Incomplete → New
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.