innodb_stats_nulls_unequal

This patch provide the new startup option to treat nulls unequal for statistics estimation of index keys. This is simplest implementation of the fix for http://bugs.mysql.com/bug.php?id=30423 .

• innodb_stats_nulls_unequal (default false) - if set true, when estimate cardinality of index keys, null values are treated as unequal values.

<example (same data to Bug#30423 if MySQL)>

[innodb_stats_nulls_unequal = false]

mysql> explain SELECT COUNT(*), 0 FROM orgs2 orgs LEFT JOIN sa_opportunities2 sa_opportunities ON orgs.org_id=sa_opportunities.org_id LEFT JOIN contacts2 contacts ON orgs.org_id=contacts.org_id;
+----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+-------+-------------+
| id | select_type | table            | type  | possible_keys   | key             | key_len | ref               | rows  | Extra       |
+----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+-------+-------------+
|  1 | SIMPLE      | orgs             | index | NULL            | orgs$org_id     | 4       | NULL              |   128 | Using index |
|  1 | SIMPLE      | sa_opportunities | ref   | sa_opp$org_id   | sa_opp$org_id   | 5       | test2.orgs.org_id |  5751 | Using index |
|  1 | SIMPLE      | contacts         | ref   | contacts$org_id | contacts$org_id | 5       | test2.orgs.org_id | 23756 | Using index |
+----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+-------+-------------+
3 rows in set (0.00 sec)

[innodb_stats_nulls_unequal = true]

mysql> explain SELECT COUNT(*), 0 FROM orgs2 orgs LEFT JOIN sa_opportunities2 sa_opportunities ON orgs.org_id=sa_opportunities.org_id LEFT JOIN contacts2 contacts ON orgs.org_id=contacts.org_id;
+----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+------+-------------+
| id | select_type | table            | type  | possible_keys   | key             | key_len | ref               | rows | Extra       |
+----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | orgs             | index | NULL            | orgs$org_id     | 4       | NULL              |  128 | Using index |
|  1 | SIMPLE      | sa_opportunities | ref   | sa_opp$org_id   | sa_opp$org_id   | 5       | test2.orgs.org_id |    1 | Using index |
|  1 | SIMPLE      | contacts         | ref   | contacts$org_id | contacts$org_id | 5       | test2.orgs.org_id |    1 | Using index |
+----+-------------+------------------+-------+-----------------+-----------------+---------+-------------------+------+-------------+
3 rows in set (0.00 sec)