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
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 4.1 and 5.0
• OS: Any (any)
• 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.