Selecting mail messages that meet one of four search criteria This is the current very repetitive and long SQL I have. Unfortunately I'm not that good at SQL to group this and get rid of the repetition.

select * from Mail 
where admin_status = 1 
and admin_id = 4 
and core_event_id =0 
and user_id !=0 
and mail_reply_status = 0 
and sent_to = 0
and admin_inbox_date &gt; DATE_SUB(CURDATE(), INTERVAL 48 HOUR)

or ( admin_status = 1 
and admin_id = 4  
AND sent_to = 0 
and mail_reply_status = 0 
and user_id != 0 
and core_event_inactive = 1)
and admin_inbox_date &gt; DATE_SUB(CURDATE(), INTERVAL 48 HOUR)

or (`subject` like 'RE:%' 
AND admin_status = 1 
AND `admin_id` = 4 
and core_event_id =0 
and user_id != 0 
and mail_reply_status = 0 
and sent_to = 4 )
and admin_inbox_date &gt; DATE_SUB(CURDATE(), INTERVAL 48 HOUR)

or (`subject` like 'RE:%' 
and admin_status = 1 
and admin_id = 4  
AND sent_to = 0 
and mail_reply_status = 0 
and user_id != 0 
and core_event_inactive = 1)
and admin_inbox_date &gt; DATE_SUB(CURDATE(), INTERVAL 48 HOUR)
First of all, the indentation is next to inexisting. The code is badly formatted. Are you scared of tabs? There's no excuse for a wall of unformatted SQL. There are online tools to fix it for you!



You should have all keywords in capitals. Why is that? Well, it's an old convention that comes from times where there was no syntax highlighting. Nowadays, they are everywhere! Heck, even I made one! But still, capitals help to read it.

And as such, your Mail table should be all in lowercase.



You are extremelly inconsistent with your backticks. Either you use it or you don't use it! Don't mix both, please!



And now, to remove repetition.


All messages are within an interval of 48 hours.
All have admin_status = 1
admin_id is always the same
sent_to only changes if subject like 'RE:%'
mail_reply_status is always 0
user_id is never 0
core_event_inactive shows twice with the value of 0


With this information, I've made this:

SELECT * FROM mail WHERE admin_status = 1
    AND admin_id = 4
    AND user_id = 0
    AND mail_reply_status = 0
    AND admin_inbox_date &gt; DATE_SUB(CURDATE(), INTERVAL 48 HOUR)
    AND ( sent_to = 0 XOR ( sent_to = 4  AND subject LIKE 'RE:%' ) )


Since the LIKE is a really heavy check, we put it at the very last. Since SQL supports short-circut evaluation, this positioning may result in faster queries. The slowest checks are the last ones.

It was really hard to follow if everything is 200% correct, but I hope I've nailed it. Please, comment if you see something innacurate.