Mysql swiss army knife queries
SET SQL_SAFE_UPDATES = 0;
Always forget this one. When you want to update multiple rows, it's requested to disable the safe mode.
Often our metrics show that the database is passing through a severe burnout where the CPU is at 100%. A way to peel into what exactly is happening is:
SHOW FULL PROCESSLIST;
9 root 172.17.0.1:52296 sys Sleep 393 10 root 172.17.0.1:52300 Sleep 393 11 root 172.17.0.1:52304 sys Query 5 executing "/*.. */" 16 root 172.17.0.1:52358 sys Query 0 starting /* ...*/ show full processlist
To kill the problematic process, you can use:
KILL QUERY 11;
If you are into stored procedures and MySQL functions, the benchmark can help you identify how fast they are.
SELECT BENCHMARK(1000000000, CONCAT("Hello", "World"));
Sooner or later, you will run explain to see what MySQL thinks and what plans he has about your query.
EXPLAIN SELECT * FROM `table`;
If you stumbled into zombi transactions, this one would help you find them.
SHOW ENGINE INNODB STATUS;
Proper query to see the index and data size per table.
SELECT TABLE_NAME as `Table`, ROUND(DATA_LENGTH / 1024 / 1024) as `Data (Mb)`, ROUND(INDEX_LENGTH / 1024 / 1024) as `Index (Mb)`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) as ` Total (Mb)` FROM information_schema.TABLES t WHERE TABLE_SCHEMA = 'db_name' ORDER by (DATA_LENGTH + INDEX_LENGTH) DESC;
Similar to the previous one, but you need full rights to the database to access it.
SHOW TABLE STATUS FROM [dbname];
Use it to analyze the table structure, also clone/duplicate tables.
SHOW CREATE TABLE `table_name`;