Consistency/Isolation for queries using UNION ALL against information_schema tables in a transaction
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.
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'
FROM information_schema.COLUMNS c)
FROM information_schema.TABLES t)