Best way to count the number of rows

[eluser]Mike Ryan[/eluser]
Hi,

If you are using MySQL >= 5.0.2 you can use triggers:

Code:

CREATE TRIGGER ins_answer AFTER INSERT ON answers
  FOR EACH ROW
    UPDATE questions SET total_answers = total_answers + 1
      WHERE questions.id = NEW.question_id;

After each insert in the answers table, questions.total_answers will be incremented by one. You would also need to implement a trigger to run "AFTER DELETE ON answers" to decrement this value. I don't have a MySQL install to test this on, so let me know if there are any errors. The syntax could be slightly-off but the idea is sound.

Off-topic: As you are still in the dev stages, it might be a good time to think about changing to Postgres. Oracle now owns (or is about to own) MySQL and there are forks popping up all over the place - I am not positive about its long-term future. Unless you have used any MySQL specific functions (like triggers ;-) ) changing to Postgres is a painless process - once it is installed, just change the "db driver" in your config.