Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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

share|improve this answer
    
Thank you for accepting. May I get an upvote as well, please ??? –  RolandoMySQLDBA Oct 16 '13 at 15:22

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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