Database Table Design

[sniko]

Although this has been pointed out within the community numerous times, I thought I'd write an 'article' on it...

Within McCodes Version 2 - Unsure with other versions, the database table designs are poor, in my opinion. Let me explain;

Let's look at the most infamous table of them all, `users`.

In one of the main files, which are called upon on every request a user makes on your site globals.php, everything (All columns) are selected - with a condition, a userid session, set when you log in - authenticate.php. So, In the base McCodes, it would select 68 columns, on every single request the user makes, just from the users table.

You may think, but I need all those columns, and having a Left Join will simply do no help. So, let's take a little snippet from the `users` table.

1. course
   
2. cdays
   
3. gang
   
4. daysingang
   
5. jobrank
   
6. fedjail
   
7. forumban
   
8. fb_reason
   
9. friend_count
   
10. enemy_count
    
11. boxes_opened
    

As all of these are default 0, and rely on other systems for a change, why don't we create a new table for each (grouping relevant ones together) and having `userid` as the foreign key, to link. Here's my reasoning;

As stated before, they rely on other systems for a change, so what happens if they don't use that specific system whilst playing, for example, they never get forum banned, or they don't donate so their friend or enemy count won't go up (pretty useless anyway, as you can use mysql_num_rows or count() with a specific condition) so this means that every time a user makes a request (Clicks a link on your game, to another page) a query to the database is made, which selects these 'redundant' columns, wasting space, and resources.

So here's what I propose;

We, developers, game owners, whoever, makes a table for these columns (grouping relevant ones together) to save resources and space, possibly resulting in saving money.

Here's one for courses

The SQL

This will take out the two 'redundant' columns from the `users` table, resulting in only 66 columns being selected at each request from the `users` table.

1. Insert data into the new table `users_courses` when they start a new course

2. Select the data when needed

3. Use mysql_free_result once finished with selecting from the new table to free memory

Now, if we did this, or better yet, it was set up like this, we'd be saving a lot of resources each request a user makes.

Shame it wasn't set up like this, as it would take a while to implement, especially for games that have been edited, with new features/systems.

 

So, as a community, here's what I propose;

Whenever we create a system, for McCodes, or not, we try our very best to not insert more columns into the `users` table, but create a new table, like the one I did for `users_courses`. I'm not telling you what to do, I'm advising you, as users, In my view, will get a better playing experience, from a faster response.

Your views?

-sniko

Systems

Courses

Edited March 11, 2012 by sniko

[Curt]

I have actually starting doing this with my game several months back. Instead moving the data into a lot of different tables, I have begun moving rows of columns to a second table which is called when needed.

[newttster]

Sniko ... if I understand correctly ... the free_result query that you are talking about ... accesses the table as it does the action and then releases the table and resources when finished doing the action?

How would this effect resources on the players profile when accessing all of the various tables? IE user info, banking info, house info etc. Would that create more usage or less in reference to time loading etc?

[a_bertrand]

Sniko: did you made some tests to see if there is really a possible gain by splitting the table?

I have NO CLUES of how McCode work, so I can't really talk for it, however from my own researches, if I have a table with let's say 40 columns, and I query 10 or 40 of them I see little to no difference, where if I need to join 4 tables or issue 4 queries to get the same 40 values it will indeed have an impact.

Yet, there is an issue which is more annoying of having a single table for all, it's that if 2 modules want a column with the same name but for different purpose... well you will have an issue. Also, installing / removing a module means, touching a table (which personally I don't like to do on a production system).

[Spudinski]

I think you missed some very "basic" logic with what you proposed.

1. Creating tables to free up resources and space.

Ok, fine. But if you knew MySQL, you'd know a table is much larger on the disk than a column in another table.

Also to go along with that: Yes, it is better to create different tables for relevant data, but not if it's only going to hold a few - mostly static - records.

2. Use mysql_free_result.

Uhmm..... You said you want to optimize queries to use LESS memory right?

I think you misread the term paging(query caching w/ MySQL) somewhere.

Another part you missed while reading the PHP docs:

