Consistency/Isolation for queries using UNION ALL against information_schema tables in a transaction
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)