[MDEV-10460] Join Buffer (flat, BNL) prefered over ref access when few rows are matching in the JOIN Created: 2016-07-28  Updated: 2016-07-29

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0.26, 10.1.16
Fix Version/s: 10.1

Type: Bug Priority: Major
Reporter: jocelyn fournier Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
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

 Comments   
Comment by jocelyn fournier [ 2016-07-28 ]

Could be related to upstream bug https://tokutek.atlassian.net/browse/DB-1006

Comment by jocelyn fournier [ 2016-07-28 ]

According to my finding, it happens in my case when there is (or the optimizer think there is) <= 6 matching rows in the joined table. I'm working on a testcase right now.

Comment by jocelyn fournier [ 2016-07-28 ]

How to repeat :

DROP TABLE IF EXISTS sp;
 
CREATE TABLE sp (id SERIAL, subtype_id INT UNSIGNED NOT NULL DEFAULT '0', KEY (subtype_id)) ENGINE=TokuDB;
 
INSERT INTO sp (subtype_id) VALUES (4),(4),(4),(4),(5),(6),(1);
 
DROP TABLE IF EXISTS spi;
 
CREATE TABLE spi (id SERIAL, gen_subtype_id INT UNSIGNED NOT NULL DEFAULT '0', product_id_key INT UNSIGNED NOT NULL DEFAULT '0', state TINYINT UNSIGNED NOT NULL DEFAULT '0', param_id INT UNSIGNED NOT NULL DEFAULT '0', KEY (gen_subtype_id, product_id_key)) ENGINE=TokuDB;
 
INSERT INTO spi (gen_subtype_id, product_id_key, state, param_id) VALUES (4,192261,1,96),(4,192261,1,102),(4,192261,1,98),(4,192261,1,101),(4,192261,1,101),(4,192261,1,101);
 
EXPLAIN SELECT subtype_id FROM `sp` AS `Parameters` LEFT JOIN `spi` 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;
 
 
 
+------+-------------+-------------------+------+----------------+------------+---------+-------+------+-------------------------------------------------+
 
| id   | select_type | table             | type | possible_keys  | key        | key_len | ref   | rows | Extra                                           |
 
+------+-------------+-------------------+------+----------------+------------+---------+-------+------+-------------------------------------------------+
 
|    1 | SIMPLE      | Parameters        | ref  | subtype_id     | subtype_id | 4       | const |    4 | Using index                                     |
 
|    1 | SIMPLE      | ParametersIndices | ALL  | gen_subtype_id | NULL       | NULL    | NULL  |    6 | Using where; Using join buffer (flat, BNL join) |
 
+------+-------------+-------------------+------+----------------+------------+---------+-------+------+-------------------------------------------------+
 
 
 
INSERT INTO spi (gen_subtype_id, product_id_key, state, param_id) VALUES (4,192261,1,96),(4,192261,1,96),(4,192261,1,96);
 
 
 
EXPLAIN SELECT subtype_id FROM `sp` AS `Parameters` LEFT JOIN `spi` 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;
 
+------+-------------+-------------------+------+----------------+----------------+---------+-------------+------+-------------+
 
| id   | select_type | table             | type | possible_keys  | key            | key_len | ref         | rows | Extra       |
 
+------+-------------+-------------------+------+----------------+----------------+---------+-------------+------+-------------+
 
|    1 | SIMPLE      | Parameters        | ref  | subtype_id     | subtype_id     | 4       | const       |    4 | Using index |
 
|    1 | SIMPLE      | ParametersIndices | ref  | gen_subtype_id | gen_subtype_id | 8       | const,const |    9 | Using where |
 
+------+-------------+-------------------+------+----------------+----------------+---------+-------------+------+-------------+

The problem occurs as well with InnoDB, and is even "worse" because it requires a lot more rows in the JOIN table to get ride of the ALL / join buffer.
This kind of optimizer choice could cause some big performance regression if the table statistics are not accurate, which is often the case, especially with partitioned tables.

