disable column type to ENUM suggestion

[mia0x75]

When I try to run the following cmd
perl mysqltuner.pl --buffers --dbstat --idxstat --sysstat --pfstat

I get lots of data type to ENUM suggestions, and I think most of them meaningless. Such as

ENUM('0.00','100.00','327.00','500.00','600.00','900.00','1200.00','1500.00','15000.00')

[mia0x75]

how about remove mysql_tables() ?

[streaky]

I partly disagree with this, in some cases it's worth considering, the key point here is considering; I've seen some where I've thought "you might be right actually". I actually came to report the same but slightly different issue - it's okay to suggest this because sometimes it makes sense, the problem is it stops being useful if it's suggesting 15000 different ENUM() values. There should be a limit - if it tries to suggest more than x (lets say 10 for argument sake then it just shouldn't suggest it). There should be a default setting of what's reasonable and it should be configurable and whatever is greater than that shouldn't be included.

Under any circumstance this just shouldn't be a thing - there's no logic to it and the performance won't be improved to any degree that you could measure it in wall time - and arguably it could be made worse in many circumstances. I'll try to PR a solution for that if I get time but... yeah.

The other point which brings my point closer to yours is it might be worth skipping for numeric columns or rather at least INT()s - sure it might make sense in logic but there's probably no performance advantage to it, beyond reducing data stored (very) slightly.

[mia0x75]

I think we should use INT rather than ENUM in our database design, because it is not easy to add new value(s). But anyway, at least, we should add an option to disable this kind of check, or disabled by default.

[streaky]

Sure, it's obviously massively dependant on circumstance. IMHO every single suggestion mysqltuner makes should be considered carefully and not blindly done.

I've been poking around this issue and I've found a couple of things.

• The tuner uses internal MySQL functionality
• The settings for that function that tuner sets appear to be way too high, it is set by default at 256 elements which even that seems way too high to me, tuner uses a 10,000 element setting. IMHO this is just wrong and why it produces fairly trash suggestions
• The function is deprecated from 5.7.18, and removed in 8.0. It isn't long for this world.
• Even if you reduce the number of max elements (say to 10) it sometimes bugs and ignores the max element setting anyway (actually I'm fairly sure it always bugs and uses 256 the default if you set it lower), thus is arguably unfixable - but it does reduce the noise

Really it probably needs some sort of custom column data type handler that can make better suggestions. Some people have done some work on creating an alternative that's arguably in some ways more useful that could potentially be ported into tuner and possibly expanded upon, replacing PROCEDURE ANALYSE with something more useful.

Just some thoughts and things I've found.

[jmrenouard]

Hi,

Advices for ENUM is coming from server himself with PROCEDURE ANALYSE.

SELECT $_ FROM $dbname.$tbname PROCEDURE ANALYSE(100000)

[jmrenouard]

Hi @streaky Hi @mia0x75

Want do you want me to do on this feature ?

• Remove it
• Silent it by default
• Reduce sample analysis
• Remove all ENUM preconisations
• Disabling it by default on MySQL 8.

Thanks for your feedback

[leewin12]

Hi @jmrenouard

If you don't mind, I would like to suggest Remove it,Remove all ENUM preconisations, or option to disable it

Thank you for your effort on awesome tool.

[streaky]

Hm, didn't see this notification when the original reply was, weird.

Certainly in 8.0 it's removed so it needs disabling for 8.0.

Secondly using 100000 for max elements seems completely wrong. The default is 256 and that's more sane but there's an argument that says it should be 10 or 20, something in that area. Personally I'd say - and this is a personal thing - that it's only really useful on a table with a decent number of rows. You can only draw reasonable conclusions about this stuff automatically if you have enough data and not too many elements. If you want to make it awesome maybe (these values would take some tweaking, just an initial thought) say minimum of 50 rows and set max_elements at say 20% of the number of rows or a 1000 depending on which is smaller. That way you'd kind of force it to give reasonable answers. You'd still have to have a think about if that's right and it'd give you false negatives in some cases but those false negatives I'd argue would be out of scope anyway.

[jmrenouard]

REmoving check for mysql 8.0 and percona 8.0.
Procedure anlyse() has be removed since version 8.0 on both distributions.