Analyse This -- A Database Optimization Procedure

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task.

Preface

The other day, I was involved in a question by wasifg on MySQLQuestions.com (a MySQL dedicated site offered by the owners of EE, so usernames and passwords from EE can be utilized there for any interested).  The gist of the question is to determine:

An auditing tool that compares the schema definition with the data present in a table (e.g., a column is defined as VARCHAR(255), but the maximum data value is only 100 characters in size).

My original suggestion, not having previously played with the procedure analyse extension, was to query the INFORMATION_SCHEMA to build a dynamic SQL query.  This gets the job done of course, but MySQL has a potentially better way, at least one already intended for this purpose; therefore, this Article will act as a resource for my old way and the new way discovered using procedure analyse.

[step=""] Analyze That
If you came here interested in Analyze Table syntax, then unfortunately that is not the scope of this particular article.  If you want to ensure that the most up-to-date key distribution information is stored for your table to aid in the performance of queries/joins, then please check this manual page: http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html.

analyze table bookings; 
                      

Select all Open in new window

[/step]

Okay!  Back to this.

Contents

So let's get started...

 

1. It's Easy…

Use Dynamic SQL.Procedure analyse_table is born:

delimiter $$
                      
                      drop procedure if exists `util`.`analyse_table`;
                      create procedure `util`.`analyse_table`(in schemaname varchar(255), 
                                                in tablename varchar(255))
                      begin
                      
                      -- Build dynamic SQL string
                      select 
                      group_concat(
                         concat("select '", table_name, "' as tbl,'", column_name, "' as col,'"
                              , data_type, "' as typ,", character_maximum_length
                              , " as len, max(char_length(`", column_name
                              , "`)) as len_data, max(length(`", column_name
                              , "`)) as len_bytes from `", table_schema, "`.`", table_name, "`")
                         separator ' union '
                      )
                      from information_schema.columns
                      where (schemaname is null or table_schema = schemaname)
                      and table_name = tablename
                      -- and data_type like '%char'
                      and not character_maximum_length is null
                      order by ordinal_position
                      into @sqlstring;
                      
                      -- If no table found, skip processing.
                      if (not @sqlstring is null) then
                         -- Execute dynamic SQL string.   
                         prepare s1 from @sqlstring;
                         execute s1;
                         deallocate prepare s1;
                      end if;
                      
                      end $$
                      
                      delimiter ;
                      

Select all Open in new window

How it works:


Example procedure call:

call util.analyse_table('test', 'bookings');
                      

Select all Open in new window

Example results:

Upon examination of the above, please note you would manually make recommendations based on analysis of the comparison(s) between len, len_data and len_bytes results.  Additionally, note that you would need different coding to handle data types like Set, which is highlighted, or even numerical values to determine if range of values could be better stored.

[step="" title=""] ««Recommendations Explored»»
Here are some samples of data associated and how you might recommend as an example.  Since I mentioned the Set based by_type, I will start with that and then show the VarChar bk_customer.

1. Distinct listing of actual "bk_type" values:

bk_type
                      ----------
                      Product
                      Service 
                      

Select all Open in new window

Since Sets are a special type, I won't get too deep into whether to use them or not -- I actually wasn't and converted this particular column as an example.  But if you do use them, hopefully you understand that the size of 15 that shows is because of the combination of the values in the set.  Moreover, since each individual selection within the set is 7 characters in length, it isn't really an issue in this case that our character/byte length is half that of the field's defined maximum length.  Your recommendation here would probably be to leave this "as-is" if you endorse Set data type in the first place.

2. Sample listing of actual "bk_customer" values:

bk_customer
                      ---------------
                      C543423
                      C1234318
                      C89
                      C101160
                      C1237
                      C123419
                      C1234595
                      C10119
                      C123275
                      C1234465 
                      

Select all Open in new window

Now, remember here our defined maximum is 10 characters; however, our highest actual data length is 8 like "C1234318".  Depending on your motives for the audit, you could certainly recommend VARCHAR(8) here.  

