Bug #19327 Optimizer estimates columns with many NULL values wrong
Submitted: 25 Apr 2006 8:41 Modified: 17 Aug 2007 22:11
Reporter: Oli Sennhauser Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:4.1 and 5.0 OS:Any (any)
Assigned to: CPU Architecture:Any

[25 Apr 2006 8: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 12:11] Valeriy Kravchuk
Sounds like a reasonable feature request for me. Do you agree?
[25 Apr 2006 12:56] Oli Sennhauser
I do fully agree with you Valeriy!
[25 Apr 2006 13:23] Valeriy Kravchuk
Optimizer should be changed to take that into account, some day.
[26 Apr 2006 16: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.
[17 Aug 2007 22: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.