MySQL bits

Recently, I had to examine a performance issue of a MySQL query. Here are some notes with helpful stuff I’ve used. All the commands might work with MariaDB too, but this was not tested by me.

Update 2023-05-18: I found Understanding database Indexes in PostgreSQL by Paweł Dąbrowski which contains similar and more detailed information but for PostgreSQL.

» Common

• Currently, my preferd mySQL GUI management tool on macOS: Sequel Ace.
• Alternative cross-platform and cross-database GUI tools: DbGate, Jailer
• Use backticks (`) e.g. `table-name` when the name contains “unusual” characters such as -.

» Describe table

Get an overview of the table:

1

DESCRIBE TABLE <TABLE>

» Indexes

» Show indexes

Indexes and keys of a table:

1

SHOW INDEX FROM <TABLE>;

» Show index size (and more)

1

SHOW TABLE STATUS FROM <DATABASE>

» Check if an index will be used in a query

Add EXPLAIN to the beginning of your query, for example:

1

EXPLAIN SELECT <COLUMNS> FROM <TABLE> LIMIT 1000

(possible_keys are keys which exist on the table, keys are the keys which are used for this query)

Use EXPLAIN ANALYZE to see how long each step takes (couldn’t really wrap my head around the output yet).

» Show unused indexes

1

SELECT * FROM sys.schema_unused_indexes;

(Should be checked after your queries were executed and the information is cleaned after a DB restart too)

Reference: Tomer Shay @ EverSQL

» Processes (e.g. queries)

» Show runnig processes

1

SHOW FULL PROCESSLIST \G;

\G is optional for a different view but might not work with all (graphical?) clients.

» Kill a process

1

KILL <ID>

» Copy Table

» Full Copy with Indexes

1
2

CREATE TABLE <NEW_TABLE> LIKE <OLD_TABLE>; 
INSERT INTO <NEW_TABLE> SELECT * FROM <OLD_TABLE>;

Solution from raveren and Mojtaba

» Full Copy without Indexes

1

CREATE TABLE <NEW_TABLE> SELECT * FROM <OLD_TABLE>;

Solution from devart.com

» Only the Schema with Indexes

1

CREATE TABLE <NEW_TABLE> LIKE <OLD_TABLE>;

» Only the Schema without Indexes

1

CREATE TABLE <NEW_TABLE> SELECT * FROM <OLD_TABLE> LIMIT 0;

Solution from Sergey Podushkin.

» LIMIT

Using limit e.g. for pagination as follows might do a full table scan despite having a primary key id:

1

SELECT * FROM <TABLE> LIMIT 150,50;

You can use a WHERE and BETWEEN instead of the LIMIT:

1

SELECT * FROM <TABLE> WHERE id BETWEEN 150 AND 200;

or WHERE and LIMIT:

1

SELECT * FROM <TABLE> WHERE id >= 150 LIMIT 50;

Reference: Bill Karwin @ Stackoverflow

But your IDs have to be incremented strictly by one. When a row was removed and there are gaps in the id column, this might lead to unwanted results. WHERE id BETWEEN x AND y might show less or no results. WHERE id => x LIMIT y might show repeated results on the “next page” when x is strictly incremented by y.

» Composite Primary Key

If you have a Composite Primary Key and want to use it in in your query, all columns of the primary key need to be listed in the exact same order as defined by the primary key. Otherwise, the primary key might not be used as an index and your query might be very slow! (TODO: “all columns” might not be necessary, the trailing columns of the key could be omitted maybe IIRC)