MySQL not updating information_schema, unless I manually run ANALYZE TABLE `myTable`

Question

I have the need to get last id (primary key) of a table (InnoDB), and to do so I perform the following query:

SELECT (SELECT `AUTO_INCREMENT` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = 'mySchema' AND `TABLE_NAME` = 'myTable') - 1;

which returns the wrong AUTO_INCREMENT. The problem is the TABLES table of information_schema is not updated with the current value, unless I run the following query:

ANALYZE TABLE `myTable`;

Why doesn't MySQL update information_schema automatically, and how could I fix this behavior?
Running MySQL Server 8.0.13 X64.

Answer 1

Q: Why doesn't MySQL update information_schema automatically, and how could I fix this behavior?

A: InnoDB holds the auto_increment value in memory, and doesn't persist that to disk.

Behavior of metadata queries (e.g. SHOW TABLE STATUS) is influenced by setting of innodb_stats_on_metadata and innodb_stats_persistent variables.

https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata

Forcing an ANALYZE everytime we query metadata can be a drain on performance.

Other than the settings of those variables, or forcing statistics to be collected by manually executing the ANALYZE TABLE, I don't think there's a "fix" for the issue.

(I think that mostly because I don't think it's a problem that needs to be fixed.)

---

To get the highest value of an auto_increment column in a table, the normative pattern is:

 SELECT MAX(`ai_col`) FROM `myschema`.`mytable`

---

What puzzles me is why we need to retrieve this particular piece of information. What are we going to use it for?

Certainly, we aren't going to use that in application code to determine a value that was assigned to a row we just inserted. There's no guarantee that the highest value isn't from a row that was inserted by some other session. And we have LAST_INSERT_ID() mechanism to retrieve the value of a row our session just inserted.

If we go with the ANALYZE TABLE to refresh statistics, there's still a small some time between that and a subsequent SELECT... another session could slip in another INSERT so that the value we get from the gather stats could be "out of date" by the time we retrieve it.

Answer 2

SELECT * FROM tbl ORDER BY insert_datetime DESC LIMIT 1;

will get you all the data from, the "latest" inserted row. No need to deal with AUTO_INCREMENT, no need to use subqueries, no ANALYZE, no information_schema, no extra fetch once you have the id, no etc, etc.

Yes, you do need an index on the column that you use to determine what is "latest". Yes, id could be used, but it should not be. AUTO_INCREMENT values are guaranteed to be unique, but nothing else.