SHOW TABLE STATUS very slow on InnoDB

Recently we've been migrating from MyISAM to InnoDB and I understand that MyISAM uses meta information against each table to track information and such - however, the database is about 1.8gb with 1+ million records across 400+ or so tables.

The problem comes with software like PHPMyAdmin runs "SHOW TABLE STATUS FROM dbase;" where it can take up to 50 seconds to execute that command. Is there a way to optimise this? (MySQL or MariaDB)

Thanks!

Here is a quick-and-dirty solution:

set global innodb_stats_on_metadata = 0;
show table status;
set global innodb_stats_on_metadata = 1;

You want to turn innodb_stats_on_metadata on immediately after the show table status; so that metadata is used efficiently for Query Optimizer analysis when evaluating queries involving InnoDB. Leaving it off will provide more stable Query Execution plans, but the index statistics grow stale quickly in a heavy-write environment.

Give it a Try !!!

StackExchange Database Administrators Q#39993, answer score: 5