The allowed maximum number of Columns and actual data in a row - What is it?

[Dave Crypto]

Hi There,

SUMMARY:
I need to know what the actual maximum date limit possible on a row of
a MYSQL database.
MORE DETAILS:

For example, does a MYSQL database only allow 4032 bytes of data to be
stored in one row or is there no limit? Does it only allow a maximum
of 50 colmums?

I tried googling on this, but all the results seem to be about
recommended maximums or design style. As an ex-Access developer I
remeber hitting a limit with the Access DB which I think was that one
row could contain 2048 bytes max(things like blobs do not contribute
to this limit). I'm hoping there are no limitations with MYSQL.

My reason for asking is that I have been asked to add new fields to
someone elses DB. There are already a lot of columns and the short and
dirty fix for me which would mean minimul PHP script changes is by
adding new fields to the current table. But there are already quite a
few so thought I would check.

TIA

Kind regards

D

[Bill Karwin]

Dave Crypto wrote:

I need to know what the actual maximum [data] limit possible on a row of
a MYSQL database.
I couldn't find a limit on a row size in a MyISAM table, but for InnoDB
tables:

http://dev.mysql.com/doc/mysql/en/in...trictions.html

"The maximum row length, except for BLOB and TEXT columns, is slightly
less than half of a database page. That is, the maximum row length is
about 8000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB,
and the total row length, including also BLOB and TEXT columns, must be
less than 4GB. InnoDB stores the first 512 bytes of a BLOB or TEXT
column in the row, and the rest into separate pages."

It sounds like the limit is on the total record length, not the number
of columns.
My reason for asking is that I have been asked to add new fields to
someone elses DB. There are already a lot of columns ...

How many is a lot?

Can you make a copy of the database and do some tests?

Regards,
Bill K.

[Gregory.Spencer]

Replay at bottom

I couldn't find a limit on a row size in a MyISAM table, but for InnoDB tables:

http://dev.mysql.com/doc/mysql/en/in...trictions.html

"The maximum row length, except for BLOB and TEXT columns, is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes. LONGBLOB and LONGTEXT columns must be less than 4GB, and the total row length, including also BLOB and TEXT columns, must be less than 4GB. InnoDB stores the first 512 bytes of a BLOB or TEXT
column in the row, and the rest into separate pages."
How many is a lot?

Thanks for that Bill. In this case the table is going to go from aboout
32 columns to 45. There was a lack of normalisation to the DB (e.g. it
has email1, email2 and email3 fields) when I adopted it and all the php
code is based around the current structure. Not wanting to change much
of the code I'm also just extending the same table.

However, when I total up all the varchars, chars, date fields etc. that
are possible in a single row it comes to less then 8000 so I think that
means I am safe.

Hypothetical Example. (And this is how it was in Access)

Lets says I have a DB with a 1000 byte row limit.
I am able to create a table with 10 varchars with 255 size.
However If I was to write a row that contained the first four field
containting the full 255 chars (Therefore 1020) I would get an error as
it is over my 8000 byte total.

The above is what happened in my Access DB and because I did not know
about it before hand it caused a bug that took time to resovle. Wanted
to avoid the same problem with MySQL.
Any more pointers welcome.

Kind regards

[Bill Karwin]

Gr*************@lycos.co.uk wrote:

How many is a lot?

Thanks for that Bill. In this case the table is going to go from aboout
32 columns to 45.

I thought you might have a table with hundreds of columns, and were
doubling it or something. I agree I think you're probably safe. But on
the other hand, I always test out *any* schema change in a development
environment before I make the change in a live system.

Regards,
Bill K.