[MDEV-10460] Join Buffer (flat, BNL) prefered over ref access when few rows are matching in the JOIN - Jira

XML

Word

Printable

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.0.26, 10.1.16
Fix Version/s: 10.1(EOL)
Component/s: Optimizer
Labels:
None

Description

Hi,

After migrating from TokuDB enterprise for MariaDB 5.5.41 to MariaDB 10.1 (with a full reimport of the DB), I'm seeing suboptimal execution plan on 'big' tables, where join buffer (flat, BNL) is chosen over a ref access, even with USE INDEX hint. FORCE INDEX fix the issue.

CREATE TABLE `sc_param_index` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `product_id_key` int(10) unsigned DEFAULT NULL,

  `contact_id_key` int(10) unsigned DEFAULT NULL,

  `franchise_id_key` int(10) unsigned DEFAULT NULL,

  `article_id_key` int(10) unsigned DEFAULT NULL,

  `user_id_key` int(10) unsigned DEFAULT NULL,

  `format_id_key` int(10) unsigned DEFAULT NULL,

  `list_id_key` int(10) unsigned DEFAULT NULL,

  `badge_id_key` int(10) DEFAULT NULL,

  `tag_id_key` int(10) unsigned DEFAULT NULL,

  `i18n_id` int(10) unsigned DEFAULT NULL,

  `param_id` int(10) unsigned NOT NULL DEFAULT '0',

  `value` varchar(600) DEFAULT NULL,

  `value_int` int(11) DEFAULT NULL,

  `value_text` text,

  `value_date` date DEFAULT NULL,

  `contact_id_value` int(10) unsigned DEFAULT NULL,

  `media_id_value` int(10) unsigned DEFAULT NULL,

  `param_select_id_value` int(10) unsigned DEFAULT NULL,

  `country_id_value` int(10) unsigned DEFAULT NULL,

  `language_id_value` int(10) unsigned DEFAULT NULL,

  `product_id_value` int(10) unsigned DEFAULT NULL,

  `franchise_id_value` int(10) unsigned DEFAULT NULL,

  `format_id_value` int(10) unsigned DEFAULT NULL,

  `tag_id_value` int(10) unsigned DEFAULT NULL,

  `ranking` int(10) unsigned DEFAULT NULL,

  `api_id` int(10) unsigned DEFAULT NULL,

  `state` tinyint(4) NOT NULL DEFAULT '0',

  `date_creation` timestamp NULL DEFAULT NULL,

  `user_id_creation` int(10) unsigned DEFAULT NULL,

  `date_last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `user_id_last_update` int(10) unsigned DEFAULT NULL,

  `date_validation` timestamp NULL DEFAULT NULL,

  `user_id_validation` int(10) unsigned DEFAULT NULL,

  `gen_subtype_id` tinyint(3) unsigned NOT NULL,

  `gen_type_id` tinyint(3) unsigned NOT NULL,

  `alt_ranking` int(10) unsigned DEFAULT NULL,

  `alt_ranking_label` varchar(50) DEFAULT NULL,

  `is_favorite` tinyint(1) unsigned DEFAULT NULL,

  `md5` binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',

  `value_date_year` smallint(6) DEFAULT NULL,

  `value_date_month` tinyint(3) unsigned DEFAULT NULL,

  `value_date_day` tinyint(3) unsigned DEFAULT NULL,

  `date_start_year` smallint(6) DEFAULT NULL,

  `date_start_month` tinyint(3) unsigned DEFAULT NULL,

  `date_start_day` tinyint(3) unsigned DEFAULT NULL,

  `date_end_year` smallint(6) DEFAULT NULL,

  `date_end_month` tinyint(3) unsigned DEFAULT NULL,

  `date_end_day` tinyint(3) unsigned DEFAULT NULL,

  `contact_id_value_alt` int(10) DEFAULT NULL,

  `forum_id_key` int(10) unsigned DEFAULT NULL,

  `carousel_item_id_key` int(10) unsigned DEFAULT NULL,

  `list_id_value` int(10) unsigned DEFAULT NULL,

  `value_md5` char(32) NOT NULL DEFAULT '',

  `contest_id_key` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`,`gen_subtype_id`),

  KEY `idx_contact_id_value` (`contact_id_value`) USING BTREE,

  KEY `idx_media_id_value` (`media_id_value`) USING BTREE,

  KEY `idx_product_id_value` (`product_id_value`) USING BTREE,

  KEY `idx_franchise_id_value` (`franchise_id_value`) USING BTREE,

  KEY `idx_tag_id_value` (`tag_id_value`) USING BTREE,

  KEY `idx_user_id_key` (`user_id_key`),

  KEY `contact_id_key` (`contact_id_key`,`param_id`),

  KEY `contact_id_value` (`contact_id_value`,`param_id`) USING BTREE,

  KEY `idx_product_id_key` (`product_id_key`,`param_id`),

  KEY `idx_param_id` (`param_id`,`value`(32)),

  KEY `idx_param_id_product_id` (`param_id`,`product_id_key`),

  KEY `idx_list_id_key` (`list_id_key`,`param_id`),

  KEY `idx_product_id_key_contact_id_value_alt` (`product_id_key`,`contact_id_value_alt`) USING BTREE,

  KEY `contact_id_value_alt` (`contact_id_value_alt`),

  KEY `idx_list_id_value` (`list_id_value`) USING BTREE,

  KEY `forum_id_key` (`forum_id_key`),

  KEY `param_id_2` (`param_id`,`state`,`list_id_key`),

  KEY `param_id_3` (`param_id`,`value_int`),

  KEY `param_id` (`param_id`,`state`,`date_creation`),

  KEY `md5` (`md5`),

  KEY `media_id_value_product` (`media_id_value`,`product_id_key`,`id`),

  KEY `media_id_value_contact` (`media_id_value`,`contact_id_key`,`id`)

) ENGINE=TokuDB AUTO_INCREMENT=375509915 DEFAULT CHARSET=utf8 `compression`='tokudb_small'

