Welcome Guest, Not a member yet? Register   Sign In
Best way to count the number of rows
#1

[eluser]Cro_Crx[/eluser]
Hey everyone

I'm creating a web page application where users can submit answers to questions that are posted. At the moment i'm still in the development stages.

In my application I need to count the number of answers to each question. I'm using MySQL with the MyISAM storage engine (as users will be viewing records a lot more than writing to the datbase so i'm told MyISAM is a better choice).

I've denormalized the database by adding a total_answers in the questions table (so i don't have to count the number of answers for each question on every page load.

I was planning on using transactions to make sure that the count numbers were accurate so for example

Code:
Start transaction
Insert an answer
add 1 to the total_answers column in the questions table
stop transacation.

Although I've just realized that MyISAM doesn't actually support transactions, although apparently running count(*) statements are quick quite on MyISAM.

So my question is, would it be better to use InnoDB on all my database tables and use the total_column along with transactions to make sure they are accurate.

OR

Would it be best to just stick with MyISAM and use statements something like this:
Code:
SELECT Count(*) FROM 'answers' WHERE question_id = '5';

If you need some more information to be helpful just let me know... but yeah i'm unsure of which method to choose!
#2

[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.
#3

[eluser]Cro_Crx[/eluser]
Hey Mike

Thanks for the response. Yeah I could use triggers, it's not a bad idea, i'm just wondering if there would be much benefits as I hear MyISAM can do count(*) quite quickly.

Yeah I was reading that Oracle bought out Sun Microsystem, I didn't realise that MySQL was a part of sun. Anyways, i run my own server so I could install Oracle if need be, although i'm much more familiar with MySQL installs.

I tried to search but couldn't find any more info, i wonder if anyone knows if count(*) is quicker or quick enough rather than using denormalization
#4

[eluser]Mike Ryan[/eluser]
The free version of Oracle is limited to 4GB of data, and 1GB of RAM. Depending on your anticipated growth this might not be an issue, but I thought I would mention it.

wrt count optimisation, yes, it is very efficient as long as you do count(*) or count(field) where field is NOT NULL and field is an index. More info
#5

[eluser]xwero[/eluser]
Mike postgre is partly sponsored by Sun so Oracle will have its hooks in postgre too.
#6

[eluser]Mike Ryan[/eluser]
Thanks xwero, I didn't know that. As long as they don't have any official control/ownership I'm not too worried - although I bet that sponsorship money will dry up pretty soon now :-)
#7

[eluser]xwero[/eluser]
I just wanted to point out mysql is a good database product no matter which company has the rights. If microsoft buys the rights on php would you switch to another language?

If you create an application or a site work with the tools you know best because then you will have the best result. If something promising comes along experiment with it until you have a good idea of the good and bad things about it and then decide if you add it to your toolbox or not.
#8

[eluser]Mike Ryan[/eluser]
[quote author="xwero" date="1240593698"]I just wanted to point out mysql is a good database product no matter which company has the rights. If microsoft buys the rights on php would you switch to another language?
[/quote]

I would not switch immediately, but I would begin looking at alternatives to make sure I had an "exit route" if I did not like the direction it was going in. Also, the community is what makes the open-source product - if PHP was bought by MS and 90% of the core PHP team left to work on a fork... well that fork could quickly become the "new PHP", even if MS owned the rights to the name PHP. I understand this is the case with MySQL - the core dev team have all moved to a new fork.

Having said this, I do agree xwero - if you are happy with MySQL and know it well, it makes sense to use it.




Theme © iAndrew 2016 - Forum software by © MyBB