It also occurs with MariaDB 10.0.26

Setting SET join_cache_level=0; also avoid the use of the join buffer.

Comment by jocelyn fournier [ 2016-07-28 ]

For InnoDB, I have to put more than 7000 rows in the spi table to get ride of the join buffer.

Comment by jocelyn fournier [ 2016-07-28 ]

More index selectivity & InnoDB :

DROP TABLE IF EXISTS sp;
 
CREATE TABLE sp (id SERIAL, subtype_id INT UNSIGNED NOT NULL DEFAULT '0', KEY (subtype_id)) ENGINE=InnoDB;
 
INSERT INTO sp (subtype_id) VALUES (4),(4),(4),(4),(5),(6),(1),(96);
 
INSERT INTO sp (id, subtype_id) VALUES (96,4);
 
DROP TABLE IF EXISTS spi;
 
CREATE TABLE spi (id SERIAL, gen_subtype_id INT UNSIGNED NOT NULL DEFAULT '0', product_id_key INT UNSIGNED NOT NULL DEFAULT '0', state TINYINT UNSIGNED NOT NULL DEFAULT '0', param_id INT UNSIGNED NOT NULL DEFAULT '0', KEY (gen_subtype_id, product_id_key)) ENGINE=InnoDB;
 
 
 
INSERT INTO spi (gen_subtype_id, product_id_key, state, param_id) VALUES (3,192261,1,96),(3,192261,1,96),(2,192261,1,102),(1,192261,1,98),(2,192261,1,101),(4,192261,1,101),(4,192261,1,101),(3,192261,1,96),(2,192261,1,102),(1,192261,1,98),(2,192261,1,101),(4,192261,1,101),(4,192261,1,101);
 
 
 
EXPLAIN SELECT subtype_id FROM `sp` AS `Parameters` LEFT JOIN `spi` 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;
 
 
 
+------+-------------+-------------------+------+----------------+------------+---------+-------+------+-------------------------------------------------+
 
| id   | select_type | table             | type | possible_keys  | key        | key_len | ref   | rows | Extra                                           |
 
+------+-------------+-------------------+------+----------------+------------+---------+-------+------+-------------------------------------------------+
 
|    1 | SIMPLE      | Parameters        | ref  | subtype_id     | subtype_id | 4       | const |    5 | Using index                                     |
 
|    1 | SIMPLE      | ParametersIndices | ALL  | gen_subtype_id | NULL       | NULL    | NULL  |   13 | Using where; Using join buffer (flat, BNL join) |
 
+------+-------------+-------------------+------+----------------+------------+---------+-------+------+-------------------------------------------------+
 
 
 
INSERT INTO spi (gen_subtype_id, product_id_key, state, param_id) VALUES (3,192261,1,96);
 
 
 
EXPLAIN SELECT subtype_id FROM `sp` AS `Parameters` LEFT JOIN `spi` 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;
 
 
 
+------+-------------+-------------------+------+----------------+----------------+---------+-------------+------+-------------+
 
| id   | select_type | table             | type | possible_keys  | key            | key_len | ref         | rows | Extra       |
 
+------+-------------+-------------------+------+----------------+----------------+---------+-------------+------+-------------+
 
|    1 | SIMPLE      | Parameters        | ref  | subtype_id     | subtype_id     | 4       | const       |    5 | Using index |
 
|    1 | SIMPLE      | ParametersIndices | ref  | gen_subtype_id | gen_subtype_id | 8       | const,const |    4 | Using where |
 
+------+-------------+-------------------+------+----------------+----------------+---------+-------------+------+-------------+

Comment by Elena Stepanova [ 2016-07-29 ]

It's reproducible as described, but I'll leave it to psergey to decide whether there is an actual problem here, and if there is, in which version it needs to be fixed. I've set 'Fix version/s' to 10.1 just to keep the issue on the radar until it's analyzed.

Generated at Thu Feb 08 07:42:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.