4

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!

5

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 !!!

  • Thank you for accepting. May I get an upvote as well, please ??? – RolandoMySQLDBA Oct 16 '13 at 15:22
0

Given that 1.8GB is a fairly small database I would assume you need to optimize it and repair it. It may take some time but essentially it would be far better if you could avoid phpmyadmin for that particular task and give it the terminal. If have access to the shell try

mysqlcheck -u your_username -p dbname

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.