---
# System prepended metadata

title: MySQL Training

---

# MySQL Training

## Tips & Tricks

1. Use the [EXPLAIN command](https://dev.mysql.com/doc/refman/8.0/en/explain.html) in order to gather insights on how optimized your query is. Example:
    ```sql
    EXPLAIN SELECT *
    FROM users
    WHERE YEAR(created_at) = 2022;
    ```
1. Appending `\G` instead of `;` gives you a veritcal analysis. Example:
    ```sql
    SELECT *
    FROM users
    WHERE YEAR(created_at) = 2022\G
    ```
1. If you are always manipulating database data in your queries, then you are probably storing the data incorrectly. Example:
    ```sql
    SELECT *
    FROM users
    WHERE YEAR(created_at) = 2022;
    ```
    **NOTE:** The query above is not able to leverage indexes since the index would be for the `created_at` column and not on the result of `YEAR(created_at)`.
1. You can only use one index per table per query.
1. The order of the columns on a [composite index](https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html) matters.
1. Place columns queried via range to the right of composite indexes. Once an index is used with a range in a query, the query cannot leverage anymore columns in the composite.
1. Make sure the values you compare agains a column are of the same type. If there's a typemismatch, indexes on the column are ignored! Imagine a `homes` table with an `owner_id` column of type `integer`, with an index on that column. The following example is not able to leverage that index because the value used in the `WHERE` statement is a string:
    ```sql
    SELECT *
    FROM homes
    WHERE owner_id = "123"
    ```
1. One can force a specific index to be used on a query. Example:
    ```sql
    SELECT *
    FROM users FORCE INDEX (users_first_name_index)
    WHERE first_name = "Alice"
    ```
1. One can give [optimization hints](https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html) to the optimizer when running a query using the `/*+ INSTRUCTION */` comment. Example:
    ```sql
    SELECT /*+ MAX_EXECUTION_TIME(3000) */ *
    FROM users FORCE INDEX (users_first_name_index)
    WHERE first_name = "Alice"
    ```
1. One can list all queries being executed as follows:
    ```sql
    SHOW PROCESSLIST\G
    ```
1. One can `EXPLAIN` an in progress query:
    ```sql
    EXPLAIN FOR CONNECTION 16\G
    ```
    Where `16` is the id of the query being executed, returned by the `SHOW PROCESSLIST\G` command.
1. Picking the **correct type for a column** can save you memory and disk usage. Example, if we were to use BIGINT type everywhere instead of INT, it means you'll need more memory and more diskspace to store the data. If you know you don't need a BIGINT value, go with INT.
1. `VARCHAR` columns preserve trailing spaces, `CHAR` does not.
1. Partitions can be used to optimize large tables. However, if one is not careful with how it is designed, it can be a "[footgun](https://en.wiktionary.org/wiki/footgun#:~:text=footgun%20(plural%20footguns),shooting%20themselves%20in%20the%20foot.)".

## Antipatterns

1. Using VARCHAR(255) as default string type: That will cause MySQL to reserve way more space (disk and memory) than it needs for the type of data being stored. In extreme cases, temporary tables could also fallback to disk because there's no enough memory to hold all the data needed in memory.