1

I have already seen this question and answer, however it doesn't appear to work with temporary tables.

Using MySQL Workbench I've created a temporary table using this code;

create temporary table IF NOT EXISTS tmpMyTable AS (
    SELECT * FROM records
)

When I select * from tmpMyTable I can see the data. However now I need to calculate the size of the tmpMyTable, in MB.

How can I achieve this? What do I need to edit in the following code?

SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
    AND table_name = "$TABLE_NAME";

Any advice is appreciated.

TheOrdinaryGeek
  • 2,273
  • 5
  • 21
  • 47
  • Unfortunately, `information_schema.TABLES` doesn't list temporary tables. Do you absolutely have to have them temporary? – Yossi Vainshtein Jul 31 '18 at 10:40
  • @YossiVainshtein unfortunately they must be temporary tables. Unless it's possible to export a temp table, then re-import the dumo and calculate that way? Open to suggestions! – TheOrdinaryGeek Jul 31 '18 at 10:57
  • If you say you are open to export and import your temporary table, you may simply create a new (non-temporary) dummy table (`create table dummy as select * from tmpMyTable`) and get the size of this table the usual way. (You may have to take precautions if you do this in more than one session). And although I assume that this is not your actual select query, if it *were*, you could get the the size of `records` table (without indexes, but adjusted for the internal id). It might help to understand what you need that information for, maybe there are alternatives. – Solarflare Jul 31 '18 at 12:01

0 Answers0