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