[SupremeCommander]

Hello, I've got a few questions regarding MySQL and Pawn;


Let's say I have a table with 100 columns. If all of them had default value of 0 rather than null would it decrease performance when we insert rows into it? If I try to fetch null data what would I get in PAWN? 0?

Also should you leave new data inputs null?

Thank you.

[Vince]

Quote:

Originally Posted by SupremeCommander 100 columns

I stopped reading right there. If you have this many columns in a single table then that's usually a sign that your database isn't properly normalized, or not normalized at all. Normalization means restructuring the database to avoid duplicate data and empty values.

Please provide the list of columns so I can help you normalize your database.

PHP код:

SHOW CREATE TABLE tableName 


Then expand options and enable fulltext.

I wish NULL values would be properly supported by SA-MP, but unfortunately they are fetched as if they were a string, which is problematic if you have a NULL value in a non-string field.

[SupremeCommander]

I don't have 100 columns, in fact I have a total of 27 columns for my player data. I was just creating a scenario.... What I wanted to know whether it would affect the performance or not. And should I use default value(0) for every integer column?

[PowerPC603]

Any field could have a default value of 0, or an empty string for text columns.
That way, you don't have to supply those zero's every time you want to create a new entry.
Supplying more data than required will just overwrite the default values set in your database structure.

Having that many columns (100) would add a slight performance loss I guess, but I don't think it will make a huge impact.
If you're worried about your script's performance, use threaded queries and let MySQL work in the background, while your script continues executing the rest of your script.

[SupremeCommander]

You guys misunderstood me. My question was not about having as many columns, but about giving every column(integer and float) a default value of 0. Would it affect performance? Should you do it for PAWN? and if there is performance difference, is it negligible?

But anyways thanks a lot for all replies!

[PowerPC603]

Quote:

Originally Posted by SupremeCommander You guys misunderstood me. My question was not about having as many columns, but about giving every column(integer and float) a default value of 0. Would it affect performance? Should you do it for PAWN? and if there is performance difference, is it negligible? But anyways thanks a lot for all replies!

As ****** already said, sites with billions of users use MySQL and have no performance issues.
A small SAMP server (even if your run a server with 1000 players) won't even make a small dent in MySQL.

Having 100 fields with a default value of 0, which are automatically set when you create a new account for example, is about the same as having no default value and sending a huge query to set them all to 0.
I'd say this huge query makes more impact because you're sending a long string of text to MySQL to interpret.
Interpreting a string takes alot longer than a small string.

Sending a small query to MySQL and let it set all default values to 0 automatically would be better.
Less overhead of communications between 2 programs, less text to interpret by MySQL would result in better performance.

After all, PAWN doesn't handle NULL values properly as I read somewhere.
Setting default values automatically gets rid of that problem.

Imagine you create a new account (when a new player logs in) and you send a huge query to MySQL, setting everything to 0 because you didn't set default values (for money, score, adminleven, jobs done, ...) in the tables of MySQL.
And you edit the script afterwards to include a new field.
You would have to edit that huge query again, perhaps splitting it up even further, to prevent errors about "line too long".
You could make mistakes during the rewrite of your query.

Having default values gets rid of that.
You still create the new account the same way you did before (insert a new account with only the playername and password).
The new field automatically gets set to 0 by MySQL itself.
No need to edit that huge query, with possible mistakes.

All you need to do is create a new, small function to update that new field whenever the data on your server changes and you're done.

For myself, I'm setting all data-fields to default value 0, besides ID, playername and password because those NEED to be inserted for a new account.
Even if you want a new player to get some default money, you could add it to the query easily and MySQL won't use that default value because it's overridden by your query.