Mysql – SHOW TABLE STATUS very slow on InnoDB

innodbmariadbMySQL

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!

Best Answer

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

Related Solutions

MySQL InnoDB – Dependent Subquery Very Slow

Some notes:

Why is there no PRIMARY KEY in the table?
Why date is of type int and not date or datetime or timestamp?
Why is it called date when it stores date and time?

Regarding the actual question, the efficiency of the query, an index on (tindex, tn, col, date) would help in my opinion much more than the other suggestions. And since you have no primary key and the (tindex, tn, col) is unique (you have added another unique index), my suggestion is:

(optionally) drop that unique index)

define the primary key as (tindex, tn, col):

ALTER TABLE mytable
  DROP INDEX __the_name_of_the_unique_index,  -- this is optional
  ADD PRIMARY KEY (tindex, tn, col) ;

This will take some time (that's why I suggest you do both operations in one pass.)

Then you can measure your query and all the suggested rewritings of the query (and check if the primary index is used).

Why is this a better index?

Because the tindex column is the first column in the index, all the rows with tindex='anticorrosive' will be in consecutive pages in the index (and all the rows with tindex='corrosive' will be in another part of index but still in consecutive pages.) So, reading (scanning) these 2 parts of the index will be much faster than scanning the whole table or scanning the whole index twice (which is what mysql does essentially with the (tn, col, tindex) index.)

Another benefit of making this index the primary key is that you get rid of the (hidden) 6-byte column that InnoDB added and has been using as the clustered index of the table (since you did not provide any primary or unique constraint/index) so the table is now less wide. The (tindex, tn, col) will be the clustered index of the table from now on. That also means that the date values will be available for the query - after the 2 parts of the index have been scanned.

Mysql – SHOW INNODB STATUS averages calculated

The SHOW ENGINE INNODB STATUS \G command will show data from the last time the command was run till now - not sure what it will be the first time. I can't shut down the server here, but if you run the command rapidly twice in succession, you'll see the time since last run - set your

mysql> pager less;

mysql> show engine innodb status \G


=====================================
2014-07-04 15:44:42 7f0a2d9c9700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds

Take a look here - averages of less than ~30 seconds are worthless according to Peter Zaitsev. You could also take a look at Baron Schwartz's innotop tool - available here.

Best Answer

Related Solutions

MySQL InnoDB – Dependent Subquery Very Slow

Mysql – SHOW INNODB STATUS averages calculated

Related Question