CODEWITHSUNDEEP
mysqlinnodbinformation-schemamysql-8.0
Oliver
Oliver

Reputation: 997

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

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.

Upvotes: 14

Views: 20464

Answers (3)

user4966711
user4966711

Reputation:

This thread is now 5 years old, but spencer7593's comments need to be addressed because this is still an issue.

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

SELECT MAX(ai_col) FROM myschema.mytable"

This is often used to get the last identity value inserted into a table. It is such a common query that it should be updated either in information_schema or in a global variable, eg @last_identity_insert. When doing multiple inserts into a table and related tables the last identity value is required for foreign keys. That's just one example.

Similarly, information_schema.table_rows: needs to be current. Selecting count(*) from a large table is a huge performance hit. Selecting a current value from information_schema is not.

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

ANALYZE should not even be necessary. When an insert into a table is done the db knows how many rows are affected. Should be a simple matter to update the table's row count.

This is an annoyance. Other major RDBMS venders do not have this issue.

Upvotes: 0

Rick James
Rick James

Reputation: 142453

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.

Upvotes: 4

spencer7593
spencer7593

Reputation: 108490

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.

Upvotes: 14

Related Questions