MYSQL - Find Proper Field Width ?

So I received a file from a partner without a correct layout with correct field widths. I thought there was a query or a tool that would return a  report showing the optimal field widths for the data within the fields in the table.

I use Navicat for my client tool.
Joel BuhrPresidentAsked:
Who is Participating?
 
Joel BuhrPresidentAuthor Commented:
This actually gives me what I was looking for.

SELECT *
FROM `table_name`
PROCEDURE ANALYSE ( )

Open in new window

0
 
gheistCommented:
varchar should fit your purpose just fine.
0
 
Joel BuhrPresidentAuthor Commented:
Gheist  thanks for the reply. But that does not resolve my question.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Steve BinkCommented:
Perhaps if you were a little more clear with your question, you would receive more relevant help...?
0
 
Joel BuhrPresidentAuthor Commented:
I thought there was a way, or a tool to analyze a table which would recommend proper field lengths.

So if all fields are currently 100 char long.

Before:
Name   100
ADDR   100
City       100
ST          100

After - Recommendation
Name   30
Addr     25
City       20
St           2

Make sense?
0
 
gheistCommented:
selelect max(length(Name)) from Before;
0
 
Joel BuhrPresidentAuthor Commented:
gheist I am trying to find out what "IS" the correct field length for the data in that field. I was sure there was a tool or query that would look at the data in the fields and return a VALUE of the widest or longest value in the field.
0
 
gheistCommented:
Maximum field length kind of fits all shorter values.
0
 
Steve BinkCommented:
More to the point, you don't need to worry about this if you are using a VARCHAR field.  While a CHAR field reserves the maximum amount of space as dictated by its length, a VARCHAR field only reserves what is necessary to store the current value.  See here for the storage requirement differences: https://dev.mysql.com/doc/refman/5.6/en/char.html.

When using a VARCHAR field, you only need to set the *maximum* length you will need.
0
 
Joel BuhrPresidentAuthor Commented:
Steve thank you for your reply. I am trying to find the correct length and type that a data field should be and thought there was a way.
0
 
Steve BinkCommented:
Well, there is a way - just look at a sample of your data.  

MySQL has a good number of data types to choose from, and selecting the correct one can impact your application's performance.  Here's a quick, dirty, by no means complete flowchart for you.MySQL-Data-Types.pdf
0
 
gheistCommented:
length applies to either type... char is aligned, and a bit more efficient.
0
 
Joel BuhrPresidentAuthor Commented:
Thank you for the feedback. Although that PDF is helpful it is not getting to the heart of my question.  Let me do a bit more research on my side.
0
 
gheistCommented:
Looking forward to hear your feedback....
0
 
Joel BuhrPresidentAuthor Commented:
Something like this but that would do it on all columns.

SELECT MAX(LENGTH(field_to_query)) FROM table_to_query;

Open in new window

0
 
gheistCommented:
Yes, as said in:
http:#a40777355
0
 
Joel BuhrPresidentAuthor Commented:
But how do you do that on all fields at one time? I have over 200 fields in the database.
0
 
gheistCommented:
show columns;
0
 
Joel BuhrPresidentAuthor Commented:
Gheist, I don't understand your response. Are you wanting a list of columns?
0
 
gheistCommented:
that statement shows list of fields (columns in MySQL terms)
0
 
Joel BuhrPresidentAuthor Commented:
How would that be incorporated in the query?
0
 
Steve BinkCommented:
You would have to make the query.  If I were doing this, I would use the "SHOW COLUMNS" query, and copy the result into my text editor.  There, I could manipulate the return to create the MAX(LENGTH(fieldname)) for each field, then paste that manipulated text into a query on that table.

Then you move on to the next table.

And yes, this is painful, tedious work.  That's why every DBA tutorial stresses data analysis before design - so you only have to do the designing part once.  Making these types of changes after the fact on a production system is just not a simple thing, not to mention it can play havoc with your data integrity if you screw something up.

Of course, you COULD write an SP to go table by table, generating the MAX(LEN...)) query for each one, running it, and saving the results somewhere.  If you have hundreds of fields spread across dozens of tables, that might even save you time.
0
 
Joel BuhrPresidentAuthor Commented:
The query and suggestions by Steve provided me with what I was looking for.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.