DROP DATABASE IF EXISTS TestTableProcedure;
CREATE DATABASE TestTableProcedure;
USE TestTableProcedure;

CREATE TABLE CustomTable
(
  ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  SomeValue CHAR(255)
);

CREATE TABLE CustomData
(
  SomeValue CHAR(255)
);

DELIMITER $$

CREATE TRIGGER CustomTable_BU BEFORE UPDATE ON CustomTable FOR EACH ROW BEGIN
  #Some generated code, so parser/compiler would not optimize it
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,    0));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,    1));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,    2));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,    3));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,    4));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,    5));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,    6));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,    7));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,    8));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,    9));

  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   10));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   11));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   12));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   13));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   14));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   15));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   16));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   17));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   18));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   19));

  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   20));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   21));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   22));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   23));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   24));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   25));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   26));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   27));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   28));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   29));

  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   30));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   31));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   32));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   33));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   34));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   35));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   36));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   37));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   38));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   39));

  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   40));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   41));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   42));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   43));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   44));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   45));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   46));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   47));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   48));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   49));

  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   50));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   51));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   52));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   53));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   54));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   55));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   56));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   57));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   58));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   59));

  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   60));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   61));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   62));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   63));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   64));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   65));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   66));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   67));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   68));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   69));

  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   70));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   71));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   72));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   73));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   74));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   75));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   76));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   77));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   78));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   79));

  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   80));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   81));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   82));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   83));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   84));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   85));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   86));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   87));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   88));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   89));

  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   90));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   91));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   92));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   93));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   94));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   95));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   96));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   97));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   98));
  INSERT INTO CustomData (SomeValue) VALUES (CONCAT(NEW.SomeValue,   99));
END$$ 

CREATE PROCEDURE CustomTableSimpleSelectProcedure()
BEGIN
  SELECT A.ID
  FROM CustomTable AS A 
  WHERE 0 = 1;
END$$

DELIMITER ;

##########################################################################################################################################
FLUSH TABLES;
SELECT current_alloc INTO @M1 FROM sys.x$memory_global_by_current_bytes WHERE event_name LIKE 'memory/sql/sp_head::main_mem_root';

SELECT A.ID 
FROM CustomTable AS A 
WHERE A.ID = -1;

SELECT current_alloc INTO @M2 FROM sys.x$memory_global_by_current_bytes WHERE event_name LIKE 'memory/sql/sp_head::main_mem_root';
SELECT FORMAT_BYTES(IFNULL(@M2, 0) - IFNULL(@M1, 0)) MemUsage;

SELECT OBJECT_SCHEMA, OBJECT_NAME, COUNT(*) FROM performance_schema.table_handles WHERE OBJECT_SCHEMA = 'TestTableProcedure' GROUP BY OBJECT_SCHEMA, OBJECT_NAME;

##########################################################################################################################################
FLUSH TABLES;
SELECT current_alloc INTO @M1 FROM sys.x$memory_global_by_current_bytes WHERE event_name LIKE 'memory/sql/sp_head::main_mem_root';

SELECT A.ID 
FROM CustomTable AS A 
LEFT JOIN CustomTable AS B ON B.ID = A.ID
LEFT JOIN CustomTable AS C ON C.ID = A.ID
WHERE A.ID = -1;

SELECT current_alloc INTO @M2 FROM sys.x$memory_global_by_current_bytes WHERE event_name LIKE 'memory/sql/sp_head::main_mem_root';
SELECT FORMAT_BYTES(IFNULL(@M2, 0) - IFNULL(@M1, 0)) MemUsage;

SELECT OBJECT_SCHEMA, OBJECT_NAME, COUNT(*) FROM performance_schema.table_handles WHERE OBJECT_SCHEMA = 'TestTableProcedure' GROUP BY OBJECT_SCHEMA, OBJECT_NAME;

##########################################################################################################################################
FLUSH TABLES;
SELECT current_alloc INTO @M1 FROM sys.x$memory_global_by_current_bytes WHERE event_name LIKE 'memory/sql/sp_head::main_mem_root';

CALL CustomTableSimpleSelectProcedure();

SELECT current_alloc INTO @M2 FROM sys.x$memory_global_by_current_bytes WHERE event_name LIKE 'memory/sql/sp_head::main_mem_root';
SELECT FORMAT_BYTES(IFNULL(@M2, 0) - IFNULL(@M1, 0)) MemUsage;

SELECT OBJECT_SCHEMA, OBJECT_NAME, COUNT(*) FROM performance_schema.table_handles WHERE OBJECT_SCHEMA = 'TestTableProcedure' GROUP BY OBJECT_SCHEMA, OBJECT_NAME;

##########################################################################################################################################

FLUSH TABLES;
DROP DATABASE IF EXISTS TestTableProcedure;