Bug #13762 SHOW CREATE TABLE slowdown with InnoDB
Submitted: 5 Oct 2005 7:37 Modified: 4 Dec 2007 21:17
Reporter: Hartmut Holzgraefe Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.14 OS:Linux (linux)
Assigned to: CPU Architecture:Any

[5 Oct 2005 7:37] Hartmut Holzgraefe
Description:
The execution time for SHOW CREATE TABLE on InnoDB tables rises over time when the table is being used, execution time seems to depend on table contents although it should depend on the table schema only

After creation of a test table SHOW CREATE TABLE returns right away (0.00s),
after inserting some 50.000 rows it already takes about 0.3s to complete.
After a TRUNCATE TABLE it even takes more than 3s to complete although
the table is now empty again.

How to repeat:
see attached files
[5 Oct 2005 14:42] Heikki Tuuri
Hartmut,

this probably is because SHOW CREATE TABLE refreshes table statistics, and does those 8 random dives in every index tree of the table.

Hmm... should we make SHOW CREATE TABLE not to refresh the statistics?

Regards,

Heikki
[5 Oct 2005 14:43] Heikki Tuuri
Hartmut,

TRUNCATE is internally performed as a DELETE FROM in 4.1.You have to wait for purge to clean up if you want the table to be physically empty.

Regards,

Heikki
[9 Nov 2005 20:35] Heikki Tuuri
Changing the category to a 'MySQL Server' bug, since the MySQL server should not ask InnoDB to update stats in SHOW CREATE TABLE.

Regards,

Heikki
[24 Nov 2005 18:54] Sergei Golubchik
Fixed in 5.0.17
[24 Nov 2005 18:56] Sergei Golubchik
Besides, server does not ask InnoDB to *update* stats, InnoDB is misusing HA_STATUS_TIME to update the statistics, while it only means to tell the time the table was updated for the last time, mtime basically.
[2 Dec 2005 20:35] Paul Dubois
Noted in 5.0.17 changelog.
[4 Dec 2007 21:17] Konstantin Osipov
Only S1 bugs are fixed in 4.1, fixed in 5.0