Histograms were introduced with MySQL 8.0 and are a valuable way of speeding up queries. The MySQL optimizer assumes that data in a column has evenly distributed values. Even distribution of data probably does not reflect much of the data sitting right now in your database.
The optimizer wants to find the most efficient way to return the data requested in a query. If it has poor information on that data, then the optimizer will make a 'guesstimate' that will will result in a query plan that will not perform well. But if the optimizer has good information, in this case provided by a histogram, then it can produce a better query plan.
In the following example a able is filled with data that is not evenly distributed. In the histogram image following, the data is represented in what looks like a rollercoaster side view.
x int unsigned not null);
insert into dist (x) value (1),(1),(1),(1),(1),
| x | count(x) |
| 1 | 5 |
| 2 | 1 |
| 3 | 6 |
| 4 | 2 |
| 5 | 1 |
| 6 | 7 |
| 8 | 1 |
| 9 | 4 |
There are 22 values of x that have a value less than seven. If we examine output of a query where we are looking for the those values, the optimizer estimates, as seen in the EXLAIN output below, it will need to roughly a third of the 27or 9 rows in the table. Here the optimizer has made a guess from assuming an even distribution, a third of 27 is 9. It is easy to see that 9 is no where close to 22.
Imagine a contractor estimates that it will take $9 to make you a widget but the final bill is $22. Or your GPS application in your phone informs you that you are nine blocks from your destination but in reality is a much longer 22 blocks away. In these two cases there may be valid reasons for the cost and distance 'overruns' but they are still frustrating to have to come up with the extra money of walk the extra distance. Likewise this query generates a poorly performing query plan.
*************************** 1. row ***************************
Extra: Using where
1 row in set, 1 warning (0.0007 sec)
Note (code 1003): /* select#1 */ select `fk`.`dist`.`x` AS `x`,count(`fk`.`dist`.`x`) AS `count(x)` from `fk`.`dist` where (`fk`.`dist`.`x` < 7)