/*!50100 PARTITION BY LIST (gen_subtype_id)

(PARTITION p0 VALUES IN (1) ENGINE = TokuDB,

 PARTITION p1 VALUES IN (2) ENGINE = TokuDB,

 PARTITION p2 VALUES IN (3) ENGINE = TokuDB,

 PARTITION p3 VALUES IN (4) ENGINE = TokuDB,

 PARTITION p4 VALUES IN (5) ENGINE = TokuDB,

 PARTITION p5 VALUES IN (6) ENGINE = TokuDB,

 PARTITION p6 VALUES IN (7) ENGINE = TokuDB,

 PARTITION p7 VALUES IN (8) ENGINE = TokuDB,

 PARTITION p8 VALUES IN (9) ENGINE = TokuDB,

 PARTITION p9 VALUES IN (10) ENGINE = TokuDB,

 PARTITION p10 VALUES IN (11) ENGINE = TokuDB,

 PARTITION p11 VALUES IN (12) ENGINE = TokuDB,

 PARTITION p12 VALUES IN (13) ENGINE = TokuDB,

 PARTITION p13 VALUES IN (14) ENGINE = TokuDB,

 PARTITION p14 VALUES IN (15) ENGINE = TokuDB,

 PARTITION p15 VALUES IN (16) ENGINE = TokuDB,

 PARTITION p16 VALUES IN (17) ENGINE = TokuDB,

 PARTITION p17 VALUES IN (18) ENGINE = TokuDB,

 PARTITION p18 VALUES IN (19) ENGINE = TokuDB,

 PARTITION p19 VALUES IN (20) ENGINE = TokuDB,

 PARTITION p20 VALUES IN (21) ENGINE = TokuDB,

 PARTITION p21 VALUES IN (22) ENGINE = TokuDB,

 PARTITION p22 VALUES IN (23) ENGINE = TokuDB,

 PARTITION p23 VALUES IN (24) ENGINE = TokuDB,

 PARTITION p24 VALUES IN (25) ENGINE = TokuDB,

 PARTITION p25 VALUES IN (26) ENGINE = TokuDB,

 PARTITION p26 VALUES IN (27) ENGINE = TokuDB,

 PARTITION p27 VALUES IN (28) ENGINE = TokuDB,

 PARTITION p28 VALUES IN (29) ENGINE = TokuDB,

 PARTITION p29 VALUES IN (30) ENGINE = TokuDB,

 PARTITION p30 VALUES IN (31) ENGINE = TokuDB,

 PARTITION p31 VALUES IN (32) ENGINE = TokuDB,

 PARTITION p32 VALUES IN (33) ENGINE = TokuDB,

 PARTITION p33 VALUES IN (34) ENGINE = TokuDB,

 PARTITION p34 VALUES IN (35) ENGINE = TokuDB,

 PARTITION p35 VALUES IN (36) ENGINE = TokuDB,

 PARTITION p36 VALUES IN (37) ENGINE = TokuDB,

 PARTITION p37 VALUES IN (38) ENGINE = TokuDB,

 PARTITION p38 VALUES IN (39) ENGINE = TokuDB,

 PARTITION p39 VALUES IN (40) ENGINE = TokuDB,

 PARTITION p40 VALUES IN (41) ENGINE = TokuDB,

 PARTITION p41 VALUES IN (42) ENGINE = TokuDB,

 PARTITION p42 VALUES IN (43) ENGINE = TokuDB,

 PARTITION p43 VALUES IN (44) ENGINE = TokuDB,

 PARTITION p44 VALUES IN (45) ENGINE = TokuDB,

 PARTITION p45 VALUES IN (46) ENGINE = TokuDB,

 PARTITION p46 VALUES IN (47) ENGINE = TokuDB,

 PARTITION p47 VALUES IN (48) ENGINE = TokuDB,

 PARTITION p48 VALUES IN (49) ENGINE = TokuDB,

 PARTITION p49 VALUES IN (50) ENGINE = TokuDB,

 PARTITION p50 VALUES IN (51) ENGINE = TokuDB,

 PARTITION p51 VALUES IN (52) ENGINE = TokuDB,

 PARTITION p52 VALUES IN (53) ENGINE = TokuDB,

 PARTITION p53 VALUES IN (54) ENGINE = TokuDB,

 PARTITION p54 VALUES IN (55) ENGINE = TokuDB,

 PARTITION p55 VALUES IN (56) ENGINE = TokuDB,

 PARTITION p56 VALUES IN (57) ENGINE = TokuDB,

 PARTITION p57 VALUES IN (58) ENGINE = TokuDB,

 PARTITION p58 VALUES IN (59) ENGINE = TokuDB,

 PARTITION p59 VALUES IN (60) ENGINE = TokuDB,

 PARTITION p60 VALUES IN (61) ENGINE = TokuDB,

 PARTITION p61 VALUES IN (62) ENGINE = TokuDB,

 PARTITION p62 VALUES IN (63) ENGINE = TokuDB,

 PARTITION p63 VALUES IN (64) ENGINE = TokuDB,

 PARTITION p64 VALUES IN (65) ENGINE = TokuDB,

 PARTITION p65 VALUES IN (66) ENGINE = TokuDB,

 PARTITION p66 VALUES IN (67) ENGINE = TokuDB,

 PARTITION p67 VALUES IN (68) ENGINE = TokuDB,

 PARTITION p68 VALUES IN (69) ENGINE = TokuDB,

 PARTITION p69 VALUES IN (70) ENGINE = TokuDB,

 PARTITION p70 VALUES IN (71) ENGINE = TokuDB,

 PARTITION p71 VALUES IN (72) ENGINE = TokuDB,

 PARTITION p72 VALUES IN (73) ENGINE = TokuDB,

 PARTITION p73 VALUES IN (74) ENGINE = TokuDB,

 PARTITION p74 VALUES IN (75) ENGINE = TokuDB,

 PARTITION p75 VALUES IN (76) ENGINE = TokuDB,

 PARTITION p76 VALUES IN (77) ENGINE = TokuDB,

 PARTITION p77 VALUES IN (78) ENGINE = TokuDB,

 PARTITION p78 VALUES IN (79) ENGINE = TokuDB,

 PARTITION p79 VALUES IN (80) ENGINE = TokuDB,

 PARTITION p80 VALUES IN (81) ENGINE = TokuDB,

 PARTITION p81 VALUES IN (82) ENGINE = TokuDB,

 PARTITION p82 VALUES IN (83) ENGINE = TokuDB,

 PARTITION p83 VALUES IN (84) ENGINE = TokuDB,

 PARTITION p84 VALUES IN (85) ENGINE = TokuDB,

 PARTITION p85 VALUES IN (86) ENGINE = TokuDB,

 PARTITION p86 VALUES IN (87) ENGINE = TokuDB,

 PARTITION p87 VALUES IN (88) ENGINE = TokuDB,

 PARTITION p88 VALUES IN (89) ENGINE = TokuDB,

 PARTITION p89 VALUES IN (90) ENGINE = TokuDB,

 PARTITION p90 VALUES IN (91) ENGINE = TokuDB,

 PARTITION p91 VALUES IN (92) ENGINE = TokuDB,

 PARTITION p92 VALUES IN (93) ENGINE = TokuDB,

 PARTITION p93 VALUES IN (94) ENGINE = TokuDB,

 PARTITION p94 VALUES IN (95) ENGINE = TokuDB,

 PARTITION p95 VALUES IN (96) ENGINE = TokuDB,

 PARTITION p96 VALUES IN (97) ENGINE = TokuDB,

 PARTITION p97 VALUES IN (98) ENGINE = TokuDB) */

