Mysql query performance multiple and or conditions-CodePudding

I have this query in mysql with very poor performance.

select `notifiables`.`notification_id` 
from `notifiables` 
where `notifiables`.`notification_type` in (2, 3, 4) 
  and (    (    `notifiables`.`notifiable_type` = 16 
            and `notifiables`.`notifiable_id` = 53642) 
        or (    `notifiables`.`notifiable_type` = 17 
            and `notifiables`.`notifiable_id` = 26358) 
        or (    `notifiables`.`notifiable_type` = 18 
            and `notifiables`.`notifiable_id` = 2654)) 
order by `notifiables`.`id` desc limit 20

Is this query can be optimized in any way. Please help This table has 2M rows. and taking upto 1-4 seconds in searching

CodePudding user response：

You can make different kinds of "VIEW" from the data you want and then join them.

CodePudding user response：

Use the next syntax:

SELECT notification_id
FROM notifiables 
WHERE notification_type IN (2, 3, 4) 
  AND (notifiable_type, notifiable_id) IN ( (16, 53642), (17, 26358), (18, 2654) )
ORDER BY id DESC LIMIT 20

Create index by (notification_type, notifiable_type, notifiable_id) or (notifiable_type, notifiable_id, notification_type) (depends on separate conditions selectivity).

Or create covering index ((notification_type, notifiable_type, notifiable_id, notification_id) or (notifiable_type, notifiable_id, notification_type, notification_id)).