Whatever technology you use for your website or intranet, if it is a dynamic site, chances are that it works with a relational database management system (RDBMS). In this field MySql performance management has been the undisputed leader for several years. Here are a few tips to make the most of its performance.
When designing, using the normal form reduces data redundancy and thus the size of the database. Concretely, if a string repeats itself several times in a field, it is generally preferable to create a table and replace this field by a numeric foreign key.
Preferably using self-incrementing numeric primary keys simplifies indexing and improves performance compared to string keys and/or compound keys.
Creating indexes for each foreign key and/or search field improves performance when performing join or search queries.
Using the MyISAM data engine instead of InnoDB improves the performance of your SELECT queries.
Using UTF-8 encoding will allow you to manage all international characters without encoding problems.
Tools such as MySqlTuner allow you to analyze the average usage of your MySql server and get advice on how to optimize the configuration.
The MySql server is often installed on the same machine as the web server, in this case you can configure MySql to use UNIX sockets instead of TCP sockets, in most cases this allows a better performance.
Opening and closing connections to the data server is a time consuming operation. Using persistent connections saves these 2 operations on every page of the site. Be careful, however, connection sharing does not work in the case of separate processes (CGI).
Avoid SELECT * if possible and reduce the number of fields, in order to reduce the data loaded in memory.
Replace your WHERE ... IN clauses by WHERE EXISTS clauses.
Count the queries on each page, a large number of queries can mean an "N+1 problem", i.e. a SELECT query placed in a loop.
Using prepared queries or stored procedures makes it easy for MySql to cache queries internally and provides you with a good level of security.
Use the EXPLAIN clause to understand how a query works and which clauses impact its performance.
MySql has an internal caching mechanism, set this value to the maximum according to the RAM you have.
Otherwise you can use an external caching system such as Memcache. The interest of an external cache is to unload the MySql server while having the efficiency of the cache in RAM.
Large tables can cause errors, slow down the site or even cause errors (crash table). If you have temporary tables think of setting up a script for purging and/or regular repair.
MySql has a repair function and a database optimization function, schedule these tasks during the night just before the daily backup task.
In conclusion, the best advice we can give you is to have a good database administrator at your disposal (if you want to find out good workers https://www.enteros.com/can help you with this question) or, if you are motivated, to become one yourself. If you have any doubts about the performance of your database, do not hesitate to consult our experts.