Selecting mail messages that meet one of four search criteria
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.