Show indexes for a specific table:
SHOW INDEXES FROM TABLE_NAME;
Note: INDEX_TYPE ='HASH' is slower than INDEX_TYPE ='BTREE'
Show indexes for tables from a specific database or all databases:
SELECT *
FROM INFORMATION_SCHEMA.STATISTICS
[WHERE TABLE_SCHEMA = 'database_name'];
Add new index for a specific table:
ALTER TABLE `table_name` ADD INDEX `index_name` USING BTREE(`column1`, `column2`);
Rename table:
ALTER TABLE current_table_name RENAME new_table_name;
Show global system variables:
SHOW GLOBAL variables;
Set global variable:
SET GLOBAL long_query_time = 20;
This will have effect only if the field is dynamic, check the documentaion for each variable.
Show global status:
SHOW GLOBAL status;
Get size total size of all MEMORY tables:
SELECT (data_length+index_length)/(1024*1024*1024) table_size,
TABLE_NAME,
TABLE_SCHEMA
FROM information_schema.tables
WHERE ENGINE LIKE 'MEMORY'
ORDER BY table_size DESC;