I'm building a chat for a turn-based game (using PHP and MySQL) where I only want to save the 30 most recent messages in the MySQL database.
Currently, I'm doing 3 queries to delete the oldest message when the count exceeds 30:
-
Insert the new chat message as a new row:
INSERT INTO chatMessages (userId,matchId,message,timestamp) VALUES (".$params["userId"].",".$params["matchId"].",'".$params["message"]."',NOW())
-
Check if there are too many messages in the database, and get the oldest one's Id.
SELECT COUNT(id) AS count, id AS oldestId FROM chatMessages WHERE matchId = ".$params["matchId"]." ORDER BY id ASC LIMIT 1
-
Remove the oldest message
DELETE FROM chatMessages WHERE id = ".$oldestId
Is there any way to do this in 2, or even 1 single query? We have quite a lot of traffic on our servers, so performance is key.