Tips For Improving MySQL Database Performance

Be it WordPress like PHP-MySQL app or custom app, database can be bottleneck. Here are some tips for improving MySQL database performance. Definitely there are specialised text books to detailed documentation by MySQL, it is not always practical for all to read to lengthy texts, extract what are important points from them and apply. This kind guide is important for the webmaster and newbie developers.

Tips For Improving MySQL Database Performance

Basically many matters depend on the way the web software is written. Elsewhere on this web you’ll find guide on improving indexing strategies, avoid unnecessary need of frequent usage of INSERT, UPDATE, or DELETE statements, avoiding correlated subqueries and coding loops. Basically we need to improve the logical structure of the query and tables to decrease the need of access as few data as possible :

1. Having efficient SQL queries
2. Designing a logical schema which support the application’s need
3. Designing to support use case
4. Relational constraints
5. Normalization

The above is for optimising code. But all these are applicable for custom software development, they are grossly not practical for ready to use software like WordPress or already developed custom software. The reason to know them do matters for WordPress plugin, theme, snippet, template selections and developments.

At MySQL server level, we need to know the DB Engine. For example, we talked about MyISAM vs Innodb MySQL Engine, InnoDB vs Percona XtraDB vs TokuDB etc. Also, the distribution like MariaDB, Percona, original MySQL, MySQL Enterprise do matters for various reasons.

Next of course matters like storage and caching. We discussed how we can enable caching for MySQL server.

Obviously the hardware on which MySQL server is installed matters huge. Using some known profiling scripts like Major Hayden, testing various optimised my.cnf settings or using Percona’s web based wizard to generate my.cnf are practical approaches. There are tools like mysqlcheck.

Frankly, beyond these checking and optimisation; one needs an experienced database expert for a serious production website or application.