CODEWITHSUNDEEP
mysql
Lurk21
Lurk21

Reputation: 2337

MySQL SELECT from INFORMATION_SCHEMA.COLUMNS taking > 1 minute

Here's my query:

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = "transactions"

And here is how long it took:

Showing rows 0 - 29 ( 36 total, Query took 61.8934 sec)

I'm no dba but this isn't normal, is it? Should it be my expectation that querying INFORMATION_SCHEMA should take over a minute? Is there anything I'm doing wrong here? Do I need to add an index or something in order to get normal response out of this?

--- Edit to address questions

Version 5.5.30-30.2

This db is hosted by HostGator

EXPLAIN SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = "transactions"

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  COLUMNS     ALL     NULL    TABLE_NAME  NULL    NULL    NULL    Using where; Open_frm_only; Scanned 1 database

SHOW CREATE TABLE transactions:

transactions    CREATE TABLE `transactions` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `accountId` int(11) NOT NULL DEFAULT '0',
 `contractId` int(11) NOT NULL DEFAULT '0',
 `insertionId` int(11) NOT NULL DEFAULT '0',
 `aNetCIMProfileId` int(11) NOT NULL DEFAULT '0',
 `paymentProfileId` int(11) NOT NULL DEFAULT '0',
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `wasSuccessful` tinyint(1) NOT NULL DEFAULT '0',
 `amount` int(11) NOT NULL DEFAULT '0',
 `resultText` varchar(255) NOT NULL,
 `authNetTransactionId` bigint(20) NOT NULL DEFAULT '0',
 `isChargedBack` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=latin1

SELECT Count( * )
FROM INFORMATION_SCHEMA.COLUMNS

[This also took > 1 minute]

1303

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = "transactions" 
    AND TABLE_SCHEMA = 'mydbname'

Same response time

SET global innodb_stats_on_metadata = 0;
#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

(This db is hosted by HostGator)

Upvotes: 2

Views: 23933

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562348

The INFORMATION_SCHEMA has been known for a long time to have really bad performance if you have a lot of databases and tables.

MySQL improves the performance of I_S in each major release, so you need to upgrade as much as you can to the current version.

You should also set the server option innodb_stats_on_metadata=0 which will help a bit. It reduces the cost of statistics collection when you query the I_S. In MySQL 5.6.6 and later, this is 0 by default.

In MySQL 8.0 they have totally redesigned the internals of the I_S, so it should gain much better performance.

Upvotes: 0

Shreekrishna shankhwar
Shreekrishna shankhwar

Reputation: 31

SELECT COLUMN_NAME,TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME like  "%transactions%" 
    AND TABLE_SCHEMA = 'mydbname'

in TABLE_SCHEMA provide the database name mydbname. On shared hosting it will take more time if you have access then it will show you the result.

Upvotes: 3

Related Questions