SHOW INDEX FROM sc_param_index;

+----------------+------------+-----------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table          | Non_unique | Key_name                                | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+----------------+------------+-----------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| sc_param_index |          0 | PRIMARY                                 |            1 | id                   | A         |   133064897 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param_index |          0 | PRIMARY                                 |            2 | gen_subtype_id       | A         |   133064897 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param_index |          1 | idx_contact_id_value                    |            1 | contact_id_value     | A         |    33266224 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | idx_media_id_value                      |            1 | media_id_value       | A         |   133064897 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | idx_product_id_value                    |            1 | product_id_value     | A         |         159 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | idx_franchise_id_value                  |            1 | franchise_id_value   | A         |         175 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | idx_tag_id_value                        |            1 | tag_id_value         | A         |        3954 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | idx_user_id_key                         |            1 | user_id_key          | A         |         262 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | contact_id_key                          |            1 | contact_id_key       | A         |         279 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | contact_id_key                          |            2 | param_id             | A         |        1118 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param_index |          1 | contact_id_value                        |            1 | contact_id_value     | A         |    33266224 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | contact_id_value                        |            2 | param_id             | A         |    33266224 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param_index |          1 | idx_product_id_key                      |            1 | product_id_key       | A         |    22177482 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | idx_product_id_key                      |            2 | param_id             | A         |   133064897 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param_index |          1 | idx_param_id                            |            1 | param_id             | A         |         909 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param_index |          1 | idx_param_id                            |            2 | value                | A         |   133064897 |       32 | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | idx_param_id_product_id                 |            1 | param_id             | A         |        1228 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param_index |          1 | idx_param_id_product_id                 |            2 | product_id_key       | A         |   133064897 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | idx_list_id_key                         |            1 | list_id_key          | A         |         292 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | idx_list_id_key                         |            2 | param_id             | A         |        1171 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param_index |          1 | idx_product_id_key_contact_id_value_alt |            1 | product_id_key       | A         |    22177482 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | idx_product_id_key_contact_id_value_alt |            2 | contact_id_value_alt | A         |    22177482 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | contact_id_value_alt                    |            1 | contact_id_value_alt | A         |         264 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | idx_list_id_value                       |            1 | list_id_value        | A         |         290 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | forum_id_key                            |            1 | forum_id_key         | A         |         271 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | param_id_2                              |            1 | param_id             | A         |         998 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param_index |          1 | param_id_2                              |            2 | state                | A         |        1248 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param_index |          1 | param_id_2                              |            3 | list_id_key          | A         |        1248 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | param_id_3                              |            1 | param_id             | A         |        1118 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param_index |          1 | param_id_3                              |            2 | value_int            | A         |        1118 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | param_id                                |            1 | param_id             | A         |        1229 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param_index |          1 | param_id                                |            2 | state                | A         |        1536 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param_index |          1 | param_id                                |            3 | date_creation        | A         |    44354965 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | md5                                     |            1 | md5                  | A         |   133064897 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param_index |          1 | media_id_value_product                  |            1 | media_id_value       | A         |   133064897 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | media_id_value_product                  |            2 | product_id_key       | A         |   133064897 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | media_id_value_product                  |            3 | id                   | A         |   133064897 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param_index |          1 | media_id_value_contact                  |            1 | media_id_value       | A         |   133064897 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | media_id_value_contact                  |            2 | contact_id_key       | A         |   133064897 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param_index |          1 | media_id_value_contact                  |            3 | id                   | A         |   133064897 |     NULL | NULL   |      | BTREE      |         |               |

