Calculating Optimal Datatypes Should Take Into Account Indexes

[xpose]

I was testing out the ability to calculate optimal datatypes and noticed that it doesn't seem to look at index values. For example if field “last” is varchar(25) and is indexd with no length specified, then suggesting to change it to tinytext will cause an error because it doesn't specify a key length for the index:

Code:

BLOB/TEXT column 'last' used in key specification without a key length.

It can be easily fixed by simply specifying a length in the index, but I am wondering if it can be taken a step further in future releases?

[Rohit]

That is a good suggestion and we will discuss this internally. Will update this thread within a week.

[peterlaursen]

We need clarification here!

1) It is true that an index defined on any BLOB/TEXT types requires a length specification

2) Also an index defined on 'long varchars' do as max index length in MySQL is 767-1000 bytes dependent on storage engine

But we do not understand how the problem described as “For example if field “last” is varchar(25) and is indexd with no length specified, then suggesting to change it to tinytext” can occur.  We have never seen (and we tried quite a lot now) that a varchar was proposed changed to BLOB/TEXT.  Also this optimizer function will always propose shorter and never longer datatypes for varchars.  

So please detail with one or more example what exactly you have experienced.

[xpose]

'peterlaursen' wrote on '07:

We need clarification here!

1) It is true that an index defined on any BLOB/TEXT types requires a length specification

2) Also an index defined on 'long varchars' do as max index length in MySQL is 767-1000 bytes dependent on storage engine

But we do not understand how the problem described as “For example if field “last” is varchar(25) and is indexd with no length specified, then suggesting to change it to tinytext” can occur.  We have never seen (and we tried quite a lot now) that a varchar was proposed changed to BLOB/TEXT.  Also this optimizer function will always propose shorter and never longer datatypes for varchars.  

So please detail with one or more example what exactly you have experienced.

My apologies peter. The original field length of “last” was 150, NOT 25.

Code:

lastvarchar(150) NULL

It then recommended to have it changed to TINYTEXT.

Code:

TINYTEXT NOT NULL

Let me know if that clears it up, so it can be reproduced.

[nithin]

Thanks for the info.

We will try this case to reproduce it.

btw: I have added this request to our issue tracker.

http://code.google.com/p/sqlyog/issues/detail?id=1353

We will schedule it after we analyze it in detail.

[Author]

Posts