Sergei Golubchik added a comment - 2024-11-22 14:07 Reformatted for readability: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE amazon_common_ads_campaign_report ALL idx_unique_campaign_report,search_campaing - - - 50182640 Using where and Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored amazon_common_ads_campaign_report 0 PRIMARY 1 id A 50182640       BTREE     NO amazon_common_ads_campaign_report 0 idx_unique_campaign_report 1 profile_id A 77442     YES BTREE     NO amazon_common_ads_campaign_report 0 idx_unique_campaign_report 2 report_date A 194506     YES BTREE     NO amazon_common_ads_campaign_report 0 idx_unique_campaign_report 3 campaign_id A 25091320     YES BTREE     NO amazon_common_ads_campaign_report 0 idx_unique_campaign_report 4 placement A 50182640     YES BTREE     NO amazon_common_ads_campaign_report 1 search_campaing 1 profile_id A 82537     YES BTREE     NO amazon_common_ads_campaign_report 1 search_campaing 2 campaign_id A 336796     YES BTREE     NO amazon_common_ads_campaign_report 1 search_campaing 3 report_date A 25091320     YES BTREE     NO

Sergei Golubchik added a comment - 2024-11-22 14:10 "skips all indexes due to high cardinality of second column in the query" doesn't sound plausible, in that case removing the second condition would make the optimizer to use the index. Does it? Try EXPLAIN FORMAT=JSON or, better, optimizer trace . What does it show?

ganesh krishnan added a comment - 2024-11-22 15:33 - edited With EXPLAIN FORMAT=JSON { "query_block" : { "select_id" : 1, "cost" : 8751.62308, "nested_loop" : [ { "table" : { "table_name" : "amazon_common_ads_campaign_report" , "access_type" : "ALL" , "possible_keys" : [ "idx_unique_campaign_report" , "search_campaing" ], "loops" : 1, "rows" : 50254393, "cost" : 8751.62308, "filtered" : 15.702981, "attached_condition" : "amazon_common_ads_campaign_report.profile_id = 4308644068810310 and amazon_common_ads_campaign_report.report_date >= '2024-05-09'" } } ] } } With just the first column: It still doesn't run it via the index. We can see that the cardinality of profile_id is low (77k in 50 million records). Mariadb should always use the index. In this case restricting by profile id is less than 15% of the table but Mariadb does a full table scan anyway. Worth noting that if I change the profile_id to some other value, the index picks it up correct. (I already optimized the table couple of times and recreated the indexes) My recommendation is that Mariadb should always use the index for large tables like this as there is no scenario where a full table scan will be faster than using an index.

Sergei Golubchik added a comment - 2024-11-22 17:08 try optimizer trace. it should tell why the index was not used. may be costs are incorrect and optimizer thinks that at 15% it'll be faster to do a full scan. Is it an InnoDB table?

