MySQL Forums
Forum List  »  General

Consistency/Isolation for queries using UNION ALL against information_schema tables in a transaction
Posted by: Shiva Vanamala
Date: September 16, 2020 12:13AM

Hi Everyone,
I have been working with a query against the information_schema database that is very expensive, I am trying to decrease the complexity of the query by using a UNION ALL instead and performing the JOIN in the application.
I would like to know if a consistent snapshot is taken for both the tables information_schema.TABLES and information_schema.COLUMNS, i.e. the snapshots are taken at the same instant.

SELECT
c.TABLE_SCHEMA,
c.TABLE_NAME,
c.COLUMN_NAME,
c.COLUMN_TYPE,
c.COLUMN_KEY,
t.TABLE_ROWS,
t.DATA_LENGTH
FROM information_schema.COLUMNS c
LEFT JOIN information_schema.TABLES t ON t.TABLE_SCHEMA COLLATE 'utf8_bin' = c.TABLE_SCHEMA COLLATE 'utf8_bin' AND
t.TABLE_NAME COLLATE 'utf8_bin' = c.TABLE_NAME COLLATE 'utf8_bin'
vs
(SELECT
'COLUMN' ROW_TYPE,
c.TABLE_SCHEMA SCHEMA_NAME,
c.TABLE_NAME TABLE_NAME,
c.COLUMN_NAME COLUMN_NAME,
c.COLUMN_TYPE C_COLUMN_TYPE,
c.COLUMN_KEY C_COLUMN_KEY,
NULL T_TABLE_TYPE,
NULL T_TABLE_ROWS,
NULL T_DATA_LENGTH
FROM information_schema.COLUMNS c)
UNION ALL
(SELECT
'TABLE' ROW_TYPE,
t.TABLE_SCHEMA SCHEMA_NAME,
t.TABLE_NAME TABLE_NAME,
NULL COLUMN_NAME,
NULL C_COLUMN_TYPE,
NULL C_COLUMN_KEY,
t.TABLE_TYPE T_TABLE_TYPE,
t.TABLE_ROWS T_TABLE_ROWS,
t.DATA_LENGTH T_DATA_LENGTH
FROM information_schema.TABLES t)

Options: ReplyQuote


Subject
Written By
Posted
Consistency/Isolation for queries using UNION ALL against information_schema tables in a transaction
September 16, 2020 12:13AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.