Filip Sergeys wrote: > Two questions I did not find straight answers for: > > Is there an easy way to retrieve/query the size of a table in bytes or > blocks > > Does adding columns to a table, when it already contains a few millions > records, have a negative performance impact. And how much worse does it > get if you add yet another column thereafter (and another and ..., > alternate solutions ?)
Columns added using ALTER TABLE ADD are not stored in the existing rows so far --> the command is very fast and the size of the stored data does not change. If columns receive another value than NULL (or the n-th added column receives a value <> NULL) then the changed column and all columns which were added before that column are included / updated to the old row --> making the row longer than before. New records include all columns known at that time --> Create table (2 columns) Insert --> two columns inserted Alter table add 1 column Insert --> three columns inserted Alter table add 1 column Insert --> four columns inserted ==> different rows are inserted. Selecting is no problem as 'missing' (not stored) columns are assumed to be NULL. --> because longer rows need more storage, need more I/O for the same amount of rows, performance for scanning the whole table will decrease deppending on the rowlength/number of columns really stored. It does not matter if all alter table add are done at once or one after the other. Elke SAP Labs Berlin > (No, this not bad analysis, we just don't know now what additional > information we will have to store in the future). > > Thanx > > Filip > > > > > -- > *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* > * System Engineer, Verzekeringen NV * > * www.verzekeringen.be * > * Oostkaai 23 B-2170 Merksem * > * 03/6416673 - 0477/340942 * > *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* > > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
