...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 ><[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
