Tips For Improving MySQL Database Performance

Advertisement

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 :

Having efficient SQL queries

Designing a logical schema which support the application’s need

Designing to support use case

Relational constraints

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.

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.