Ad:

PHP Discussion Thread
Unsolved
Views: 744

Jan 2nd, 2009

Which is better for storing user data?

Expand Post »

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

Similar Threads

Button to open a new browser window in ASP.NET
Sessions: Best method for keeping data in PHP
Problem when using a template classes to store user-defined types in C++
Simple login (read data through txt file) in Visual Basic 4 / 5 / 6
data grabbing from html sites in Python

danielpataki

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

28 posts
since Oct 2008

Permalink

Jan 2nd, 2009

Re: Which is better for storing user data?

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 Syntax (Toggle Plain Text)
<?php
$startTime=time();
echo $startTime;
run your count
 
$timePassed=time()-$startTime();
echo $timePassed;
?>
<?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.

DiGSGRL

Reputation Points: 10

Solved Threads: 4

Light Poster

Offline

45 posts
since May 2008

Permalink

Jan 2nd, 2009

Re: Which is better for storing user data?

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) .

danielpataki

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

28 posts
since Oct 2008

Permalink

Jan 2nd, 2009

Re: Which is better for storing user data?

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..

DiGSGRL

Reputation Points: 10

Solved Threads: 4

Light Poster

Offline

45 posts
since May 2008

Permalink

Jan 4th, 2009

Re: Which is better for storing user data?

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

danielpataki

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

28 posts
since Oct 2008

Permalink

Jan 6th, 2009

Re: Which is better for storing user data?

Click to Expand / Collapse Quote originally posted by 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

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

Moderator

Reputation Points: 457

Solved Threads: 101

Nearly a Posting Virtuoso

Offline

1,250 posts
since Sep 2005

Permalink

Jan 6th, 2009

Re: Which is better for storing user data?

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

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

digital-ether

Moderator

Reputation Points: 457

Solved Threads: 101

Nearly a Posting Virtuoso

Offline

1,250 posts
since Sep 2005

Permalink

Jan 6th, 2009

Re: Which is better for storing user data?

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

danielpataki

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

28 posts
since Oct 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: Call Home Function (Like paid scripts have)

Next Thread in PHP Forum Timeline: Dynamic arrays

DaniWeb Message

Cancel Changes

User Name
Password