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
