Values for fields

[newttster]

Okay ... this is something that I am trying to get a hang on. I have read the documentation that is available and I am still somewhat puzzled about it.

For some examples.

In the standard users table that comes with McCodes;

The daysold field is using int(11) ... now it seems to me that that is a waste of storage because if you work it out value wise that equals 5,883,516 years. Where as if you were to use smallint it works out to 89 years. You can't use tinyint because you wouldn't get a full year out of it.

The IP storage fields are varchar(255). When really all you need is varchar(16).

And that's another thing ... if you were to use char instead of varchar ... the theory is that char is faster. The problem with this is that according to what I have read you have to use one or the other in a table. If you use char in one field, use it in all fields.

Now I know that the number inside the () is for display purposes when it comes to numbers ... but when it comes to char and varchar ... it applies to both storage and display. Right?

See where the confusion comes in.

Any thoughts and ideas on how to make this clearer ... meaning explaining it in dum dum terms would be greatly appreciated.

[a_bertrand]

- IP with 255 varchar is certainly too big. Yet you may want to keep a bit of space due to IP V6.

- Char / Varchar: you can mix both in the same table however if you do so you may actually not gain anything from the usage of char. I would personally use char only if the size of the field is constant. Which means, if I know I will ALWAYS use 2 char then I use char, otherwise I will always fallback to varchar. However it's up to you to choose your strategy. I doubt you will see all that much difference.

- Int / small int, yes sure you could gain some space by using the right type of data. But check exactly the size taken by your record / table and then think about the size of your disk and tell me if that will actually make a huge difference. Maybe not. So for something where you know for sure it will not go above a given limit then choose the right one, for the other leave a little margin doesn't harm.

Overall, the strategy used by McCodes 2 to store data is certainly not the best but changing completely the table structure will quiet certainly kill the possibility to use many mods out of the box. Maybe you should consider other engines before simply changing the table structure of McCodes as the issues don't stop there and you will have so much work to really clean it that you may actually gain more time from starting from scratch or as said use another engine.

[newttster]

Thanks Alain ... that sort of clears up some things for me.

I have been cleaning up the tables that came with McCodes ... well atleast as much as I can with my limited knowledge. I know that alot of the mods need to be adjusted as well to accomodate that. I have also been trying to reduce the size of the users table as well. When I first started ... I dumped everything from mods that I was making/using into the users table ... as I'm sure most newbies do. Obviously I could start all over again from the beginning with another engine ... but with all that I have put into this one and my limited time ... I'll stick with this one.

Once I have gotten the hang of all of that ... my next step is to learn about security and injections. Now then ... won't that be fun. I've heard that the latest release from McCodes apparently takes care of that. My issue with that is that I have modified things so much that I doubt that I would be able to use the new patches.

[Arson]

- IP with 255 varchar is certainly too big. Yet you may want to keep a bit of space due to IP V6.

- Char / Varchar: you can mix both in the same table however if you do so you may actually not gain anything from the usage of char. I would personally use char only if the size of the field is constant. Which means, if I know I will ALWAYS use 2 char then I use char, otherwise I will always fallback to varchar. However it's up to you to choose your strategy. I doubt you will see all that much difference.

- Int / small int, yes sure you could gain some space by using the right type of data. But check exactly the size taken by your record / table and then think about the size of your disk and tell me if that will actually make a huge difference. Maybe not. So for something where you know for sure it will not go above a given limit then choose the right one, for the other leave a little margin doesn't harm.

Overall, the strategy used by McCodes 2 to store data is certainly not the best but changing completely the table structure will quiet certainly kill the possibility to use many mods out of the box. Maybe you should consider other engines before simply changing the table structure of McCodes as the issues don't stop there and you will have so much work to really clean it that you may actually gain more time from starting from scratch or as said use another engine.

For someone with an already established MCCodes game I think optimizing field types would be a good idea, at the very least in the `users` table.

While a few bits here and there might not be significant when you have a huge hard drive, you still have to think, the more memory it is taking up, the more milliseconds it is going to take to call or alter that data. When there are tens of thousands or even just thousands of rows in the database these sorts of small tweaks can really make all the difference.

I'm not sure about being better off starting from scratch or using a different engine, that really just comes down to what you want in your game.

Optimizing MCCodes field types isn't going to take you longer than coding your own engine from scratch >_>, it will just be a tedious and boring process.

[a_bertrand]

Sure tweaking the fields doesn't cost you a lot, but if you start to change the table structure / names and more, you will need to change as well all the queries which was accessing those data. At that point you may end up loosing a lot more time and efforts than starting with a clean code considering that maybe the old code is not all that great.

[Arson]

Sure tweaking the fields doesn't cost you a lot, but if you start to change the table structure / names and more, you will need to change as well all the queries which was accessing those data. At that point you may end up loosing a lot more time and efforts than starting with a clean code considering that maybe the old code is not all that great.

I see no reason to change table names or field names.

[Nickson]

names is not really the issue, whether it's named abc or xyz will not have any effect, the data type is one thing yes. But I believe that the whole database design is much more important. Sure you can optimize those 80 tables in the user table, but why not reduce those to 40? I know it's possible and rather easy to do, but it will require quite some work, that's the downside. But I think you'll get much more out of that, than going from int to smallint.

[newttster]

I agree. What you name your fields really doesn't matter as long as they make sense. The size of your tables and the data type can both certainly be optimized in such a way that there is a definite improvement in the time it takes to call or alter data. Yes, it certainly does take time and effort and a lot of cross checking to make sure that the various queries etc are now referenced properly, however, in the long run ... I think it will be worth the effort.

@SRB. I like your usage of the timestamp. I find it very interesting to learn new ways of calling data ... and in the long run eliminating useless fields. Thank you.

I think the best thing about forums like this is that you are always learning, whether you are someone brand new to php or an old hand at it.