mysql_free_result() only needs to be called if you are concerned about how much memory is being used for queries that return large result sets. All associated result memory is automatically freed at the end of the script's execution.

I highly doubt that even a 200 column row will yield a large enough set of data to even acquire the status of a "slow query".

[a_bertrand]

Slow queries are when you start group functions, sub queries and more. Simple "where id=?" will not be slow queries... unless you miss the index keys and do a full table scan which again can end up in slow queries.

[sniko]

@newttster - It frees the memory that's allocated to the query. As the query needs to be held somewhere in memory, as it's put to a variable. As I stated before, select what's needed, when needed, so in the instance of player profiles, more will be selected, but not from the globals.php file, from the players profile. In my view, it depends what you select, I.E not using the wildcard (*), only list the fields you need.

@a_bertrand - Although I haven't done physical tests, I've done theory, in my computing class, and how memory can be used and preserved. As explained, McCodes uses the wildcard (*) in globals to select from numerous tables, using a LEFT JOIN. The aim of this post was to cut down the redundant columns being selected, and only selecting what's needed, when needed. What I mean by this, is that some columns rely on other systems to have a change in value, and if they never use that system, it will always be default, 0. Although you've said that using 10 - 40 columns and having little difference, there is difference. "Every little helps" #Tesco ;)

@Spudinski - Yeah, I am aware that a table does take more memory than columns, but let's assume that you have a user base of 10,000+. Only using the courses columns `cdays`,`course` - both int(11) - 11 bytes of data - 11 x 10,000 = X. (Maths maybe wrong to calculate the used memory, but you get the drift) As I stated before, if a user doesn't use that system, it'll stay default, 0, which in the way the column is set up, will be 00000000000 (not just a single 0, as the rest of the int has to be filled, as it's set up int(11)). So, X is unused memory, which will be using resources, when you can cut it down. Now, I didn't know about the "All associated result memory is automatically freed at the end of the script's execution.", so thank you for spotting that out!

@SomeRandomBastard - Ah, thank you, I'm am just used to putting in values, and pressing 'Go', and forgetting to change the charset

I hope this clears some things up, but please state that if I am wrong, I am wrong, or I have a misconception.

[a_bertrand]

sniko: what I'm saying, theory is all good and fun, but is nothing till you actually have proofs. Do the checks and then think about changing something. Why? Because imagine you spend days to rewrite all... and you end up being slower than before? So really, do some trials yourself, check what will happen if you have multiple tables or just one and see if there is a positive or negative impact. The little memory used to store a whole row even if it's not used will have no impact on your server. Sure defining the columns you wait for and actually NOT USE the name / value array but instead just numbered arrays do have sense but it's not given that you actually see much difference if any. And if you don't know if you will actually see difference, I would think it's smarter that you work on some other areas. BTW didn't your teacher said: don't fix what's not broken? Till you see issues, do NOT optimize. That's a normal rule.

[sniko]

@a_bertrand - Although I agree with you 100%, I think, in this day in age, we need to take things with a different approach, sure, use the normal methods, but expand on them, and make computing much faster and logical. Anyway, I thank you for your replies.

[Spudinski]

@sniko: Yes, your calculations are wrong.

The number zero(0) is one bit, it doesn't use 11 bytes(or 11x8=88bits) of memory.

But let me make it clear: A memory size of 11 integers could be allocated by that specific column.

Also MySQL does not ZEROFILL by default, you have to specify it.

Ps. I lol'd on:

11 bytes of data - 11 x 10,000 = X.

pow(10) is used when converting bits to digits, aka, printable max().

In which case the answer would have been: 11^10 = 25937424601

[sniko]

@sniko: Yes, your calculations are wrong.

The number zero(0) is one bit, it doesn't use 11 bytes(or 11x8=88bits) of memory.

But let me make it clear: A memory size of 11 integers could be allocated by that specific column.

Also MySQL does not ZEROFILL by default, you have to specify it.

Ps. I lol'd on:

pow(10) is used when converting bits to digits, aka, printable max().

In which case the answer would have been: 11^10 = 25937424601

That made it clearer, many thanks!!