This guide is designed to help you lighten and optimize your databases at Infomaniak.

Preamble

• The number and size of databases are unlimited.
• However, it is crucial to optimize your tables (via indexes) to avoid overloading the server and ensure fast display of your site, especially when the volume of data increases.

Optimize Queries

Indexes are essential for performance: they work like a book's table of contents, allowing MySQL/MariaDB to find information without reading all the pages. Without indexes, the server must perform a 'full scan' of the table, which is very resource-intensive.

An index is particularly effective on columns used to filter results (clause WHERE), to perform joins between tables (JOIN) or to sort data (ORDER BY).

Analyze with EXPLAIN

Before adding an index, you need to identify the slow query. The EXPLAIN command (also available via the phpMyAdmin interface) allows you to simulate the query.

Observe the "rows" column (number of lines analyzed) and the "key" column (key used). If "rows" is high and "key" is empty (NULL), it is strongly recommended to create an index.

Add Indexes

To add an index, you can use the "Structure" tab of phpMyAdmin or the following SQL command:

ALTER TABLE nom_de_table ADD INDEX nom_index (colonne_cible);

Note: Only index the necessary columns. Too many indexes can slow down the addition or modification of data (INSERT/UPDATE).

Learn More / References

MySQL 8.0 Documentation: Optimization and Indexes
MySQL 8.0 Documentation: Understanding EXPLAIN