Hi.
First, there is somewhere a chapter about disk usage in the manual
(too lazy to look it up).
On Fri, Feb 09, 2001 at 09:51:03AM -0500, [EMAIL PROTECTED] wrote:
> I was wondering if someone could help advise me on calculating
> database size estimations. Assume the following table:
>
> Field Type Null Default
> type enum('user','group') No user
> id smallint(6) No 0
> action smallint(6) No 0
> flag enum('Y','N') No N
>
> Keyname Unique Field
> PRIMARY Yes id
> PRIMARY Yes action
> PRIMARY Yes type
>
> Say I want to size this table for 20,000 entries, I would estimate
> that the data would consume (5 * 20,000 + 2 * 20,000 + 2 * 20,000 +
> 1 * 20,000) = 200,000 bytes. Right?
Almost. ENUM fields are not saved as char, but as bit fields, which
are saved as whole bytes (see the ENUM description). Additionally you
have a little overhead to each row (e.g. for NULL value flags, if
appropriate [not in your case] and other). So you have
(head+1+2+2+1) * 20000 ~= 130KB
> But how do I estimate the amount of disk space the indexes (keys)
> will consume? The table is MyISAM.
It is quite easy to calculate the maximum: Each field content and
additionally a pointer to the data (depends on your table type and
size, but in your case probably 4 bytes long):
(2+2+1+4)*20000 ~= 180KB
In practice, it is normally smaller, because MySQL tries to pack the
content if values are repeating.
Well, the above is only a rough estimation. As always you get the best
results when you just try and build a sample database and fill it with
random data.
Bye,
Benjamin.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
