|
|
Posted by Brian on 06/03/05 05:22
Once again NC you come up with the answer i was looking for :)
"NC" <nc@iname.com> wrote in message
news:1117747076.938639.235370@g43g2000cwa.googlegroups.com...
> Brian wrote:
>>
>> Trying to work out how many records a table can hold safely
>> without causing a problem.
>
> This depends on three things: (1) the type of primary/unique
> keys you use, (2) file size limit of your operating system,
> and (3) the table type you use.
>
> The primary/unique key constraint is the easiest to understand.
> If you have a primary/unique key whose value can be between
> 1 and X, this key can take only X values; therefore, the
> table indexed with this key can only have X records. For
> example, if you have a table where the primary key is defined
> as SMALLINT UNSIGNED, you can only have 65,535 records in
> that table.
>
> Bounds for different integer types used in MySQL can be found
> here:
>
> http://dev.mysql.com/doc/mysql/en/numeric-types.html
>
> File size limits imposed by the operating system can also
> be of significance, depending on what type of table you
> use.
>
> A MyISAM table consists of three files, the data file,
> the index file, and the definition file. The data file
> is usually the largest, so the maximum amount of records
> your MyISAM table can hold can be found by dividing the
> operating system's file size limit by the number of bytes
> per record.
>
> An InnoDB table usually is not tied to any particular
> file (unless you use the innodb_file_per_table directive
> in my.cnf); MySQL can keep several tables in one file
> or create several files to hold a single table. So, in
> theory, the number of records in an InnoDB table is limited
> only by the type of the primary/unique key(s). If you do
> use innodb_file_per_table directive, however, MySQL creates
> one file per table, so the size of a table (including both
> data and indexes) cannot exceed the operating system's file
> size limit.
>
> Cheers,
> NC
>
Navigation:
[Reply to this message]
|