-- Show all storage engines supported by this MySQL installation
SHOW ENGINESG

-- Output fields:
-- Engine       -- engine name
-- Support      -- DEFAULT, YES, NO, or DISABLED
-- Comment      -- description
-- Transactions -- YES or NO
-- XA           -- distributed transaction support
-- Savepoints   -- YES or NO

-- Create a table explicitly using InnoDB (default, so optional)
CREATE TABLE orders (
  order_id    INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT         NOT NULL,
  order_date  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  total       DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
) ENGINE = InnoDB;

-- Check which engine a table uses
SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE();

-- Show InnoDB status
SHOW ENGINE INNODB STATUSG

-- Check current buffer pool size
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Check buffer pool hit ratio (aim for > 99%)
SELECT
  (1 - (
    variable_value / (
      SELECT variable_value
      FROM performance_schema.global_status
      WHERE variable_name = 'Innodb_buffer_pool_reads'
    ) * 100
  )) AS hit_ratio_pct
FROM performance_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_read_requests';

-- Set buffer pool to 70-80% of available RAM in my.cnf
-- [mysqld]
-- innodb_buffer_pool_size = 4G

-- MyISAM table (rare in modern MySQL)
CREATE TABLE search_log (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  query   VARCHAR(255),
  logged  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE = MyISAM;

-- MyISAM has a COUNT(*) shortcut (no full scan needed for simple COUNT)
SELECT COUNT(*) FROM search_log;  -- Very fast on MyISAM, reads table metadata

-- Good use case: session cache, temporary computation table
CREATE TABLE rate_limiter (
  client_ip    VARCHAR(45) NOT NULL,
  request_count INT        NOT NULL DEFAULT 0,
  window_start DATETIME    NOT NULL,
  PRIMARY KEY (client_ip)
) ENGINE = MEMORY;

-- Hash index (default): perfect for exact equality lookups
-- ALTER TABLE rate_limiter ADD INDEX USING HASH (client_ip);

-- BTree index: needed for range queries
CREATE TABLE temp_sort_work (
  id    INT,
  score DECIMAL(10,4),
  INDEX USING BTREE (score)
) ENGINE = MEMORY;

CREATE TABLE access_log_archive (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT,
  endpoint   VARCHAR(255),
  ip_address VARCHAR(45),
  logged_at  DATETIME
) ENGINE = ARCHIVE;

-- Used in replication relay topology
CREATE TABLE relay_events (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  payload TEXT
) ENGINE = BLACKHOLE;

-- INSERT is accepted (logged to binlog), but SELECT returns nothing
INSERT INTO relay_events (payload) VALUES ('forward to replica');
SELECT * FROM relay_events;  -- Returns empty result set

CREATE TABLE export_data (
  id    INT          NOT NULL,  -- CSV engine: all columns must be NOT NULL
  name  VARCHAR(100) NOT NULL,
  value DECIMAL(10,2) NOT NULL
) ENGINE = CSV;

-- The data file is stored at: datadir/db_name/export_data.CSV
-- Open it in any spreadsheet application

-- Convert a MyISAM table to InnoDB (online, non-blocking in MySQL 5.6+)
ALTER TABLE old_table ENGINE = InnoDB;

-- Convert InnoDB to ARCHIVE for cold data
ALTER TABLE completed_orders_2020 ENGINE = ARCHIVE;

-- Convert MEMORY table to InnoDB
ALTER TABLE temp_results ENGINE = InnoDB;

-- Check table engine before and after
SELECT TABLE_NAME, ENGINE, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb'
ORDER BY ENGINE, TABLE_NAME;

-- Verify file-per-table is enabled (default ON in MySQL 5.6+)
SHOW VARIABLES LIKE 'innodb_file_per_table';

-- Each table has its own file:
-- datadir/mydb/orders.ibd
-- datadir/mydb/customers.ibd

-- Check tablespace file for a specific table
SELECT TABLESPACE_NAME, FILE_NAME, FILE_SIZE / 1024 / 1024 AS size_mb
FROM information_schema.FILES
WHERE FILE_NAME LIKE '%mydb%'
ORDER BY FILE_SIZE DESC;

-- Reclaim space after large DELETEs (reorganizes and truncates the ibd file)
OPTIMIZE TABLE orders;  -- equivalent to ALTER TABLE orders ENGINE=InnoDB

-- Find all non-InnoDB tables (run this to find legacy tables)
SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  ENGINE,
  TABLE_ROWS,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
  AND ENGINE != 'InnoDB'
ORDER BY size_mb DESC;

-- List all tables with their engine and size
SELECT
  TABLE_NAME,
  ENGINE,
  TABLE_ROWS,
  ROUND(DATA_LENGTH / 1024 / 1024, 2)  AS data_mb,
  ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;