InnoDB and MyISAM Index Statistics Collection and  S  is the average value group size. That ratio yields an approximate number of value groups in the table. For a join based on the  <=>  comparison operator,  NULL  is not treated differently from any other value: NULL <=> NULL , just as  N  <=>  N  for any other  N . However, for a join based on the  =  operator,  NULL  is different from non- NULL  values:  expr1  =  expr2  is not true when  expr1  or  expr2  (or both) are  NULL . This affects  ref  accesses for comparisons of the form tbl_name.key  =  expr : MySQL does not access the table if the current value of  expr  is  NULL , because the comparison cannot be true. For  =  comparisons, it does not matter how many  NULL  values are in the table. For optimization purposes, the relevant value is the average size of the non- NULL  value groups. However, MySQL does not currently enable that average size to be collected or used. For  InnoDB  and  MyISAM  tables, you have some control over collection of table statistics by means of the innodb_stats_method  and  myisam_stats_method  system variables, respectively. These variables have three possible values, which differ as follows: If the  NULL  value group size is much higher than the average non- NULL  value group size, this method skews the average value group size upward. This makes index appear to the optimizer to be less useful than it really is for joins that look for non- NULL  values. Consequently, the  nulls_equal  method may cause the optimizer not to use the index for  ref  accesses when it should. If you have many  NULL  values, this method skews the average value group size downward. If the average non- NULL  value group size is large, counting  NULL  values each as a group of size 1 causes the optimizer to overestimate the value of the index for joins that look for non- NULL  values. Consequently, the  nulls_unequal  method may cause the optimizer to use this index for  ref  lookups when other methods may be better. If you tend to use many joins that use  <=>  rather than  = ,  NULL  values are not special in comparisons and one  NULL  is equal to another. In this case,  nulls_equal  is the appropriate statistics method. The  innodb_stats_method  system variable has a global value; the  myisam_stats_method  system variable has both global and session values. Setting the global value affects statistics collection for tables from the corresponding storage engine. Setting the session value affects statistics collection only for the current client connection. This means that you can force a table’s statistics to be regenerated with a given method without affecting other clients by setting the session value of  myisam_stats_method . To regenerate  MyISAM  table statistics, you can use any of the following methods: Some caveats regarding the use of  innodb_stats_method  and  myisam_stats_method : •You can force table statistics to be collected explicitly, as just described. However, MySQL may also collect statistics automatically. For example, if during the course of executing statements for a table, •Change the table to cause its statistics to go out of date (for example, insert a row and then delete it), and then set  myisam_stats_method  and issue an  ANALYZE TABLE  statement •Execute  myisamchk --stats_method= method_name  --analyze •When the variable is set to  nulls_ignored ,  NULL  values are ignored. •When the variable is set to  nulls_unequal ,  NULL  values are not considered the same. Instead, each NULL  value forms a separate value group of size 1. •When the variable is set to  nulls_equal , all  NULL  values are treated as identical (that is, they all form a single value group). 1721