Wow, I feel like I wasted time just asking! :-) But my goal was not to save disk space it was to optimize the queries on the table as this is a test project for mySQL/OS X versus Unify/SCO or Unify/Linux or mySQL/Linux.
I am kind of partial to the mySQL/OS X combo, so I wanted it to work as optimized as possible considering it is a 500Mhz iMac G3 going against a DP PII 850 Linux box. Like I said the results are very promising. Thanks, Michael DL Neil wrote: >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
