MySQL Bugs: #43968: Show index has performance degradation as the table of focus increases in size

Bug #43968	Show index has performance degradation as the table of focus increases in size
Submitted:	30 Mar 2009 19:07	Modified:	5 Apr 2013 12:51
Reporter:	Edward Huang	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.1.32, 5.1 bzr	OS:	Any (Linux and Windows)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Show index return result slows down as the size of the database increases.
We've noticed this problem from Community edition 5.1.30 running on Linux.  We've also test this problem on Community edition 5.1.32 on Windows XP via cygwin.

The observed behavior appears in both running the "show index..." from a query browser, via administrator UI, or from accessing JDBC driver calls .getPrimaryKeys(...)

Is this a feature or bug?  Is there another way to obtain index schema information with out using show index... ?

How to repeat:
1. create a test table with following setup

CREATE TABLE  `test`.`test_log` (
  `SEQ` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `TEST_ID` int(11) NOT NULL,
  `TEST_NAME` varchar(20) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`SEQ`,`TEST_ID`),
  KEY `IX_TEST_ID` (`TEST_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1250392 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY HASH (TEST_ID)
PARTITIONS 50 */;

2. write to sktestinsert.sql with following sql:

INSERT INTO TEST_LOG(TEST_ID,TEST_NAME) values
(rand()*8879889896,'THIS IS JUST A TEST'),
(rand()*8879889896,'THIS IS JUST A TEST'),
(rand()*8879889896,'THIS IS JUST A TEST'),
.....####### copy & paste about 10000 entries #####
(rand()*8879889896,'THIS IS JUST A TEST');

3. write to sktesttimed.sql with following sql:

SHOW INDEX FROM TEST_LOG;

4. write a shell script with following contents

time ./mysql.exe -u root test < sktesttimed.sql

for ((j=0; j<=1000; j++))
do

  for ((i=0; i<= 10; i++))
  do
    ./mysql.exe -u root test < sktestinsert.sql  
  done;

  time ./mysql.exe -u root test < sktesttimed.sql
done;

Thank you for the report.

Verified as described.

Strictly say some performance degradation is expected, because  SHOW INDEX shows Cardinality which can vary for different data.

But problem occurs only if storage engine is InnoDB, so I mark this as verified.

results in my environment

Attachment: resutls.txt (text/plain), 15.38 KiB.

Is there a way to "SHOW INDEX..." without cardinality?

I wonder if the SHOW INDEX command could be extended to include an option to NOT compute cardinality.

The reason this bug was noticed was due to the long delays we experienced while using the getPrimaryKeys() method of the DatabaseMetaData class of the JDBC interface.  During the long delay, I had sniffed the wire to see that under the covers JConnect was issuing a "SHOW KEYS" command (which is an alias of SHOW INDEX).  If the underlying command were to not compute cardinality, the results would come back faster.

Note that cardinality does NOT seem to be a return value from the getPrimaryKeys() method, which is why I'm bringing this up.

Vasil, could you have a look? I wonder also if innodb_stats_on_metadata covers it.

Hmm, looks like this has been forgotten for a long time!

The cardinality estimation does not depend on the size of the table, it
always looks up a fixed number of pages.

innodb_stats_on_metadata=0 will disable cardinality estimation during SHOW
INDEX, can you try that one and see if the problem goes away?

In 5.6+ with InnoDB persistent statistics, cardinality will not be calculated automatically during SHOW INDEX.

Edward,

please test in your environment. I can not repeat described behavior anymore since version 5.1.67. With innodb_stats_on_metadata=1 SHOW INDEX run a bit slower: 0.220s vs 0.010s, but there is no execution time increase.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".