innodb_stats_auto_recalc

Fun fact: disabling this will fuck up join optimizer. Since it doesn’t have the InnoDB stats, the optimizer can wrongly estimate the usage of certain indexes. I disabled this option when remaking a MariaDB 10.3 server. The problem is not that you don’t see anymore the InnoDB rows estimate, but same queries that worked fine (in same MariaDB) were taking forever now. EXPLAIN command showed on some queries (with JOIN) that PRIMARY keys were simply ignored.

Even if you re-enable the option and restart the mysql server, the problem persist, because InnoDB will not start update the stats for the existing unmodified tables. You need to issue a manual ANALYZE TABLE 😉

Leave a Comment