CODEWITHSUNDEEP
mysql
Drunken Daddy
Drunken Daddy

Reputation: 8001

mysql - How to improve case when performance?

I've this query-

SELECT * FROM alerts a 
INNER JOIN alerts_account aa ON a.alert_id=aa.alert_id 
WHERE aa.account_id=638 
AND a.type IN(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17) 
AND CASE WHEN (type = 11 OR type = 13 OR type = 16 OR type = 17) 
THEN (is_acknowledged = 0) AND (TIMESTAMPDIFF(HOUR, time, utc_timestamp()) <= 4) 
ELSE 1=1 
END order by a.alert_id desc limit 10

My table have millions of entries and its the TIMESTAMPDIFF(HOUR, time, utc_timestamp()) that's slowing down the query? It takes more than 10 seconds. How can I improve the performance in this case. Should I even use CASE WHEN here?

EDIT:

alerts

alert_id | type | time                | details| patient_account_id
1        | 1    | 2014-10-22 05:43:45 | 2015   | 234
2        | 5    | 2014-10-22 06:21:23 | 2014   | 345
3        | 12   | 2014-10-22 14:30:23 | 2016   | 456

alert_account 

alerts_account_id | alert_id |account_id| is_poped_up| is_acknowledged
1                 | 1        |234       | 0          | 0
2                 | 2        |345       | 1          | 1
3                 | 3        |456       | 1          | 1

alerts

alerts

alerts_account

alerts_account

Upvotes: 0

Views: 414

Answers (1)

Zafar Malik
Zafar Malik

Reputation: 6854

First create index as per below and check performance-

alter table alerts_account add index idx_alert_id(alert_id);

You get sufficient performance by this further provide me count as I asked to gain more performance.

Even most accurate index we can have only after getting desired count but after seeing your query it seems that mostly data is filtered by account_id field, so you can create final index as per below-

alter table alerts_account add index idx_alert_id(alert_id), add index idx_account_id(account_id);

Upvotes: 1

Related Questions