The following is executing very slow and takes ages to execute. Even with an empty result set, it takes around 280 seconds. There are a few case statements in the query. Some help to optimize the query will be highly appreciated.

**Execution Plan:**
The plan is depending upon the date variable input the field of the date range will be changed in the where clause.
eg: if the date = '1' then, the date range will be queried based on a.created\_date, and if the date = '2' then, the date range will be queried based on t.active\_datetime

**SQL Query:**

```
SELECT  @n:=@n+1 No,  a.name customer_name, a.id_number AS customer_id, a.email AS email_address, CONCAT(a.mobile_country_code, '-', a.mobile_number) AS contact_no,
t.status AS status, a.created_date AS registration_date,  a.account_type, a.account_id AS account_no, c.wallet_id AS wallet_account_no, t.old_serial_no AS r_id,
t.active_datetime AS activation_date, v.number AS registration_no,  v.class AS lass,  (CASE WHEN t.is_free = true THEN 'Yes'  ELSE 'No'  END ) AS replacement,
a.reg_source AS channel, (CASE WHEN a.reg_source = 'ADMIN' THEN t.last_modified_by  ELSE 'SYSTEM'  END )  AS activated_by, t.replacement_date AS replacement_date, 
t.new_serial_no AS new_r_id, d.description AS reason, t.terminated_datetime AS tag_termination_date
FROM abc a 
LEFT JOIN def b ON (b.id = a.id) 
LEFT JOIN ghi c ON (c.id = b.id),  tags t
LEFT JOIN jkl v ON (v.id = t.id)
LEFT JOIN mno d ON (d.code = t.reason_code),
(SELECT @n:= 0) AS n , (SELECT @date := '3') AS date
WHERE
a.account_type IN ( 'PRIVATE') AND  a.account_status IN ( 'ACTIVE') AND t.status IN ('ACTIVE') AND
a.reg_source IN ('ADMIN') AND   
CASE 
WHEN @date = '1' THEN a.created_date BETWEEN '2020-11-24'  AND '2020-11-24'
WHEN @date = '2' THEN t.active_datetime BETWEEN '2020-11-24'  AND '2020-11-24'
WHEN @date = '3' THEN t.replacement_date BETWEEN '2020-11-24'  AND '2020-11-24'
WHEN @date = '4' THEN t.terminated_datetime BETWEEN '2020-11-24'  AND '2020-11-24'  
END ;

```