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
