Yeah, I think in the end what I will do is change a lot of the columns back to char from varchar. I was thinking this would save space making for a smaller faster DB, but the inherent overhead in a varchar field is not worth the space savings, which DL made crystal clear.
Thanks, Michael DL Neil wrote: >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 > --------------------------------------------------------------------- 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
