MySQL Index not working ( Use Case specific scenario)

Question

So far following is my scenario :

Parameters controlled by user: (These parameters are controlled by a dashboard but for testing purposes I have created sql parameters in order to change their values)

    SET @device_param := "all devices";
    SET @date_param_start_bar_chart := '2016-09-01';
    SET @date_param_end_bar_chart := '2016-09-19';
    SET @country_param := "US";
    SET @channel_param := "all channels";

Query that runs at the back-end

SELECT 
  country_code,
  channel_report_tag,
  SUM(count_more_then_30_min_play) AS '>30 minutes',
  SUM(count_15_30_min_play) AS '15-30 Minutes',
  SUM(count_0_15_min_play) AS '0-15 Minutes' 
FROM
  channel_play_times_cleaned 
WHERE IFNULL(country_code, '') = 
  CASE
    WHEN @country_param = "all countries" 
    THEN IFNULL(country_code, '') 
    ELSE @country_param 
  END 
  AND IFNULL(channel_report_tag, '') = 
  CASE
    WHEN @channel_param = "all channels" 
    THEN IFNULL(channel_report_tag, '') 
    ELSE @channel_param 
  END 
  AND iFnull(device_report_tag, '') = 
  CASE
    WHEN @device_param = "all devices" 
    THEN iFnull(device_report_tag, '') 
    ELSE @device_param 
  END 
  AND playing_date BETWEEN @date_param_start_bar_chart 
  AND @date_param_end_bar_chart 
GROUP BY channel_report_tag 
ORDER BY SUM(count_more_then_30_min_play) DESC 
limit 10 ;

The index that I have applied is

CREATE INDEX my_index 
ON channel_play_times_cleaned (
  country_code,
  channel_report_tag,
  device_report_tag,
  playing_date,
  channel_report_tag
)

I have followed this link : My SQL Index Cook-Book Guide to create my index.

However the EXPLAIN keyword while executing the above query tells me that there is no index used.

I want to what am I doing wrong over here ?

Answer 1

1. You use functions and case expression in the first 3 where condition. Simple field index cannot be used to speed up such look ups.

2. MySQL could potentially use an index for the playing_date criteria, but that field is not the leftmost in the cited index, therefore the cited index is not suitable for that either.

If I were you, I would remove the logic from the where criteria and moved that into the application layer by constructing such an sql statement that has the case conditions resolved and emits only the necessary sql.