Mysql: when to use triggers

Question

I am currently making an online multiplayer chess game. I was wondering if it would be appropriate to user triggers to log in every movement.

Then, using nodejs I would repetitively check the trigger table and update the visual for both players & observers.

Since I only need to make changes in the database, the visual aspect would follow up automaticly (using the recurring function to check changed data in the database). The goal of this would be to seperate the visuals from the logic involved to make the game.

Would any of you recommend this technique or its simply a no go?

Answer 1

You describe a possible technical solution for your task. But i strongly recommend NOT to do so.

This will not scale very well - it adds a lot of overhead and load to both your database and application server.

There are other lightweight possibilities that scale much better:

• use a message queue like (for example) redis with the node_redis client. It has built-in pubsub semantics.
• abstract your database calls and push all database updates to the message queue, too.
• instead of using a "recurring" function (AJAX poll) to get status updates, you could use a HTTP streamer like jquery-stream or jquery-socket for example. This avoids the overhead of opening a new HTTP connection for each client update.
• use the event-driven features of nodejs on the server side to push new events to the client connection.