How can I make this sql query faster?

Question

I have a table user_notifications that has 1100000 records and I have to run this below query but it takes more than 3 minutes to complete the query what can I do to improve the fetch time.

SELECT `user_notifications`.`user_id`
FROM `user_notifications`
WHERE `user_notifications`.`notification_template_id` = 175
AND (DATE(sent_at) >= DATE_SUB(CURDATE(), INTERVAL 4 day))
AND `user_notifications`.`user_id` IN (
  1203, 1282, 1499, 2244, 2575, 2697, 2828, 2900, 3085, 3989,
  5264, 5314, 5368, 5452, 5603, 6133, 6498..
)

the user ids in IN block are sometimes upto 1k.

for optimisation I have indexed on user_id and notification_template_id column in user_notification table.

Answer 1

Big IN() lists are inherently slow. Create a temporary table with an index and put the values in the IN() list into that tempory table instead, then you'll get the power of an indexed join instead of giant IN() list.

Answer 2

You seem to be querying for a small date range. How about having an index based on SENT_AT column? Do you know what index the current query is using?

Answer 3

(1) Don't hide columns in functions if you might need to use an index:

AND (DATE(sent_at) >= DATE_SUB(CURDATE(), INTERVAL 4 day))

-->

AND sent_at >= CURDATE() - INTERVAL 4 day

(2) Use a "composite" index for

WHERE `notification_template_id` = 175
  AND sent_at >= ...
  AND `user_id` IN (...)

The first column should be the one with '='. It is unclear what to put next, so I suggest adding both of these indexes:

INDEX(notification_template_id, user_id, sent_at)
INDEX(notification_template_id, sent_at)

The Optimizer will probably pick between them correctly.

Composite indexes are not the same as indexes on the individual columns.

(3) Yes, you could try putting the IN list in a tmp table, but the cost of doing such might outweigh the benefit. I don't think of 1K values in IN() as being "too many".

(4) My cookbook on building indexes.