List:General Discussion« Previous MessageNext Message »
From:Michael Stearne Date:January 25 2002 5:28pm
Subject:Re: Tighly packed table
View as plain text  
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" <diadomraz@stripped>
>To: <mysql@stripped>
>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:PHPml@stripped]
>>Sent: Saturday, January 26, 2002 5:39 PM
>>To: Michael Stearne
>>Cc: Michael Stearne; Roger Karnouk; mysql@stripped
>>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" <mstearne@stripped>
>>>>To: "Roger Karnouk" <roger@stripped>
>>>>Cc: <mysql@stripped>
>>>>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:mstearne@stripped]
>>>>>>Sent: Thursday, January 24, 2002 4:38 PM
>>>>>>To: Christopher Thompson
>>>>>>Cc: mysql@stripped
>>>>>>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
> <mysql-thread97360@stripped>
>>>>>>>To unsubscribe, e-mail
>>>>>>><mysql-unsubscribe-mstearne=mac.com@stripped>
>>>>>>>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
> <mysql-thread97363@stripped>
>>>>>>To unsubscribe, e-mail
>>>>>><mysql-unsubscribe-roger=mediahouse.com@stripped>
>>>>>>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
> <mysql-thread97371@stripped>
>>>>>To unsubscribe, e-mail <mysql-unsubscribe-
>>>>>PHPml=dande.homechoice.co.uk@stripped>
>>>>>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 <mysql-thread97377@stripped>
>>>>To unsubscribe, e-mail <mysql-unsubscribe-
>>>>mstearne=mac.com@stripped>
>>>>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 <mysql-thread97388@stripped>
>>>To unsubscribe, e-mail
>>>
>><mysql-unsubscribe-PHPml=dande.homechoice.co.uk@stripped>
>>
>>>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 <mysql-thread97453@stripped>
>>To unsubscribe, e-mail
>><mysql-unsubscribe-diadomraz=rambler.bg@stripped>
>>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 <mysql-thread97458@stripped>
>>To unsubscribe, e-mail
> <mysql-unsubscribe-PHPml=dande.homechoice.co.uk@stripped>
>>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 <mysql-thread97472@stripped>
>To unsubscribe, e-mail <mysql-unsubscribe-mstearne=mac.com@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>



Thread
Tighly packed tableMichael Stearne24 Jan
  • Re: Tighly packed tableChristopher Thompson24 Jan
    • Re: Tighly packed tableMichael Stearne24 Jan
RE: Tighly packed tableRoger Karnouk24 Jan
  • RE: Tighly packed tableChristopher Thompson24 Jan
  • Re: Tighly packed tableMichael Stearne24 Jan
    • Re: Tighly packed tableChristopher Thompson24 Jan
  • Re: Tighly packed tableMichael Stearne25 Jan
  • Re: Tighly packed tableDL Neil25 Jan
    • Re: Tighly packed tableMichael Stearne25 Jan
      • Re: Tighly packed tableDL Neil25 Jan
        • RE: Tighly packed tableDobromir Velev25 Jan
          • Re: Tighly packed tableDL Neil25 Jan
          • Re: Tighly packed tableMichael Stearne25 Jan
      • Re: Tighly packed tableMichael Stearne25 Jan
Re: Tighly packed tableEgor Egorov25 Jan