Skip to main content

One post tagged with "global status"

View All Tags

· One min read
Hreniuc Cristian-Alexandru

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;