Schema Optimiser

The Schema Optimizer feature analyses what data are stored in the table and proposes schema changes that will optimize the table (column types and column lengths) to fit existing data. Technically, this feature is based on the server implementation of “Procedure Analyse()”.

Having wrong and too long datatypes can reduce server operations on the table as more memory will be used. Also, for columns on which indexes are defined reducing datatype length will reduce the size of the index. Depending on storage engine and how data are looked up by the Engine that can result in significant performance improvement. This can be achieved by clicking on ‘Calculate Optimal Datatype’ link in HTML mode.

In particular it is a common misunderstanding that length of varchars does not matter much – but that is only true for storage on disk and not when data are loaded into memory. A varchar(20) occupies 20 bytes in memory and a varchar(100) occupies 100 bytes.

However you should never use this feature uncritically for two reason:

• Neither the server nor SQLyog can know what data may be inserted to the table in the future. So, if SQLyog proposes you a varchar(37) that only means that this is the most efficient datatype for data currently stored. The server admin or the application developer will have to take the decision.
• We have disabled the ability of ‘Procedure Analyse()’ to propose ENUM types. There is a bug in “Procedure Analyse()” that causes inconsistent and wrong results in all server versions which are currently released. If you want to check if an ENUM type is appropriate for a particular column you may supplement this feature by executing SELECT COUNT(DISTINCT column) for the column.

Note: This feature is available in SQLyog Ultimate.