Bug #19327 Optimizer estimates columns with many NULL values wrong
Submitted: 25 Apr 2006 10:41 Modified: 18 Aug 2007 0:11
Reporter: Oli Sennhauser
Status: Verified
Category:Server: Optimizer Severity:S4 (Feature request)
Version:4.1 and 5.0 OS:Any (any)
Assigned to: Target Version:TBD
Triage: D5 (Feature request)

[25 Apr 2006 10:41] Oli Sennhauser 
Description:
Customer has a lot of tables with NULL values in it and he did a lot of outer joins...
In this situation the optimizer is estimating sometimes catastophicaly wrong.
So we have to change myisam_stats_method from nulls_unequal to null_equal
but this affects the whole system.

How to repeat:
problem is know but i will work out test cases...

Suggested fix:
Collect also the null values per indexed attribute so optimizer could better estimate.
[25 Apr 2006 14:11] Valeriy Kravchuk 
Sounds like a reasonable feature request for me. Do you agree?
[25 Apr 2006 14:56] Oli Sennhauser 
I do fully agree with you Valeriy!
[25 Apr 2006 15:23] Valeriy Kravchuk 
Optimizer should be changed to take that into account, some day.
[26 Apr 2006 18:10] Jochen Riehm 
This feature request (separate counting of NULL values) could be seen as an extension of
the myisam_stats_method 'ignore_null' of bug 9622.
[18 Aug 2007 0:11] Igor Babaev 
- This is a feature request (even was opened as such).
- I don't think it makes sense to implement this functionality before we implement
histograms.

By the above reasons I move the bug to 'To be fixed later'.
Product management will decide in what version a fix for this problem appears.