Which is better for storing user data?

[danielpataki]

Hi everyone!

I'm working on coding my own forum and I have no problem with this, I would like to ask those of you knowledgable about the inner workings of php and mysql about which method is faster.

So far I have always been determining post counts, discussions started counts for users using a mysql query, counting all the discussions with the actual user's ID.

However, it might be better/faster to also record this in the user table. I could have a "comment_count" and a "discussion_count" column, when a user adds a comment not only a new comment gets inserted, but the comment_count in the users table is updated using comment_count = comment_count +1. This can be pulled using a session variable, so no php processing would take place at each refresh to get these numbers (which are displayed in the sidebar).

My question is that is this a better way to go? It does mean that I will have somewhat redundant columns, since they could be calculated from other tables.

Thanks a lot,

Daniel

[DiGSGRL]

I have a database with almost 3000 entries in it at the moment and running a simple count from MySql runs very quickly.
If you are interested in the numbers you can run put a time at the top of your script and after its finished running you can check the end time.

<?php
$startTime=time();
echo $startTime;
run your count

$timePassed=time()-$startTime();
echo $timePassed;
?>

If using count with MySQL does it fast enough for you then I would use that. Avoid redundancy if you can because its well.. its redundant lol.

Hope this helps you.

[danielpataki]

Hi DiGSGRL!

Thanks for your help, but I don't really need to time it, I am sure its faster since I am saving the time of 3-4 queries. All data in a session variable this way. My question was actually more geared toward the database theory side. This is not much redundant data, I just find it more comfortable, since why should these values be calculated each andf every time (even if they didn't change) .

[DiGSGRL]

Ok let me set this out and see if I am understanding what exactly you what to do with the comment count.

You have comment count that I am guessing is a variable storing how many comments a user has made. Are you using this variable to show how many comments a user has made?

My concern is that if you are doing that, when you show the amount of comments a user has made that it would not update as they made more comments. Post#1 will always have this user commented one time and so on.

Other than that there are some security issues with session variables. I read about this a while ago when I was setting up my sessions but I do remember reading that if someone wanted to they could change the data stored in the variables. This is a complicated thing to accomplish and if you are on https then I think it gets to the point where it isn't worth it to the hacker since the information is all encrypted. I am sure the number of comments someone has made is high on the hacking a forum session variable list but I guess its just something to think about.

Yea i'll stop letting my mind wander freely across the keyboard and let you get back to your work lol..

[danielpataki]

Hi DiGSGRL

I don't think what you are saying really applies here, especially the security issue. What I want to get around is recounting how many comments someone has made after every refresh.

I can do this by storing their comment count as an integer in the Users table. So this variable is created once, say the value is five. If they comment on a discussion the comment will be stored in the database, the integer in the Users database will be incremented by one AND the Session variable will also be incremented by 1, to match the new value. This means I don't always have to select those 5 comments from the 10,000 which saves processing time.

If the user is proficient enough to change the Session Variable then why not? This is not a security issue. He can not change the database value and after he logs out he would have to hack it again, in any case, he would be the only one to see it.

Thanks for the ideas anyway, anything might help!

Daniel

[digital-ether]

Hi DiGSGRL

I don't think what you are saying really applies here, especially the security issue. What I want to get around is recounting how many comments someone has made after every refresh.

I can do this by storing their comment count as an integer in the Users table. So this variable is created once, say the value is five. If they comment on a discussion the comment will be stored in the database, the integer in the Users database will be incremented by one AND the Session variable will also be incremented by 1, to match the new value. This means I don't always have to select those 5 comments from the 10,000 which saves processing time.

If the user is proficient enough to change the Session Variable then why not? This is not a security issue. He can not change the database value and after he logs out he would have to hack it again, in any case, he would be the only one to see it.

Thanks for the ideas anyway, anything might help!

Daniel

Caching the counts is probably a very good idea. Database normalization is just a model, realistically a good database structure would have a few redundant tables for efficiency, some data cached in PHP logic, and some in sessions etc.

You should also index the row that references the users, eg: userid. That way this is saved in mysql memory, so referencing it is faster. MySQL has a couple of storage types (which has changed over the different versions). The two common ones being MyISAM and InnoDB.
MyISAM does not use transactions, so it is usually much faster for single queries.
InnoDB uses transactions, so data updates are verified for their integrity. This makes it a lot slower.
Unless you're dealing with sensitive data, such as a payment processing system you'd probably want MyISAM.
You can also mix tables, but JOINS between to different storage engines don't work (I think).

Also, your PHP and mysql logic will eventually become HTML output, so it can be a good methodology to group caching based on HTML output instead of your PHP or MySQL logic.

For example, in the MVC pattern, you would be caching on the "view" level.

CMSs usually use this cache model to cache per extension ouput, instead of the whole page. So there could be a block of HTML that shows the latests posts, it could be dynamic, and there could be a block on the same page that shows the popular posts, and it could be cached.

[digital-ether]

BTW: here's docs on the different storage types in MySQL:
http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html

Here's on mysql performance:
http://www.mysqlperformanceblog.com/

[danielpataki]

Hi digital-ether!

Thanks, your post cleared up most of my questions :) I am actually, I think quite proficient at databses and mysql but I am self taught and haven't had time to go into the "hard theory", so while I know how to do stuff I am not the best at the actual database mechanics.

Thanks again for your clarification!

Daniel