ganesh krishnan added a comment - 2024-11-22 18:17 My db team ran the optimizer trace and the output is below. I am not proficient in using this so I will have to do some read up. However you can look at the dump provided below and add your thoughts. One other possible anomaly I can see is that the composite index "search_campaing" (which can also be used) is showing "id" as a "key part" though this column is not part of the composite index.     *************************** 1. row *************************** QUERY: EXPLAIN SELECT * FROM amazon_common_ads_campaign_report WHERE profile_id = 4308644068810310 AND report_date >= '2024-05-09' TRACE: { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select amazon_common_ads_campaign_report.`id` AS `id`,amazon_common_ads_campaign_report.bid_plus AS bid_plus,amazon_common_ads_campaign_report.campaign_budget AS campaign_budget,amazon_common_ads_campaign_report.campaign_id AS campaign_id,amazon_common_ads_campaign_report.clicks AS clicks,amazon_common_ads_campaign_report.conversion_same_sku AS conversion_same_sku,amazon_common_ads_campaign_report.conversions AS conversions,amazon_common_ads_campaign_report.cost AS cost,amazon_common_ads_campaign_report.currency AS currency,amazon_common_ads_campaign_report.dpv_units AS dpv_units,amazon_common_ads_campaign_report.impressions AS impressions,amazon_common_ads_campaign_report.orders_new_to_brand AS orders_new_to_brand,amazon_common_ads_campaign_report.orders_new_to_brand_percentage AS orders_new_to_brand_percentage,amazon_common_ads_campaign_report.page_views AS page_views,amazon_common_ads_campaign_report.placement AS placement,amazon_common_ads_campaign_report.profile_id AS profile_id,amazon_common_ads_campaign_report.region AS region,amazon_common_ads_campaign_report.report_date AS report_date,amazon_common_ads_campaign_report.sales AS sales,amazon_common_ads_campaign_report.sales_new_to_brand AS sales_new_to_brand,amazon_common_ads_campaign_report.sales_new_to_brand_percentage AS sales_new_to_brand_percentage,amazon_common_ads_campaign_report.sales_same_sku AS sales_same_sku,amazon_common_ads_campaign_report.tactic AS tactic,amazon_common_ads_campaign_report.units AS units,amazon_common_ads_campaign_report.units_new_to_brand AS units_new_to_brand,amazon_common_ads_campaign_report.units_new_to_brand_percentage AS units_new_to_brand_percentage,amazon_common_ads_campaign_report.units_same_sku AS units_same_sku,amazon_common_ads_campaign_report.organic_sales AS organic_sales,amazon_common_ads_campaign_report.organic_units AS organic_units,amazon_common_ads_campaign_report.purchases14d AS purchases14d,amazon_common_ads_campaign_report.purchases1d AS purchases1d,amazon_common_ads_campaign_report.purchases30d AS purchases30d,amazon_common_ads_campaign_report.purchases7d AS purchases7d,amazon_common_ads_campaign_report.sales14d AS sales14d,amazon_common_ads_campaign_report.sales1d AS sales1d,amazon_common_ads_campaign_report.sales30d AS sales30d,amazon_common_ads_campaign_report.sales7d AS sales7d,amazon_common_ads_campaign_report.clicks14d AS clicks14d,amazon_common_ads_campaign_report.clicks1d AS clicks1d,amazon_common_ads_campaign_report.clicks30d AS clicks30d,amazon_common_ads_campaign_report.clicks7d AS clicks7d,amazon_common_ads_campaign_report.cost_type AS cost_type,amazon_common_ads_campaign_report.cumulative_reach AS cumulative_reach,amazon_common_ads_campaign_report.impressions_frequency_average AS impressions_frequency_average,amazon_common_ads_campaign_report.kindle_edition_normalized_pages_read14d AS kindle_edition_normalized_pages_read14d,amazon_common_ads_campaign_report.kindle_edition_normalized_pages_royalties14d AS kindle_edition_normalized_pages_royalties14d,amazon_common_ads_campaign_report.top_of_search_impression_share AS top_of_search_impression_share,amazon_common_ads_campaign_report.view_click_through_rate AS view_click_through_rate,amazon_common_ads_campaign_report.viewability_rate AS viewability_rate,amazon_common_ads_campaign_report.viewable_impressions AS viewable_impressions from amazon_common_ads_campaign_report where amazon_common_ads_campaign_report.profile_id = 4308644068810310 and amazon_common_ads_campaign_report.report_date >= '2024-05-09'" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "amazon_common_ads_campaign_report.profile_id = 4308644068810310 and amazon_common_ads_campaign_report.report_date >= '2024-05-09'", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "amazon_common_ads_campaign_report.report_date >= '2024-05-09' and multiple equal(4308644068810310, amazon_common_ads_campaign_report.profile_id)" }, { "transformation": "constant_propagation", "resulting_condition": "amazon_common_ads_campaign_report.report_date >= '2024-05-09' and multiple equal(4308644068810310, amazon_common_ads_campaign_report.profile_id)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "amazon_common_ads_campaign_report.report_date >= '2024-05-09' and multiple equal(4308644068810310, amazon_common_ads_campaign_report.profile_id)" } ] } }, { "table_dependencies": [ { "table": "amazon_common_ads_campaign_report", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "amazon_common_ads_campaign_report", "index": "idx_unique_campaign_report", "field": "profile_id", "equals": "4308644068810310", "null_rejecting": true }, { "table": "amazon_common_ads_campaign_report", "index": "search_campaing", "field": "profile_id", "equals": "4308644068810310", "null_rejecting": true } ] }, { "rows_estimation": [ { "table": "amazon_common_ads_campaign_report", "range_analysis": { "table_scan": { "rows": 50260416, "cost": 8752.604829 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "idx_unique_campaign_report", "usable": true, "key_parts": [ "profile_id", "report_date", "campaign_id", "placement" ] }, { "index": "search_campaing", "usable": true, "key_parts": [ "profile_id", "campaign_id", "report_date", "id" ] } ], "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "idx_unique_campaign_report", "ranges": [ "(4308644068810310,2024-05-09) <= (profile_id,report_date) <= (4308644068810310)" ], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 7996150, "cost": 9050.106173, "chosen": false, "cause": "cost" }, { "index": "search_campaing", "ranges": [ "(4308644068810310) <= (profile_id) <= (4308644068810310)" ], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 21480760, "cost": 21921.45011, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "no group by or distinct" } } }, { "selectivity_for_indexes": [ { "index_name": "idx_unique_campaign_report", "selectivity_from_index": 0.159094386 } ], "selectivity_for_columns": [], "cond_selectivity": 0.159094386 } ] }, { "considered_execution_plans": [ { "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "amazon_common_ads_campaign_report", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "ref", "index": "idx_unique_campaign_report", "chosen": false, "cause": "range is simple and more selective" }, { "access_type": "ref", "index": "search_campaing", "used_range_estimates": true, "rows": 21480760, "cost": 21921.44932, "chosen": true }, { "access_type": "scan", "rows": 50260416, "rows_after_filter": 7996150, "rows_out": 7996150, "cost": 8752.604829, "index_only": false, "chosen": true } ], "chosen_access_method": { "type": "scan", "rows_read": 7996150, "rows_out": 7996150, "cost": 8752.604829, "uses_join_buffering": false } } } ] }, { "plan_prefix": "", "table": "amazon_common_ads_campaign_report", "rows_for_plan": 7996150, "cost_for_plan": 8752.604829 } ] }, { "best_join_order": ["amazon_common_ads_campaign_report"], "rows": 7996150, "cost": 8752.604829 }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "amazon_common_ads_campaign_report.profile_id = 4308644068810310 and amazon_common_ads_campaign_report.report_date >= '2024-05-09'" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "amazon_common_ads_campaign_report", "attached_condition": "amazon_common_ads_campaign_report.profile_id = 4308644068810310 and amazon_common_ads_campaign_report.report_date >= '2024-05-09'" } ] } }, { "make_join_readinfo": [] } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] }   TRACE: outputted from https://www.aihello.com sysadmin (user: emp0178)

Sergei Golubchik added a comment - 2024-11-22 21:06 Well, optimizer chooses the table scan based on the cost: "table_scan": "rows": 50,260,416, "cost": 8,752.604829   "index": "idx_unique_campaign_report", "rows": 7,996,150, "cost": 9,050.106173,   "index": "search_campaing", "rows": 21,480,760, "cost": 21,921.45011, You didn't answer the question about the storage engine. Is it amazon_common_ads_campaign_report an InnoDB table?

ganesh krishnan added a comment - 2024-11-22 21:12 Yes its InnoDb. Apologies for missing it. The cost is a technical aspect, however logically if I have index on profile_id and I know profile_id is a subset of the data, MariaDB should always ~100% of the time use the index. In my case this is evident. If I force the index, the query executes quickly. It looks like the cost estimation is incorrect resulting in this situation.

Sergei Golubchik added a comment - 2024-11-22 21:50 For MyISAM, for example, a table scan means sequential disk access and index access implies random access pattern. For rotational disks sequential is much faster, so index must be selective enough (say, 5% or less) to be faster than the table scan. So logically it's not that simple. In your case it's InnoDB and, most likely, SSD, so the above shouldn't be the reason. May be something else is, it needs investigation.