I need each page of the site to display the number of new messages for the user (for example, 30 new messages). To do this, every time I need to retrieve count(*) number of messages for a given user from the table (and if there are only 50 messages in the table for all users, then it is expensive). How in this situation to arrive, can need use caching of the request?

    4 answers 4

    Because It is better to update the messages more than once per hour. I suggest making a table with special data (messages, updates of a different kind) and updating it with appropriate actions.

     function addMessage($from, $to, $msg) { /* code */ mysql_query('UPDATE upd_table SET total_in_messages=total_in_messages+1 WHERE userid='.$to.';'); mysql_query('UPDATE upd_table SET total_out_messages=total_out_messages+1 WHERE userid='.$from.';'); }

    There you can also make a counter new_messages_counter , for example.

      And you can do "ORDER BY date_added DESC LIMIT 30", not too expensive)

        count (*) is a muscle-optimized function and does not waste time on data transfer. so it's really not very expensive.

        • That is, if count ( ) is called in this form: SELECT COUNT ( ) FROM aaa , and if I still have WHERE, then I understand that he needs to refer to columns to love - Arthur Lodenev
        • The time of the mysql request is divided into execution time, and data transfer time. If you don’t pull information out of the columns, then the time will be short, addressing the columns in the where clause, there’s nothing to do with it. To increase the speed, you can add an index for the field by which you filter the selection (where ..) - draev

        Few develop the idea of @ Sh4dow , create an additional table with {userID, privateMessageCount} updating privateMessageCount occurs via a trigger to insert a record of a new message for a given user into the user's private message table (you shouldn’t do this through additional requests as it suggests @ Sh4dow - meaningless load for mysql, albeit small).

        Yes, and if necessary to get count (*), we simply get the data from the new table I described above.