edited body
Source Link
ypercubeᵀᴹ
  • 91.9k
  • 13
  • 188
  • 284

I suggest you create a database that has the INFORMATION_SCHEMA tables (or only those you need) as replicates. Index them appropriately and you will have performance gain.

The problem of syncing between this database and INFORMATION_SCHEMA is tricky though.

You could have a procedure that synchronizes these tables every hour or every 5 minutes (how often is the tables' structure changed?).

Another idea would be to use MySQL Proxy to catch any ALTER TABLE statements (and CREATE and DROP and CREATE INDEX and whatever other statements modify information you need) and then synchronize the replicated information schema after these statements succeed.


If you only need the column names and not any other information, like datatype, length or available indexes, you could perhaps replace the use of SHOW COLUMNS with (fast) queries that return 1 row only, with LIMIT 1 or none at all, with either LIMIT 0 or:

SELECT * FROM TableName WHERE FALSE ;

Despite the general advice against the use odof SELECT *, this may be a legitimate case where nothing else is useful. (everything else but *, may result in error!)

I suggest you create a database that has the INFORMATION_SCHEMA tables (or only those you need) as replicates. Index them appropriately and you will have performance gain.

The problem of syncing between this database and INFORMATION_SCHEMA is tricky though.

You could have a procedure that synchronizes these tables every hour or every 5 minutes (how often is the tables' structure changed?).

Another idea would be to use MySQL Proxy to catch any ALTER TABLE statements (and CREATE and DROP and CREATE INDEX and whatever other statements modify information you need) and then synchronize the replicated information schema after these statements succeed.


If you only need the column names and not any other information, like datatype, length or available indexes, you could perhaps replace the use of SHOW COLUMNS with (fast) queries that return 1 row only, with LIMIT 1 or none at all, with either LIMIT 0 or:

SELECT * FROM TableName WHERE FALSE ;

Despite the general advice against the use od SELECT *, this may be a legitimate case where nothing else is useful. (everything else but *, may result in error!)

I suggest you create a database that has the INFORMATION_SCHEMA tables (or only those you need) as replicates. Index them appropriately and you will have performance gain.

The problem of syncing between this database and INFORMATION_SCHEMA is tricky though.

You could have a procedure that synchronizes these tables every hour or every 5 minutes (how often is the tables' structure changed?).

Another idea would be to use MySQL Proxy to catch any ALTER TABLE statements (and CREATE and DROP and CREATE INDEX and whatever other statements modify information you need) and then synchronize the replicated information schema after these statements succeed.


If you only need the column names and not any other information, like datatype, length or available indexes, you could perhaps replace the use of SHOW COLUMNS with (fast) queries that return 1 row only, with LIMIT 1 or none at all, with either LIMIT 0 or:

SELECT * FROM TableName WHERE FALSE ;

Despite the general advice against the use of SELECT *, this may be a legitimate case where nothing else is useful. (everything else but *, may result in error!)

added 484 characters in body
Source Link
ypercubeᵀᴹ
  • 91.9k
  • 13
  • 188
  • 284

I suggest you create a database that has the INFORMATION_SCHEMA tables (or only those you need) as replicates. Index them appropriately and you will have performance gain.

The problem of syncing between this database and INFORMATION_SCHEMA is tricky though.

You could have a procedure that synchronizes these tables every hour or every 5 minutes (how often is the tables' structure changed?).

Another idea would be to use MySQL Proxy to catch any ALTER TABLE statements (and CREATE and DROP and CREATE INDEX and whatever other statements modify information you need) and then synchronize the replicated information schema after these statements succeed.


If you only need the column names and not any other information, like datatype, length or available indexes, you could perhaps replace the use of SHOW COLUMNS with (fast) queries that return 1 row only, with LIMIT 1 or none at all, with either LIMIT 0 or:

SELECT * FROM TableName WHERE FALSE ;

Despite the general advice against the use od SELECT *, this may be a legitimate case where nothing else is useful. (everything else but *, may result in error!)

I suggest you create a database that has the INFORMATION_SCHEMA tables (or only those you need) as replicates. Index them appropriately and you will have performance gain.

The problem of syncing between this database and INFORMATION_SCHEMA is tricky though.

You could have a procedure that synchronizes these tables every hour or every 5 minutes (how often is the tables' structure changed?).

Another idea would be to use MySQL Proxy to catch any ALTER TABLE statements (and CREATE and DROP and CREATE INDEX and whatever other statements modify information you need) and then synchronize the replicated information schema after these statements succeed.

I suggest you create a database that has the INFORMATION_SCHEMA tables (or only those you need) as replicates. Index them appropriately and you will have performance gain.

The problem of syncing between this database and INFORMATION_SCHEMA is tricky though.

You could have a procedure that synchronizes these tables every hour or every 5 minutes (how often is the tables' structure changed?).

Another idea would be to use MySQL Proxy to catch any ALTER TABLE statements (and CREATE and DROP and CREATE INDEX and whatever other statements modify information you need) and then synchronize the replicated information schema after these statements succeed.


If you only need the column names and not any other information, like datatype, length or available indexes, you could perhaps replace the use of SHOW COLUMNS with (fast) queries that return 1 row only, with LIMIT 1 or none at all, with either LIMIT 0 or:

SELECT * FROM TableName WHERE FALSE ;

Despite the general advice against the use od SELECT *, this may be a legitimate case where nothing else is useful. (everything else but *, may result in error!)

Source Link
ypercubeᵀᴹ
  • 91.9k
  • 13
  • 188
  • 284

I suggest you create a database that has the INFORMATION_SCHEMA tables (or only those you need) as replicates. Index them appropriately and you will have performance gain.

The problem of syncing between this database and INFORMATION_SCHEMA is tricky though.

You could have a procedure that synchronizes these tables every hour or every 5 minutes (how often is the tables' structure changed?).

Another idea would be to use MySQL Proxy to catch any ALTER TABLE statements (and CREATE and DROP and CREATE INDEX and whatever other statements modify information you need) and then synchronize the replicated information schema after these statements succeed.