Michael: see also my PS comment Dobromir: Michael and I were joking between us, hence the silly comments appearing. I apologise if this did not communicate. IMHO the pragmatics of the exercise made it a waste of time/effort - even when I over-stated the savings at every opportunity! On a Friday afternoon a little speculation and humor is a good way to start the weekend!
You are 100% correct, the disk space occupied by a table is not the sum of the the length of its data-rows. For example, there is always space left for expansion/INSERTions. However in this case, because it is a R/O table, it could be squashed right down. I cannot comment if a table containing varchar/variable length fields can be compressed more or less than a table with only fixed length fields. Basically varchar allows one to potentially 'trade' disk space savings for a degradation in query response times. Some do not realise that by removing variable length fields to a 'companion table', any queries which access the (fixed-length) table without needing to look at the variable-length field(s), will speed up significantly. Thanks for providing some 'real' numbers. That was of interest. =dn ----- Original Message ----- From: "Dobromir Velev" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: 25 January 2002 16:11 Subject: RE: Tighly packed table > Hi, > If your column is of type VARCHAR, you want save much space (at least not as > much as DL Neil said). The specifications of tha varchar column type is that > it uses as much bytes as the data in it. Of course this will make your > indexes smaller (if this column is indexed). > A few days before I decided to optimize one of my tables (5 milion rows) and > altered a varchar(250) field to a varchar(100). > The size of the MYD data file changed with less than 1Mb so you see that > there was not much use of doing it. > > Dobromir Velev > Software Developer > http://www.websitepulse.com/ > > > -----Original Message----- > From: DL Neil [mailto:[EMAIL PROTECTED]] > Sent: Saturday, January 26, 2002 5:39 PM > To: Michael Stearne > Cc: Michael Stearne; Roger Karnouk; [EMAIL PROTECTED] > Subject: Re: Tighly packed table > > > Michael, > > Let's round it up to 3 million rows (I'm lazy at math too!) > Let's say you currently allow 15 bytes per name. > Let's say the longest name on file is 12 characters. > > The exercise would save 3 bytes/row multiplied by 3M rows, ie 9MB > (yes, let's ignore binary-decimal differences too) > > If you had two name fields (first- and family-name). > Woohoo that's a potential saving of 18MB > I'm also generous (to a fault) so round it up to 20MB. > > If you go out to buy a small PC HDD today, the smallest catalog product > might be 40GB > (let's assume they quote formatted capacity - they don't, but there am I > being half-full/-baked again) > > Thus we have the ability to save 0.0005% against total capacity of a new > drive. > Of course, the designer might have allowed way too much name-space (pun > hah!) or the table may have other > 'compressible' columns. > Let's go for a saving of 0.001% > > A new drive costs how much? > Your hourly rate is how much? > How long will the job take you? > How many cups of coffee is that? > Can the client carry the cost of all that coffee? > Won't your stomach rebel at the mistreatment? > > Mind you, most of the above is made up - I don't have any faults! > Time for me to go refill my glass (with healthy fruit juice)! > =dn > > PS after enjoying myself, let me point out that if the 'name' fields are > currently defined as variable length, > this exercise would allow you to make them fixed length. If you can 'wipe > out' all the variable width columns in > the table, performance will improve significantly! > > > > Hahaha. This is a static database. But you are right I don't know > > how much this will actually help. Hard disk isn't an issue. It was > > just an experiment...(that I have no time for anyway!) > > > > Thanks, > > Michael > > > > > > On Friday, January 25, 2002, at 06:19 PM, DL Neil wrote: > > > > > ...and because no one has been really cynical... > > > > > > After that query runs, then prepare for a coffee overload whilst you > > > perform the ALTER TABLE, then get ready > > > because if you shorten the field to (say) 12 characters/bytes the > > > very next day, someone with a 13 character > > > name is going to try to register! > > > > > > I'm wondering just how much space this 'little' exercise is going to > > > save, either as a ratio of the size of the > > > db, or as a ratio of HDD size? > > > > > > My glass is half-empty! > > > =dn > > > > > > > > > ----- Original Message ----- > > > From: "Michael Stearne" <[EMAIL PROTECTED]> > > > To: "Roger Karnouk" <[EMAIL PROTECTED]> > > > Cc: <[EMAIL PROTECTED]> > > > Sent: 24 January 2002 22:58 > > > Subject: Re: Tighly packed table > > > > > > > > >> The problem is, this query really hurts (I don't know if it finishes) > > >> for unindexed field for 2.9 million rows. But I'm sure it will finish > > >> eventually. > > >> > > >> Michael > > >> > > >> Roger Karnouk wrote: > > >> > > >>> select max(length(firstname)) from TableName; > > >>> > > >>> -----Original Message----- > > >>> From: Michael Stearne [mailto:[EMAIL PROTECTED]] > > >>> Sent: Thursday, January 24, 2002 4:38 PM > > >>> To: Christopher Thompson > > >>> Cc: [EMAIL PROTECTED] > > >>> Subject: Re: Tighly packed table > > >>> > > >>> > > >>> Christopher Thompson wrote: > > >>> > > >>>> At 04:10 PM 1/24/2002 -0500, Michael Stearne wrote: > > >>>> > > >>>>> We have a somewhat large read-only table (2.9 million recs). I am > > >>>>> wonder if there is a utility that will look at each row of each > > >>>>> columns and come up with a summary of the largest field (in > > >>>>> character > > >>>>> length) for each column. For example, scan each row's firstname > > >>>>> field and report that the longest first name is 12 characters. > > >>>>> That > > >>>>> way I can ALTER the firstname field to be a char or varchar of 12? > > >>>>> What would be better BTW? > > >>>>> > > >>>> > > >>>> I don't know if CHAR or VARCHAR is better for you but as to the > > >>>> query > > >>>> here, it would seem easiest to write a short program to query all > > >>>> the > > >>>> rows and programatically determine the longest column length. > > >>>> > > >>>> That said, you could probably set up a SQL statement for it. > > >>>> There's > > >>>> a LENGTH function in SQL, isn't there? The statement would look > > >>>> SIMILAR to the following: > > >>>> > > >>>> SELECT MAX(LENGTH(t1.FIRSTNAME)) AS fnamelength FROM TableFoo t1, > > >>>> TableFoo t2 WHERE LENGTH(t2.FIRSTNAME) <= fnamelength; > > >>>> > > >>> > > >>> Looks good to me, thanks. > > >>> > > >>> Michael > > >>> > > >>>> (Please note that my university SQL instructor pointed out that I > > >>>> wrote SQL statements backwards to anyone else he had ever > > >>>> taught. For > > >>>> that matter, I did Prolog backwards, too. :) > > >>>> > > >>>> > > >>>> > > >>>> --------------------------------------------------------------------- > > >>>> 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 > > >>>> > > >>> > > >>> > > >>> > > >>> > > >>> --------------------------------------------------------------------- > > >>> 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 > > >>> > > >>> > > >> > > >> > > >> > > >> > > >> --------------------------------------------------------------------- > > >> 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 <mysql-unsubscribe- > > >> [EMAIL PROTECTED]> > > >> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > >> > > >> > > > > > > > > > --------------------------------------------------------------------- > > > 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 <mysql-unsubscribe- > > > [EMAIL PROTECTED]> > > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > > > > --------------------------------------------------------------------- > > 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 > > > > > > > > --------------------------------------------------------------------- > 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 > > > > --------------------------------------------------------------------- > 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 > > --------------------------------------------------------------------- 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
