Mysql swiss-army knife queries

Clean-up data

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.

Processes

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;

Performance

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;

Data size

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`;

Structure

Use it to analyze the table structure, also clone/duplicate tables.

SHOW CREATE TABLE `table_name`;

Deduplication

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;

Let's be in contact and join my newsletter.

Join 0 other subscribers.

Blog posts

Mysql swiss army knife queries