Optimize WordPress database perfomance

Optimize WordPress database perfomance

Optimize WordPress database

Optimize WordPress database perfomance – every blog owner got this conclusion, ealier or later, but finally always.
Any site owner asks himself, how can I make my site lighter and faster. What else should I do? What technique to apply? General site speed is critical property for present days. As there are a lot of alternative variants where web-surfer may find and get the same information, large part of visitors do not wait, while their browsers finish download process of slow page. They just return to search engine and click on the next link from the huge list of available sources. This way, we (site owners) lose auditory and, as a result, have a lower traffic. And WordPress blogs are not exclusion. The same general rools are in action for our loving WordPress too. It doesn’t matter, what platform do you use, in order to build your site. It does matter, with what speed your platform delivers content to your site’s visitors.
Reliable hosting service provider; fast and powerful server; wide, high-speed, broadband, backbone channel; popular and effective publishing platform – all of these factors are important and valuable in relation to the final result – your site speed.
Good, you had made your choice. For different reasons this time you can not make it better. What else could you enhance?

Client side: HTML, CSS, JavaScript – yes, it was done. Pages markup and scripts code are clean and minimized.
Server side: Yes, we can not (more exact we should not) modify WordPress core PHP source code. But there are some ways to raise its productivity, e.g. this way – “single.php is called twice – how to stop”. General idea, WordPress is powerful and universal. If you don’t need some feature, block, exclude it – such step will make WordPress lighter and faster.
With such thought in your head you use minimal set of plugins with effective PHP code. Let’s mark this position as ‘processed’ too.
Is your posts content dynamic or static? If it is static why server should render it executing PHP code, retriving from database, formatting, rendering, etc. again and again? Yes, here we go to other important theme – content caching. There are a lot of plugins, which put successfully rendered content into static HTML form and return it on to user requests very fast without PHP and MySQL database overload. From one side, it could make your site as quick as a bullet, but other side exists too. What will you lose, when you make you site more, if not the whole one, yes, fast, but – static? It is the theme of the separate and serious talk and need separate post to discuss.

What other productivity reserve do we have? From where WordPress gets all those posts content? Yes, three times Yes – from MySQL database. So database operations speed is important too and it is not last thing for WordPress driven site productivity.
Let’s see what developers propose for WordPress users without programming experience and even technical skills in this category of WordPress plugins. I selected plugins by these 2 criteria:
1) it should be fresh enough, that is updated at least one time this 2012 year;
2) it probably has “database” and “optimize” keywords in its title or description.
I got the list of related plugins using ChoosePlugin.com service. I sorted plugins list in the descending order of total downloads quant:

I installed and tried every plugin from the list above on the test site with WordPress version 3.4.2. As the result I published for you some screenshots and videos of plugins in action. Those staff is available from the links to the right of every plugin above.
All reviewed plugins are devided on two groups: 1st, makes MySQL database tables optimization only; 2nd, makes WordPress database content cleanup, deletes some unneeded records and only then makes the same MySQL database tables optimization.

Is there a Miracle? Do those plugins make something magic during database optimization process? Does something really complex or sophisticated take place? What is hidden behind a scene? Nothing superior or mysterious. All plugins just propose the accurate wrapper for standard MySQL command:

  OPTIMIZE TABLE <table_name>;

You see, that all mystery is no mystery at all – optimization is done by MySQL itself. According to MySQL user manual:
“OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly.”

There is no need to know SQL in order connect to the WordPress database via phpMyAdmin, MySQLWorkBench or other tool you prefer and execute this command manually against selected database tables, e.g. “posts” or “options” table:

  OPTIMIZE TABLE wp_posts;
  OPTIMIZE TABLE wp_options;

Yes, much time is needed to apply optimization command against all database tables, and, Yes, plugins help good here as one touch command execution wrapper, but it’s not necessary. Execute command like

  SHOW TABLE STATUS WHERE Data_free/Data_length>0.1;

where 0.1 is 10% level of free space inside total space allocated for selected table, and you will get list of tables which probably need optimization, look on screenshot:

show table status

show table status

Logically all is fine. We have some non-optimized things at the database. We have optimization tool, built into database server itself, in order to fix that. Why do not use it? Excellent, we use it, all things become optimized and we are happy. Are you happy? I’m not. Why? I didn’t note any change in my database queries productivity. Why? Because of on the small and even middle sized databases things discussed above are not critical. MySQL is a very powerfull RDBMS (relational database management system). It is developed to work with data sets consisted of hundred thousands even billion records. How much post average blogger writes a year: 52 if he publishes new post once a week, 104 if – twice a week, 365 – if – every day. Do you catch the idea?
Finally, all those optimizations hints and toys are important and do matter for the really large databases only. Thus, is you manage 1000+ blogs multi-site WordPress network and grow – use it. If you are one of thousands average sized blog owners with <500 posts a year, you can use (at least once a year) or not use it. I think those 0.01 of a second you can win in speed is not the thing you are searching for in the endless pursuit for speed, optimization and, as the result - traffic. Other side of the coin: table space defragmentation leads reduces its size. Space allocation for new added recoreds is always slower then old space reuse. Thus only practice, measurements and experiment could show you, if you need database optimization really or not. Do you need more information on the database optimization necessity? Read this useful post “How often should you use OPTIMIZE TABLE?”.

Conclusion and final recomendation: If you decide to be optimized, use plugin, which does a lot of work for you at once, plugin like rvg-optimize-database: Optimize Database after Deleting Revisions, to cleanup and optimize WordPress tables. As addition use plugins which help you to clear trash from uninstalled plugins: Plugins Garbage Collector or WPDBSpringClean.
As for me, I prefer “Plugins Garbage Collector”. I developed this plugin myself, but it’s not the only reason. “Plugins Garbage Collector” gives you more detailed information about database tables, which are used by active or forgotten by de-installed plugins. Other additional argument – it doesn’t duplicate database optimization functionality, which you have already in “Optimize Database after Deleting Revisions” plugin.

Thanks for reading.

Tags: ,