Honesty Rocks! truth rules.

Space Needed For Database

HOME      >>       Programming

khalilov

Iam assuming the information in the databases i will create will be stored in the 500 MB space i get, but since 500MB isn't enough iam wondering if you guys can tell me how much bytes the following take: Varchar(x),Tinyint,Text,date,smallint,mediumint,bigint,float.... And the rest present when you add/edit a row in a table.Also what are the ranges of tinyint,smallint,mediumint and big int


faulty.lee

Iam assuming the information in the databases i will create will be stored in the 500 MB space i get, but since 500MB isn't enough iam wondering if you guys can tell me how much bytes the following take: Varchar(x),Tinyint,Text,date,smallint,mediumint,bigint,float.... And the rest present when you add/edit a row in a table.

 

Also what are the ranges of tinyint,smallint,mediumint and big int

You can refer to the MySQL documentation for that matter ->http://forums.xisto.com/no_longer_exists/

 

I would just show you a simple calculation, and 500MB is plenty of space to put your data.

 

Let's take a worse case scenario, say you have 20 columns to store your data, and all of them are varchar(255).

255 characters can store quite a lot of things. The sentence above is only 112 characters. So,

20columns x 255+1bytes = 5120bytes = 5kB per row.

5MB of space can give 1000 rows, and 500MB will give you 100'000 rows. That's quite a lot to store. In fact, you normally won't fill up as much in each columns, it could easily give you double or triple of that figure.

 

If you really need to store that much of data, I would actually recommend you to try paid database service, or paid hosting with database. They normally offer few GB of space for a very affordable annual fee.

 

Btw, I don't think the space used up by the database is shared with your disk space quota. If not mistaken I read it some where, and I saw the MySQL space allocation is diff compare to my disk space quota. That was few months back, I took a look just now(I seldom go to the cPanel), found that it's much diff now, so that rules might not applies anymore.


Quatrux

The given 500 MB for this free hosting is also used for the Databases, you can have ~500 MB of information in your SQL databases, as I remember earlier in older CPanel versions, the stats on left menu bar in CPanel showed how much space currently the Database is using, but I just tried to search for it, it doesn't show that anymore, so I guess phpMyAdmin can show it, if you really need to..


yordan

What will you do with your database ? Will you really have 500.000 rows in your table ?I think that if your site really has 500k visitors, you can negociate more space from Xisto without any problem.


TavoxPeru

Iam assuming the information in the databases i will create will be stored in the 500 MB space i get, but since 500MB isn't enough iam wondering if you guys can tell me how much bytes the following take: Varchar(x),Tinyint,Text,date,smallint,mediumint,bigint,float.... And the rest present when you add/edit a row in a table.

 

Also what are the ranges of tinyint,smallint,mediumint and big int

The url given by faulty.lee shows in detail all the data type storage requirements, but if you need it quickly here there are:

DATE: 3 bytes.

FLOAT: 4 bytes.

TINYINT: 1 byte.

SMALLINT: 2 bytes.

BIGINT: 8 bytes.

VARCHAR(x): x+1 bytes, usually 0 to 255 bytes.

TEXT: Maximum 64Kb.

Take into account that for the VARCHAR(x) and the TEXT data types this value depends if it is a binary string or a non-binary string -for the first one it represents the declared column lenght in bytes and for the second one in characters- and which character set you will use with them, because some character sets are multi-bytes like UTF-8 (up to three bytes per character) or UCS2 (up to two bytes per character).

 

BTW, i don't think that you can reach the 500MB limit unless you will use your database to store images or binary data like music or video.

 

Best regards,


khalilov

lol XDIn that case 500 megs will keep me happy for a long time :mellow:, thx XDEdit: Is there a limit to how many columns a table can have btw?


faulty.lee

A quick search in google return this result -> There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table.

 

You can read the detail here -> http://forums.xisto.com/no_longer_exists/

 

MySQL is is not a really a hobbyist's toys. It's ready for prime time production environment. So, for normal hobbyist requirement, i guess MySQL is much more than enough for layman like us, :mellow: hehe


yordan

A quick search in google return this result -> There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table.

The space effectively used depend on the number of rows, not the number of columns in a single row.

Jeigh1405241495

Yea it's like yordan said, all you can know from the design of a database table is the maximum and minimum size each entry, or table row, can have. For example if you are storing user information and you know the minimum size of each of 5 columns data types is 10 bytes, and the max is 100 bytes, you would effectively know each row is 50-500 byts of data. That said, the number of columns is only limited by the way in which you use the database so it could, in theory, increase to gigantic proportions regardless if there are 1 or 1000 fields in the table.


darasen

The 500 MB offered by Xisto should be more than sufficient. If your database requires more than that chances are that your site is doing well enough to purchase space. If you are even approaching the the column limit on a table the chances are that your data model needs some serious work.


Atomic0

IF your database space requirement exceeds what is provided by Xisto, it would be best if you invest in paid hosting since your site would be able to generate income enough to support itself in terms of hosting and other costs. Also, you could use a alternative host that provide more space to host your SQL databases (I suppose it is possible to split up the table into two tables with data being retrieved from two different databases on different hosts).