如何向后运行mysql查询？

Question

The title of question may sound weird but with my english could not get better title.

A have created chat aplication in my website. Now I want to add notifications.

When submitting a new message I am checking if other user has read previous message in given conversation. If he has then I write a new notification. If he has not seen previous then I do not write a new notification.

I use mysql count() function to count fields and then do the php logic. In CI it looks like this:

public function ifUnreadMsgs($con_id, $sender_id)
{
    $this->db->where('conversation_id', $con_id);
    $this->db->where('sender_id', $sender_id);
    $this->db->where('seen IS NULL', null, false);
    $this->db->from('messages');
    $count = $this->db->count_all_results();

    if($count > 0){
        return true;
    }else{
        return false;
    }
}

My question is about optimization. I do know that with time I will have a lot of messages. Lets say I have 1 000 000 messages stored in database. I also know that the one with possible "NULL" in seen will be with msg_id of approx. 999 995. I have to use this query often and user waits for ajax response so I want to reduce the time for query as much as possible.

Is it possible to run query backward and stop as I hit the value I was looking for? I thought about using DISTICT or LIMIT keyword for stopping but how to run it backwards?

EDIT:

Actually I need to start looping through messages table starting from last row, stop at "conversation_id" and look if "seen" is NULL or not.

Answer 1

you can use ORDER BY for both ascending & descending order. by default ORDER BY starts from the first, but you can start it by adding DESC. Please check this & that