SELECTcount(*)FROMthe_loop.loopWHERE(partner_id='VMF'ANDtype_idIN('PostType-275e2809-5658-48ba-b3cf-0884f5851347', 'PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba', 'PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301', 'PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9', 'PostType-1cdac073-9cfd-4388-98ca-86b140921adb', 'PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')ANDcreatedBETWEEN'2021-02-09 00:00:00'ANDcreated<'2021-08-08 23:59:59.999'ANDdeletedISNULL)LIMIT0, 1000, { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "IN_uses_bisection": true }, { "expanded_query": "/* select#1 */ select count(0) AS `count(*)` from `loop` where ((`loop`.`partner_id` = 'VMF') and (`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`loop`.`created` between '2021-02-09 00:00:00' and `loop`.`created`) < '2021-08-08 23:59:59.999') and (`loop`.`deleted` is null)) limit 0,1000" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`loop`.`partner_id` = 'VMF') and (`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`loop`.`created` between '2021-02-09 00:00:00' and `loop`.`created`) < '2021-08-08 23:59:59.999') and (`loop`.`deleted` is null))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`loop`.`created` between '2021-02-09 00:00:00' and `loop`.`created`) < '2021-08-08 23:59:59.999') and (`loop`.`deleted` is null) and multiple equal('VMF', `loop`.`partner_id`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`loop`.`created` between '2021-02-09 00:00:00' and `loop`.`created`) < '2021-08-08 23:59:59.999') and (`loop`.`deleted` is null) and multiple equal('VMF', `loop`.`partner_id`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`loop`.`created` between '2021-02-09 00:00:00' and `loop`.`created`) < '2021-08-08 23:59:59.999') and (`loop`.`deleted` is null) and multiple equal('VMF', `loop`.`partner_id`))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`loop`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`loop`", "field": "partner_id", "equals": "'VMF'", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "`loop`", "range_analysis": { "table_scan": { "rows": 25495539, "cost": 8.92e6 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "type_id", "post_id" ] }, { "index": "idx_loop_prod_timestamps", "usable": true, "key_parts": [ "partner_id", "type_id", "created", "deleted", "updated", "post_id" ] }, { "index": "idx_loop_prod", "usable": true, "key_parts": [ "created", "partner_id", "type_id", "market_id", "user_id", "deleted", "post_id" ] }, { "index": "idx_loop_prod_type_id_partner_id_created_deleted", "usable": true, "key_parts": [ "type_id", "partner_id", "created", "deleted", "post_id" ] } ], "best_covering_index_scan": { "index": "idx_loop_prod_type_id_partner_id_created_deleted", "cost": 8.91e6, "chosen": true }, "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "skip_scan_range": { "potential_skip_scan_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "query_references_nonkey_column" }, { "index": "idx_loop_prod_timestamps", "tree_travel_cost": 1.25, "num_groups": 7046448, "rows": 17933224, "cost": 2.47e7 }, { "index": "idx_loop_prod", "tree_travel_cost": 1.25, "num_groups": 6972159, "rows": 25495540, "cost": 2.98e7 }, { "index": "idx_loop_prod_type_id_partner_id_created_deleted", "tree_travel_cost": 1.25, "num_groups": 7040252, "rows": 17309876, "cost": 2.45e7 } ] }, "best_skip_scan_summary": { "type": "skip_scan", "index": "idx_loop_prod_type_id_partner_id_created_deleted", "key_parts_used_for_access": [ "type_id", "partner_id", "created", "deleted" ], "prefix ranges": [ "PostType-1cdac073-9cfd-4388-98ca-86b140921adb <= type_id <= PostType-1cdac073-9cfd-4388-98ca-86b140921adb AND VMF <= partner_id <= VMF", "PostType-275e2809-5658-48ba-b3cf-0884f5851347 <= type_id <= PostType-275e2809-5658-48ba-b3cf-0884f5851347 AND VMF <= partner_id <= VMF", "PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9 <= type_id <= PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9 AND VMF <= partner_id <= VMF", "PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9 <= type_id <= PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9 AND VMF <= partner_id <= VMF", "PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba <= type_id <= PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba AND VMF <= partner_id <= VMF", "PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301 <= type_id <= PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301 AND VMF <= partner_id <= VMF" ], "range": [ "NULL <= deleted <= NULL" ], "chosen": false, "cause": "cost" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "PostType-1cdac073-9cfd-4388-98ca-86b140921adb <= type_id <= PostType-1cdac073-9cfd-4388-98ca-86b140921adb", "PostType-275e2809-5658-48ba-b3cf-0884f5851347 <= type_id <= PostType-275e2809-5658-48ba-b3cf-0884f5851347", "PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9 <= type_id <= PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9", "PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9 <= type_id <= PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9", "PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba <= type_id <= PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba", "PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301 <= type_id <= PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 23325020, "cost": 2.35e6, "chosen": true }, { "index": "idx_loop_prod_timestamps", "ranges": [ "VMF <= partner_id <= VMF AND PostType-1cdac073-9cfd-4388-98ca-86b140921adb <= type_id <= PostType-1cdac073-9cfd-4388-98ca-86b140921adb", "VMF <= partner_id <= VMF AND PostType-275e2809-5658-48ba-b3cf-0884f5851347 <= type_id <= PostType-275e2809-5658-48ba-b3cf-0884f5851347", "VMF <= partner_id <= VMF AND PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9 <= type_id <= PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9", "VMF <= partner_id <= VMF AND PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9 <= type_id <= PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9", "VMF <= partner_id <= VMF AND PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba <= type_id <= PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba", "VMF <= partner_id <= VMF AND PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301 <= type_id <= PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 17933225, "cost": 6.22e6, "chosen": false, "cause": "cost" }, { "index": "idx_loop_prod", "chosen": false, "cause": "no_valid_range_for_this_index" }, { "index": "idx_loop_prod_type_id_partner_id_created_deleted", "ranges": [ "PostType-1cdac073-9cfd-4388-98ca-86b140921adb <= type_id <= PostType-1cdac073-9cfd-4388-98ca-86b140921adb AND VMF <= partner_id <= VMF", "PostType-275e2809-5658-48ba-b3cf-0884f5851347 <= type_id <= PostType-275e2809-5658-48ba-b3cf-0884f5851347 AND VMF <= partner_id <= VMF", "PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9 <= type_id <= PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9 AND VMF <= partner_id <= VMF", "PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9 <= type_id <= PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9 AND VMF <= partner_id <= VMF", "PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba <= type_id <= PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba AND VMF <= partner_id <= VMF", "PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301 <= type_id <= PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301 AND VMF <= partner_id <= VMF" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "rows": 17309875, "cost": 6.05e6, "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": 23325020, "ranges": [ "PostType-1cdac073-9cfd-4388-98ca-86b140921adb <= type_id <= PostType-1cdac073-9cfd-4388-98ca-86b140921adb", "PostType-275e2809-5658-48ba-b3cf-0884f5851347 <= type_id <= PostType-275e2809-5658-48ba-b3cf-0884f5851347", "PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9 <= type_id <= PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9", "PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9 <= type_id <= PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9", "PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba <= type_id <= PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba", "PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301 <= type_id <= PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301" ] }, "rows_for_plan": 23325020, "cost_for_plan": 2.35e6, "chosen": true } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`loop`", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "idx_loop_prod_timestamps", "chosen": false, "cause": "range_uses_more_keyparts" }, { "rows_to_scan": 23325020, "access_type": "range", "range_details": { "used_index": "PRIMARY" }, "resulting_rows": 2.33e7, "cost": 4.68e6, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 2.33e7, "cost_for_plan": 4.68e6, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`loop`.`partner_id` = 'VMF') and (`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`loop`.`created` between '2021-02-09 00:00:00' and `loop`.`created`) < '2021-08-08 23:59:59.999') and (`loop`.`deleted` is null))", "attached_conditions_computation": [ { "table": "`loop`", "rechecking_index_usage": { "recheck_reason": "low_limit", "limit": 1000, "row_estimate": 2.33e7 } } ], "attached_conditions_summary": [ { "table": "`loop`", "attached": "((`loop`.`partner_id` = 'VMF') and (`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`loop`.`created` between '2021-02-09 00:00:00' and `loop`.`created`) < '2021-08-08 23:59:59.999') and (`loop`.`deleted` is null))" } ] } }, { "optimizing_distinct_group_by_order_by": { } }, { "finalizing_table_conditions": [ { "table": "`loop`", "original_table_condition": "((`loop`.`partner_id` = 'VMF') and (`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`loop`.`created` between '2021-02-09 00:00:00' and `loop`.`created`) < '2021-08-08 23:59:59.999') and (`loop`.`deleted` is null))", "final_table_condition ": "((`loop`.`partner_id` = 'VMF') and (`loop`.`type_id` in ('PostType-275e2809-5658-48ba-b3cf-0884f5851347','PostType-cb20489a-80ab-411b-93cc-6a9dc3341eba','PostType-e53411b6-ab01-4825-ac47-6d6e18d7a301','PostType-3b79c6ee-1d4b-471a-a23f-4e851016c5a9','PostType-1cdac073-9cfd-4388-98ca-86b140921adb','PostType-4ca8e767-5a57-4cf2-8635-8f3f78b7f9e9')) and ((`loop`.`created` between '2021-02-09 00:00:00' and `loop`.`created`) < '2021-08-08 23:59:59.999') and (`loop`.`deleted` is null))" } ] }, { "refine_plan": [ { "table": "`loop`" } ] }, { "considering_tmp_tables": [ ] } ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] }, 0, 0