+----------------+------------+-----------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

40 rows in set (0.00 sec)

CREATE TABLE `sc_param` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `label` varchar(128) NOT NULL DEFAULT '' COMMENT 'Label du param?tre.',

  `subtype_id` tinyint(3) unsigned NOT NULL COMMENT 'Identifiant de la sous-cat?gorie de produit.',

  `pos` tinyint(3) unsigned DEFAULT NULL COMMENT 'Position pour d?terminer l''ordre d''affichage (ORDER DESC).',

  `tag` varchar(25) DEFAULT NULL COMMENT 'Cat?gorie de param?tre.',

  `max` tinyint(3) unsigned DEFAULT NULL COMMENT 'Nombre de valeurs accept?es pour ce param?tre (NULL = illimit?).',

  `is_i18n` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Flag pour indiquer la r?gionalisation du param?tre (0 = la valeur est unique pour tous les pays).',

  `is_alt_ranked` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'if param acception altranking',

  `is_format` tinyint(3) unsigned NOT NULL DEFAULT '0',

  `ranking_type` tinyint(3) unsigned DEFAULT NULL,

  `type_data` varchar(10) DEFAULT NULL COMMENT 'Type de valeur.',

  `related_to` varchar(20) DEFAULT NULL COMMENT 'Nom de la table servant ? faire la jointure dans sc_index_param',

  `rules` text COMMENT 'use with related_to (options)',

  `date_creation` timestamp NULL DEFAULT NULL,

  `date_last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `json_rules` varchar(500) DEFAULT NULL,

  `is_contact_affinity` tinyint(3) unsigned NOT NULL DEFAULT '0',

  `_tmp_id` int(10) unsigned DEFAULT NULL,

  `_old_id` int(10) unsigned DEFAULT NULL COMMENT 'Identifiant de l''ancienne base. Temporaire. A effacer apr?s migration.',

  PRIMARY KEY (`id`),

  KEY `fk_param__subtype_id` (`subtype_id`),

  KEY `idx_param__label_subtype_id` (`label`,`subtype_id`) USING BTREE,

  KEY `migration_old_id` (`_old_id`),

  KEY `idx_id_tag` (`id`,`tag`),

  KEY `idx_tag` (`tag`,`id`) USING BTREE,

  KEY `tag` (`tag`,`subtype_id`),

  KEY `idx_id_affinity` (`id`,`is_contact_affinity`),

  KEY `idx_affinity_id` (`is_contact_affinity`,`id`)

) ENGINE=TokuDB AUTO_INCREMENT=483 DEFAULT CHARSET=utf8 `compression`='tokudb_small';

SHOW INDEX FROM sc_param;

+----------+------------+-----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table    | Non_unique | Key_name                    | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+----------+------------+-----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| sc_param |          0 | PRIMARY                     |            1 | id                  | A         |         425 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param |          1 | fk_param__subtype_id        |            1 | subtype_id          | A         |          85 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param |          1 | idx_param__label_subtype_id |            1 | label               | A         |         425 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param |          1 | idx_param__label_subtype_id |            2 | subtype_id          | A         |         425 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param |          1 | migration_old_id            |            1 | _old_id             | A         |         141 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param |          1 | idx_id_tag                  |            1 | id                  | A         |         425 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param |          1 | idx_id_tag                  |            2 | tag                 | A         |         425 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param |          1 | idx_tag                     |            1 | tag                 | A         |         425 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param |          1 | idx_tag                     |            2 | id                  | A         |         425 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param |          1 | tag                         |            1 | tag                 | A         |         425 |     NULL | NULL   | YES  | BTREE      |         |               |

| sc_param |          1 | tag                         |            2 | subtype_id          | A         |         425 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param |          1 | idx_id_affinity             |            1 | id                  | A         |         425 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param |          1 | idx_id_affinity             |            2 | is_contact_affinity | A         |         425 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param |          1 | idx_affinity_id             |            1 | is_contact_affinity | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |

| sc_param |          1 | idx_affinity_id             |            2 | id                  | A         |         425 |     NULL | NULL   |      | BTREE      |         |               |

+----------+------------+-----------------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

EXPLAIN SELECT * FROM `sc_param` AS `Parameters` LEFT JOIN `sc_param_index` AS `ParametersIndices` ON `ParametersIndices`.`gen_subtype_id` = 4 AND `ParametersIndices`.`product_id_key` = 192261 AND (`ParametersIndices`.`state` >= 0) AND `Parameters`.`id` = `ParametersIndices`.`param_id` WHERE `Parameters`.`subtype_id` = 4 ORDER BY `Parameters`.`pos` ASC, `ParametersIndices`.`alt_ranking` ASC, IF(ParametersIndices.ranking IS NOT NULL, 0, 1) ASC, `ParametersIndices`.`ranking` ASC, `ParametersIndices`.`id` ASC;

+------+-------------+-------------------+------+--------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+-------+------+-------------------------------------------------+

| id   | select_type | table             | type | possible_keys                                                                                                                  | key                  | key_len | ref   | rows | Extra                                           |

+------+-------------+-------------------+------+--------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+-------+------+-------------------------------------------------+

|    1 | SIMPLE      | Parameters        | ref  | fk_param__subtype_id                                                                                                           | fk_param__subtype_id | 1       | const |   43 | Using temporary; Using filesort                 |

|    1 | SIMPLE      | ParametersIndices | ALL  | idx_product_id_key,idx_param_id,idx_param_id_product_id,idx_product_id_key_contact_id_value_alt,param_id_2,param_id_3,param_id | NULL                 | NULL    | NULL  |    2 | Using where; Using join buffer (flat, BNL join) |

+------+-------------+-------------------+------+--------------------------------------------------------------------------------------------------------------------------------+----------------------+---------+-------+------+-------------------------------------------------+

2 rows in set (0.00 sec)

EXPLAIN SELECT * FROM `sc_param` AS `Parameters` LEFT JOIN `sc_param_index` AS `ParametersIndices` USE INDEX (idx_product_id_key) ON `ParametersIndices`.`gen_subtype_id` = 4 AND `ParametersIndices`.`product_id_key` = 192261 AND (`ParametersIndices`.`state` >= 0) AND `Parameters`.`id` = `ParametersIndices`.`param_id` WHERE `Parameters`.`subtype_id` = 4 ORDER BY `Parameters`.`pos` ASC, `ParametersIndices`.`alt_ranking` ASC, IF(ParametersIndices.ranking IS NOT NULL, 0, 1) ASC, `ParametersIndices`.`ranking` ASC, `ParametersIndices`.`id` ASC;

+------+-------------+-------------------+------+----------------------+----------------------+---------+-------+------+-------------------------------------------------+

| id   | select_type | table             | type | possible_keys        | key                  | key_len | ref   | rows | Extra                                           |

+------+-------------+-------------------+------+----------------------+----------------------+---------+-------+------+-------------------------------------------------+

|    1 | SIMPLE      | Parameters        | ref  | fk_param__subtype_id | fk_param__subtype_id | 1       | const |   43 | Using temporary; Using filesort                 |

|    1 | SIMPLE      | ParametersIndices | ALL  | idx_product_id_key   | NULL                 | NULL    | NULL  |    2 | Using where; Using join buffer (flat, BNL join) |

+------+-------------+-------------------+------+----------------------+----------------------+---------+-------+------+-------------------------------------------------+

2 rows in set (0.00 sec)

EXPLAIN SELECT * FROM `sc_param` AS `Parameters` LEFT JOIN `sc_param_index` AS `ParametersIndices` FORCE INDEX (idx_product_id_key) ON `ParametersIndices`.`gen_subtype_id` = 4 AND `ParametersIndices`.`product_id_key` = 192261 AND (`ParametersIndices`.`state` >= 0) AND `Parameters`.`id` = `ParametersIndices`.`param_id` WHERE `Parameters`.`subtype_id` = 4 ORDER BY `Parameters`.`pos` ASC, `ParametersIndices`.`alt_ranking` ASC, IF(ParametersIndices.ranking IS NOT NULL, 0, 1) ASC, `ParametersIndices`.`ranking` ASC, `ParametersIndices`.`id` ASC;

+------+-------------+-------------------+------+----------------------+----------------------+---------+--------------------------+------+---------------------------------+

| id   | select_type | table             | type | possible_keys        | key                  | key_len | ref                      | rows | Extra                           |

+------+-------------+-------------------+------+----------------------+----------------------+---------+--------------------------+------+---------------------------------+

|    1 | SIMPLE      | Parameters        | ref  | fk_param__subtype_id | fk_param__subtype_id | 1       | const                    |   43 | Using temporary; Using filesort |

|    1 | SIMPLE      | ParametersIndices | ref  | idx_product_id_key   | idx_product_id_key   | 9       | const,sc_2.Parameters.id |    1 | Using where                     |

+------+-------------+-------------------+------+----------------------+----------------------+---------+--------------------------+------+---------------------------------+

2 rows in set (0.00 sec)

I'm still trying to reproduce the issue on a smaller part of the table, but on the slave, there's no issue, with almost the same SHOW INDEX values

Thanks and regards,
Jocelyn

Attachments

Activity

People

Assignee:: Sergei Petrunia

Reporter:: jocelyn fournier

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2016-07-28 15:22

Updated:: 2016-07-29 21:49

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.