SET SESSION OPTIMIZER_TRACE="enabled=on"; explain format=json select portal_id, id from file_metadata where ( (portal_id > 2459647) or ( ((portal_id = 2459647)) AND (id > 1459) ) ) order by portal_id asc, id asc limit 1 offset 99\G [...] mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G *************************** 1. row *************************** QUERY: explain format=json select portal_id, id from file_metadata where ( (portal_id > 2459647) or ( ((portal_id = 2459647)) AND (id > 1459) ) ) order by portal_id asc, id asc limit 1 offset 99 TRACE: { "steps": [ { "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `file_metadata`.`portal_id` AS `portal_id`,`file_metadata`.`id` AS `id` from `file_metadata` where ((`file_metadata`.`portal_id` > 2459647) or ((`file_metadata`.`portal_id` = 2459647) and (`file_metadata`.`id` > 1459))) order by `file_metadata`.`portal_id`,`file_metadata`.`id` limit 99,1" } ] } }, { "join_optimization": { "select#": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "((`file_metadata`.`portal_id` > 2459647) or ((`file_metadata`.`portal_id` = 2459647) and (`file_metadata`.`id` > 1459)))", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "((`file_metadata`.`portal_id` > 2459647) or ((`file_metadata`.`id` > 1459) and multiple equal(2459647, `file_metadata`.`portal_id`)))" }, { "transformation": "constant_propagation", "resulting_condition": "((`file_metadata`.`portal_id` > 2459647) or ((`file_metadata`.`id` > 1459) and multiple equal(2459647, `file_metadata`.`portal_id`)))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`file_metadata`.`portal_id` > 2459647) or ((`file_metadata`.`id` > 1459) and multiple equal(2459647, `file_metadata`.`portal_id`)))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`file_metadata`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ ] }, { "rows_estimation": [ { "table": "`file_metadata`", "range_analysis": { "table_scan": { "rows": 472568938, "cost": 1.25587e+08 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "portal_id", "id" ] }, { "index": "file_updated", "usable": true, "key_parts": [ "portal_id", "updated", "id" ] }, { "index": "folder_name_deleted", "usable": true, "key_parts": [ "folder_id", "name", "deleted_at", "portal_id", "id" ] }, { "index": "file_deleted", "usable": true, "key_parts": [ "portal_id", "deleted_at", "id" ] }, { "index": "cloud_key", "usable": true, "key_parts": [ "cloud_key", "deleted_at", "portal_id", "id" ] }, { "index": "name_extension_deleted", "usable": true, "key_parts": [ "name", "extension", "deleted_at", "portal_id", "id" ] }, { "index": "file_md5", "usable": true, "key_parts": [ "file_md5", "portal_id", "id" ] }, { "index": "file_name", "usable": true, "key_parts": [ "portal_id", "name", "deleted_at", "id" ] }, { "index": "type_name_deleted_updated", "usable": true, "key_parts": [ "portal_id", "type", "name", "deleted_at", "updated", "id" ] }, { "index": "archive_hidden", "usable": true, "key_parts": [ "portal_id", "archived", "hidden", "id" ] }, { "index": "frequent_search", "usable": true, "key_parts": [ "portal_id", "name", "type", "deleted_at", "hidden", "archived", "id" ] }, { "index": "frequent_search2", "usable": true, "key_parts": [ "portal_id", "folder_id", "name", "extension", "deleted_at", "updated", "id" ] }, { "index": "files_by_type_and_visibility", "usable": true, "key_parts": [ "portal_id", "type", "hidden", "archived", "deleted_at", "updated", "id" ] } ], "best_covering_index_scan": { "index": "archive_hidden", "cost": 4.82932e+07, "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": "prefix_not_const_equality" }, { "index": "file_updated", "usable": false, "cause": "prefix_not_const_equality" }, { "index": "folder_name_deleted", "usable": false, "cause": "range_predicate_too_complex" }, { "index": "file_deleted", "usable": false, "cause": "prefix_not_const_equality" }, { "index": "cloud_key", "usable": false, "cause": "range_predicate_too_complex" }, { "index": "name_extension_deleted", "usable": false, "cause": "range_predicate_too_complex" }, { "index": "file_md5", "usable": false, "cause": "range_predicate_too_complex" }, { "index": "file_name", "usable": false, "cause": "prefix_not_const_equality" }, { "index": "type_name_deleted_updated", "usable": false, "cause": "prefix_not_const_equality" }, { "index": "archive_hidden", "usable": false, "cause": "prefix_not_const_equality" }, { "index": "frequent_search", "usable": false, "cause": "prefix_not_const_equality" }, { "index": "frequent_search2", "usable": false, "cause": "prefix_not_const_equality" }, { "index": "files_by_type_and_visibility", "usable": false, "cause": "prefix_not_const_equality" } ] }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "portal_id = 2459647 AND 1459 < id", "2459647 < portal_id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": true, "in_memory": 0.00161962, "rows": 472568938, "cost": 4.93187e+07, "chosen": false, "cause": "cost" }, { "index": "file_updated", "ranges": [ "portal_id = 2459647", "2459647 < portal_id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "in_memory": 1.80646e-05, "rows": 472568938, "cost": 4.86387e+07, "chosen": false, "cause": "cost" }, { "index": "folder_name_deleted", "chosen": false, "cause": "no_valid_range_for_this_index" }, { "index": "file_deleted", "ranges": [ "portal_id = 2459647", "2459647 < portal_id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "in_memory": 1.84164e-05, "rows": 472568938, "cost": 4.86387e+07, "chosen": false, "cause": "cost" }, { "index": "cloud_key", "chosen": false, "cause": "no_valid_range_for_this_index" }, { "index": "name_extension_deleted", "chosen": false, "cause": "no_valid_range_for_this_index" }, { "index": "file_md5", "chosen": false, "cause": "no_valid_range_for_this_index" }, { "index": "file_name", "ranges": [ "portal_id = 2459647", "2459647 < portal_id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "in_memory": 8.07895e-06, "rows": 472568938, "cost": 1.06328e+08, "chosen": false, "cause": "cost" }, { "index": "type_name_deleted_updated", "ranges": [ "portal_id = 2459647", "2459647 < portal_id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "in_memory": 6.95252e-06, "rows": 472568938, "cost": 1.06328e+08, "chosen": false, "cause": "cost" }, { "index": "archive_hidden", "ranges": [ "portal_id = 2459647", "2459647 < portal_id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "in_memory": 2.45058e-05, "rows": 472568938, "cost": 4.82932e+07, "chosen": false, "cause": "cost" }, { "index": "frequent_search", "ranges": [ "portal_id = 2459647", "2459647 < portal_id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "in_memory": 7.59459e-06, "rows": 472568938, "cost": 1.06328e+08, "chosen": false, "cause": "cost" }, { "index": "frequent_search2", "ranges": [ "portal_id = 2459647", "2459647 < portal_id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "in_memory": 6.44849e-06, "rows": 472568938, "cost": 1.06328e+08, "chosen": false, "cause": "cost" }, { "index": "files_by_type_and_visibility", "ranges": [ "portal_id = 2459647", "2459647 < portal_id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": true, "in_memory": 1.15109e-05, "rows": 472568938, "cost": 4.98969e+07, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`file_metadata`", "best_access_path": { "considered_access_paths": [ { "rows_to_scan": 472568938, "filtering_effect": [ ], "final_filtering_effect": 1, "access_type": "scan", "resulting_rows": 4.72569e+08, "cost": 1.25587e+08, "chosen": true } ] }, "condition_filtering_pct": 100, "rows_for_plan": 4.72569e+08, "cost_for_plan": 1.25587e+08, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`file_metadata`.`portal_id` > 2459647) or ((`file_metadata`.`portal_id` = 2459647) and (`file_metadata`.`id` > 1459)))", "attached_conditions_computation": [ { "table": "`file_metadata`", "rechecking_index_usage": { "recheck_reason": "low_limit", "limit": 100, "row_estimate": 4.72569e+08, "range_analysis": { "table_scan": { "rows": 472568938, "cost": 5.19252e+08 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "portal_id", "id" ] }, { "index": "file_updated", "usable": false, "cause": "not_applicable" }, { "index": "folder_name_deleted", "usable": false, "cause": "not_applicable" }, { "index": "file_deleted", "usable": false, "cause": "not_applicable" }, { "index": "cloud_key", "usable": false, "cause": "not_applicable" }, { "index": "name_extension_deleted", "usable": false, "cause": "not_applicable" }, { "index": "file_md5", "usable": false, "cause": "not_applicable" }, { "index": "file_name", "usable": false, "cause": "not_applicable" }, { "index": "type_name_deleted_updated", "usable": false, "cause": "not_applicable" }, { "index": "archive_hidden", "usable": false, "cause": "not_applicable" }, { "index": "frequent_search", "usable": false, "cause": "not_applicable" }, { "index": "frequent_search2", "usable": false, "cause": "not_applicable" }, { "index": "files_by_type_and_visibility", "usable": false, "cause": "not_applicable" } ], "best_covering_index_scan": { "index": "archive_hidden", "cost": 4.82932e+07, "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": "prefix_not_const_equality" } ] }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "portal_id = 2459647 AND 1459 < id", "2459647 < portal_id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": true, "in_memory": 0.00161962, "rows": 472568938, "cost": 4.93187e+07, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } } } } } ], "attached_conditions_summary": [ { "table": "`file_metadata`", "attached": "((`file_metadata`.`portal_id` > 2459647) or ((`file_metadata`.`portal_id` = 2459647) and (`file_metadata`.`id` > 1459)))" } ] } }, { "optimizing_distinct_group_by_order_by": { "simplifying_order_by": { "original_clause": "`file_metadata`.`portal_id`,`file_metadata`.`id`", "items": [ { "item": "`file_metadata`.`portal_id`" }, { "item": "`file_metadata`.`id`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`file_metadata`.`portal_id`,`file_metadata`.`id`" } } }, { "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "steps": [ { "rows_estimation": { "table": "`file_metadata`", "index": "PRIMARY", "range_analysis": { "table_scan": { "rows": 472568938, "cost": 5.19252e+08 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": [ "portal_id", "id" ] }, { "index": "file_updated", "usable": false, "cause": "not_applicable" }, { "index": "folder_name_deleted", "usable": false, "cause": "not_applicable" }, { "index": "file_deleted", "usable": false, "cause": "not_applicable" }, { "index": "cloud_key", "usable": false, "cause": "not_applicable" }, { "index": "name_extension_deleted", "usable": false, "cause": "not_applicable" }, { "index": "file_md5", "usable": false, "cause": "not_applicable" }, { "index": "file_name", "usable": false, "cause": "not_applicable" }, { "index": "type_name_deleted_updated", "usable": false, "cause": "not_applicable" }, { "index": "archive_hidden", "usable": false, "cause": "not_applicable" }, { "index": "frequent_search", "usable": false, "cause": "not_applicable" }, { "index": "frequent_search2", "usable": false, "cause": "not_applicable" }, { "index": "files_by_type_and_visibility", "usable": false, "cause": "not_applicable" } ], "best_covering_index_scan": { "index": "archive_hidden", "cost": 4.82932e+07, "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": "prefix_not_const_equality" } ] }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "PRIMARY", "ranges": [ "portal_id = 2459647 AND 1459 < id", "2459647 < portal_id" ], "index_dives_for_eq_ranges": true, "rowid_ordered": true, "using_mrr": false, "index_only": true, "in_memory": 0.00161962, "rows": 472568938, "cost": 4.93187e+07, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } } } } } ], "index_order_summary": { "table": "`file_metadata`", "index_provides_order": true, "order_direction": "asc", "index": "PRIMARY", "plan_changed": true, "access_type": "index" } } }, { "finalizing_table_conditions": [ { "table": "`file_metadata`", "original_table_condition": "((`file_metadata`.`portal_id` > 2459647) or ((`file_metadata`.`portal_id` = 2459647) and (`file_metadata`.`id` > 1459)))", "final_table_condition ": "((`file_metadata`.`portal_id` > 2459647) or ((`file_metadata`.`portal_id` = 2459647) and (`file_metadata`.`id` > 1459)))" } ] }, { "refine_plan": [ { "table": "`file_metadata`" } ] }, { "considering_tmp_tables": [ ] } ] } }, { "join_explain": { "select#": 1, "steps": [ ] } } ] } MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 INSUFFICIENT_PRIVILEGES: 0 1 row in set (0.01 sec)