Page 1 of 1

Can multiple entries in a mysql table column cause server to

Posted: Sun May 22, 2011 2:27 pm
by drayarms
Here is one of those purely conceptual questions, which involves no code. I'm trying to create a select query which among other things, allows a user of a website to search other members who fall within a particular age range. I have a column in the table where the members' information are stored which records their ages. Which brings us to the problem. On any given day, a member's age may increase by one year compared to what it was the previous day, hence the need to update this column periodically. I can't think of any way to automatically update this column on a daily basis. So my solution is to run an initial update query every time a member tries to search other members based on age, which updates the age column for all other members, before running the select query which eventually retrieves the desired age range. This leads to the second problem. Imagine there are thousands of users using the website. At any given instance, there could be hundreds of members, trying to search others based on age. This means hundreds of users will be updating a single column (the age column) in one table at the same time. Is this feasible? Can it cause the server to crash? Or is there really a more reasonable way to do all of this? Thank you all for taking your time to read this. Appreciate any responses.

Re: Can multiple entries in a mysql table column cause serve

Posted: Sun May 22, 2011 5:05 pm
by mikosiko
I have a column in the table where the members' information are stored which records their age
That is an incorrect approach precisely for the update issues that you described . Don't store "age" in the table... store just DOB's (date of birth) in a DATE field and calculate the age when necessary using date's functions.

Re: Can multiple entries in a mysql table column cause serve

Posted: Sun May 22, 2011 9:26 pm
by califdon
mikosiko wrote:
I have a column in the table where the members' information are stored which records their age
That is an incorrect approach precisely for the update issues that you described . Don't store "age" in the table... store just DOB's (date of birth) in a DATE field and calculate the age when necessary using date's functions.
ABSOLUTELY! This is an example of storing dependent data in a field (dependent on what the current date is). Never store dependent data in a database. Store the raw data, in this case, the date of birth, which is not dependent on when you check its value, nor on anything else in the record, other than the primary key.

Re: Can multiple entries in a mysql table column cause serve

Posted: Mon May 23, 2011 4:48 am
by emelianenko
I have tested this myself and it works. I got it partly from elsewhere but modified it slightly as there was a small error in the original code. The "from_days" cares for the fact of those born on days of leap years.

SELECT extract(YEAR FROM from_days(datediff(curdate(), dob))) AS age

FROM students

GROUP BY age;