Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.5.1
-
Ubuntu 24.04.1 LTS
Description
Mariadb is incorrectly not using any indexes
example
EXPLAIN SELECT * FROM amazon_common_ads_campaign_report WHERE profile_id = 4308644068810310 AND report_date >= '2024-05-09'; |
Returns
|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|
|
|
For Index
|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|
|
|
Profile_id, the first column in index, has low cardinality to warrant using index idx_unique_campaign_report but the query skips all indexes due to high cardinality of second column in the query (report_date).
Hence, instead of scanning through 77442 rows for the profile_id, its scanning for 4.5m+ rows with the report_date