Adding our brains back into the equation, let's look at the variance in length a bit.  Since we are not using a fixed format like "C0000001", if you have a lot of customers or customer turnover like a job shop with no repeat business, you may opt instead to leave the extra 2 characters for future growth when you move past "C9999999".  This may intrigue you to amend the procedure above to send back minimum, standard deviation or other such additional statistical aggregates alongside our max lengths to quickly determine volatility or variability in data.
[/step]

There-in lies the con to this solution: manual recommendations … so is there a better way ?

2. Oh! It's Even Easier…

Use Procedure Analyse.The new and improved analyse_table procedure:

delimiter $$
                      
                      drop procedure if exists `util`.`analyse_table`;
                      create procedure `util`.`analyse_table`(in schemaname varchar(255), 
                                                in tablename varchar(255),
                                                in max_elems int,
                                                in max_mem int)
                      begin
                      
                      -- Build dynamic SQL string
                      select concat('select `'
                                  , group_concat(column_name separator '`,`')
                                  , '` from `', table_schema, '`.`', table_name
                                  , '` procedure analyse(',max_elems,',',max_mem,');')
                      from information_schema.columns
                      where (schemaname is null or table_schema = schemaname)
                      and table_name = tablename
                      -- and data_type like '%char'
                      -- and not character_maximum_length is null
                      order by ordinal_position
                      into @sqlstring;
                      
                      -- If no table found, skip processing.
                      if (not @sqlstring is null) then
                         -- Execute dynamic SQL string.   
                         prepare s1 from @sqlstring;
                         execute s1;
                         deallocate prepare s1;
                      end if;
                      
                      end $$
                      
                      delimiter ;
                      

Select all Open in new window

How it works:


Example procedure call:

call util.analyse_table('test', 'bookings', 1, 1);
                      

Select all Open in new window

Voila !

As you will see from examination of the new results, you don't have to manually derive recommendation of type as you get optimal field type suggestions like "CHAR(8) NOT NULL".  Additionally, data types such as Set, Int, et al are all handled appropriately without requiring additional code.

With that said, since procedure analyse works with the wildcard select syntax:

select *
                      from bookings
                      procedure analyse(1,1);
                      

Select all Open in new window

Our example procedure call is equivalent to the above in results; therefore, the real advantage to using the dynamic procedure call is the ability to analyze specific types of columns like character data only without having to know each column name.

Summary

In the above, we looked at a nice resource for database administrators in procedure analyse.  Please use it wisely, remembering not to get short-sighted.  For example, if your data only hold 8 character strings today, but may grow to 16 characters on future iterations of your application, the procedural code will not have this insight so as with anything else computer-related: don't check your brain at the door.  If you have the physical resources to sustain a slightly bigger size than currently needed, then it may be okay to do so, especially if the higher length doesn't require additional storage bytes.  Remember our example above where our business needs may warrant leaving flexibility for growth in data length because of the nature of the values and with VARCHAR the amount of storage required is based on the actual values and not the size specified; therefore, the added flexibility doesn't cost you anything extra today.

Therefore:

Thanks for reading!

Best regards and happy coding,

Kevin C. Cross, Sr. ( mwvisa1)

References

Data Types >  http://dev.mysql.com/doc/refman/5.1/en/data-types.html
Data Type Storage Requirements >  http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html
Information Schema Columns Table >  http://dev.mysql.com/doc/refman/5.1/en/columns-table.html
Prepared Statements >  http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html
Procedure Analyse >  http://dev.mysql.com/doc/refman/5.1/en/procedure-analyse.html
String Functions >  http://dev.mysql.com/doc/refman/5.1/en/string-functions.html


=-=-=-=-=-=-=-=-=-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=
If you found this article helpful, please click the Yes button after the question just below. This will give me (the author) a few points and might encourage me to write more articles.

If you didn't or otherwise feel the need to vote No, please first leave a comment to give me a chance to answer and perhaps to improve this article.

Thank you!
=-=-=-=-=-=-=-=-=-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=-=-=- =-=-=