Why is this query slow to execute?

You want to start monitoring statistics on the distribution of storage engines that are being used
and the average sizes of tables in the various databases.
Some details are as follows:
The Mysql instance has 400 databases.
Each database on an average consists of 25-50 tables.
You use the query:
SELECT TABLE_SCHEMA,
‘ENGINE’,
COUNT (*),
SUM (data_length) total_size
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’
GROUP BY TABLE_SCHEMA, ‘ENGINE’
;
Why is this query slow to execute?

You want to start monitoring statistics on the distribution of storage engines that are being used
and the average sizes of tables in the various databases.
Some details are as follows:
The Mysql instance has 400 databases.
Each database on an average consists of 25-50 tables.
You use the query:
SELECT TABLE_SCHEMA,
‘ENGINE’,
COUNT (*),
SUM (data_length) total_size
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ‘BASE TABLE’
GROUP BY TABLE_SCHEMA, ‘ENGINE’
;
Why is this query slow to execute?

A.
Counting and summarizing all table pages in the InnoDB shared tablespace is time consuming.

B.
Collecting information requires various disk-level operations and is time consuming.

C.
Aggregating details from various storage engine caches for the final output is time consuming.

D.
Collecting information requires large numbers of locks on various INFORMATION_SCHEMA
tables.

Explanation:



Leave a Reply 7

Your email address will not be published. Required fields are marked *


Steve

Steve

Unclear; but from past experience I would say A.

E

E

I would say B. Because at least with MyISAM all the information about tables is not initially read from disk. So disk operations are needed.

Simon

Simon

B seems to be the best answer

http://dev.mysql.com/doc/refman/5.5/en/information-schema-optimization.html

Within a database, avoid queries that use a nonconstant table name lookup value (or no lookup value) because they require a scan of the database directory to find matching table files.

Cristian

Cristian

..but this query does not have nonconstant table names & does not require computing multiple table names & then looking up stuff in more than one table – it has multiple values for an attribute called table_name within the single table information_schema.tables.
A. is the only answer that really fits.