This guide is designed to help Infomaniak users lighten and optimize their databases.

Preamble

• The number and size of databases are unlimited.
• But it is crucial to optimize your queries and tables (using indexes) to avoid overloading the server, especially when the size of the database increases significantly.

Optimize queries

Indexes play a crucial role in improving query performance by enabling quick searches for rows containing specific values. Without indexes, MySQL must scan all rows in the table and perform comparisons at each step to extract relevant results. The larger the table, the more resource-intensive this operation becomes.

With appropriate indexes on the columns used, MySQL can quickly locate the rows in the data file without having to scan the entire table.

Using the EXPLAIN command

The EXPLAIN command allows you to analyze an SQL query to determine the number of rows scanned during its execution. If this number is high, it is recommended to add indexes to improve the query's performance. Generally, indexes are added to the columns used in the WHERE clause of an SQL query.

Add indexes

To add indexes to a table, use the following syntax:

ALTER TABLE nom_de_table ADD INDEX nom_index (colonne1, colonne2, ...);

Learn more / References

http://dev.mysql.com/doc/refman/5.0/fr/mysql-indexes.html
http://dev.mysql.com/doc/refman/5.0/fr/explain.html