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.
SET FOREIGN_KEY_CHECKS = 0;
Useful when you have cycle references in your schema but still you want to clean-up everything.
Make sure you set it to
SET FOREIGN_KEY_CHECKS = 1; after you are done.
Often our metrics show that the database is passing through a severe burnout where the CPU is at 100%. A way to peek 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 `db_name`;
Use it to analyze the table structure, also clone/duplicate tables.
SHOW CREATE TABLE `table_name`;
To find duplicated records, you will group them by columns identifying their uniqueness.
SELECT column1, column2, count(*) as count FROM `table` GROUP BY `column1`, `column2` HAVING count > 1;
To clean up duplicates, you can use inner join on the same table and check for the unique columns to be equal. Leave the record with the smallest id.
DELETE t1 FROM `table` t1 INNER JOIN `table` t2 WHERE t1.id < t2.id AND t1.column1 = t2.column1 AND t1.column2 = t2.column2;