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.
- 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:
» Show indexes
Indexes and keys of a table:
» Show index size (and more)
» Check if an index will be used in a query
EXPLAIN to the beginning of your query, for example:
possible_keys are keys which exist on the table,
keys are the keys which are used for this query)
EXPLAIN ANALYZE to see how long each step takes (couldn’t really wrap my head around the output yet).
» Show 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
\G is optional for a different view but might not work with all (graphical?) clients.
» Kill a process
» Copy Table
» Full Copy with Indexes
Solution from raveren and Mojtaba
» Full Copy without Indexes
Solution from devart.com
» Only the Schema with Indexes
» Only the Schema without Indexes
Solution from Sergey Podushkin.
Using limit e.g. for pagination as follows might do a full table scan despite having a primary key
You can use a
BETWEEN instead of the
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
» 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)