Performance Tweaks for Big WordPress Sites

Performance Tweaks for Big Wordpress Sites

Posted:  July 2, 2014

Performance Tweaks for Big Wordpress Sites

Tip #1

We backed up the the wp_posts table and then used a simple MySQL command to remove old post revisions. This decreased the table size from 400MB to 120MB:

DELETE FROM `wp_posts` WHERE post_type = 'revision' AND post_date NOT LIKE '2012-%'

Long-term solution

There are WordPress plugins which can limit the number of revisions per post. We think that the WordPress code should be improved and the revisions should be stored in a different table to maximize speed. You can support this on WordPress bug tracking.

Large number of comments

We need to be extra careful specially with the wp_comments table which has close to 400,000 comments and 320MB. We often find speed issues with MySQL queries which take only a couple of milliseconds on smaller sites.

WordPress 3.2 added a count of comments into the WordPress Admin Bar which shows up for logged in Administrators and Editors when browsing the site.

Counting the comments actually takes a lot of time on our big database. Here’s the query and it’s duration in log:

SQL query for counting of the comments

SELECT comment_approved, COUNT(*) AS num_comments FROM wp_comments GROUP BY comment_approved;

In the above case, it takes 0.3 seconds, while all the other queries are done in 0.05 – 0.001 seconds.

Tip #2

To do this kind of audits use WPDB Profiling plugin for WordPress. It shows you all sorts of information in the site footer and you can turn it on and off as you like.

Beware: this plugin turns off post revisions and autosaves when activated, you need to turn it off once you are finished or change it’s settings.
It’s clearly the slowest query when the site is loading. And this site has up to 20 editors who like to come to the site and read the comments to their articles, so it affects the performance. Keep in mind that this information is shown on multiple places in WordPress Admin Interface.

We created our own queries to do this count. It’s 5 queries instead of 1, but they are faster. Just try to test them:

SELECT COUNT(comment_ID) FROM wp_comments WHERE comment_approved = 'trash<span style="font-family: 'Lucida Grande','Lucida Sans Unicode',sans-serif;">'</span>
SELECT COUNT(comment_ID) FROM wp_comments WHERE comment_approved = 'spam'
SELECT COUNT(comment_ID) FROM wp_comments WHERE comment_approved = '0'<br />
SELECT COUNT(comment_ID) FROM wp_comments WHERE comment_approved = 'post-trash'<br />
SELECT COUNT(comment_ID) FROM wp_comments

The last query counts all the commands, so we subsctract the previous counts. Here are our results – 0.042144 seconds:

SQL query for counting of the comments improved

That’s a big improvement over 0.3 seconds duration with the standard query.

Tip #3

If you want to test above queries, replace SELECT with SELECT SQL_NO_CACHE to make sure no MySQL caching will be used.
We also opened a bug tracking ticket for this on WordPress Trac – Speeding up Dashboard and Comment moderation SQL load.

Finding slow database queries with MySQL tools

Another way of finding the slow queries is using this MySQL command. It’s best used when you have SSH access to the site.

  1. Watch the server load using top.
  2. When you see that mysql process is taking too much of the CPU, just try to list running queries with following command in MySQL console:
mysql> SHOW PROCESSLIST;

+-------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id    | User | db      | Time | State        | Info                                                                                                 |
+-------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 59462 | site | site_db |    0 | NULL         | SHOW PROCESSLIST                                                                                     |
| 61208 | site | site_db |   <strong>62</strong> | Sending data | SELECT ID FROM wp_posts AS a LEFT JOIN (SELECT post_id FROM wp_postmeta WHERE meta_key='...') AS b   |
| 61228 | site | site_db |   25 | Locked       | UPDATE `wp_postmeta` SET `meta_value` = '1327484262:39' WHERE `post_id` = 66955 AND `meta_key` = '_e |
| 61238 | site | site_db |   16 | Locked       | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (17992)                       |
| 61241 | site | site_db |   16 | Locked       | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (12931)                       |
| 61249 | site | site_db |   11 | Locked       | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (34465)                       |
| 61251 | site | site_db |   11 | Locked       | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (5209)                        |
| 61257 | site | site_db |    6 | Locked       | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (34465)                       |
| 61258 | site | site_db |    5 | Locked       | SELECT meta_id FROM wp_postmeta WHERE meta_key = '....' AND post_id = 24661                          |
| 61262 | site | site_db |    1 | Locked       | SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (5367)                        |
+-------+------+---------+------+--------------+------------------------------------------------------------------------------------------------------+

In the above list, there is some nasty query which is taking 62 (!) seconds to get executed and the other queries are just waiting until it’s finished. Yes, the site was in real trouble when the above list was saved.
There is also an MySQL option called “log slow queries”, but we newer had a success with it.
Here’s the query in it’s full beauty. Since it’s using subqueries, it’s slow and hard to optimize:

SELECT ID FROM wp_posts AS a LEFT JOIN (SELECT post_id FROM wp_postmeta WHERE meta_key='fb') AS b ON a.ID=b.post_id WHERE b.post_id IS NULL;

We re-coded the plugin and this bad query is no longer used. This was some weird query in some old plugin which was running as WordPress Cron job, so it’s not visible in WPDB Profiling which was described above.

Tip #4

If the query seems to complicated, try to move some of the processing into PHP.>

Use indexes!

If you see a slow query, try to use MySQL EXPLAIN command on it. Here’s the query:

SELECT count(*) FROM wp_comments AS c JOIN wp_posts AS p ON c.comment_post_ID = p.ID WHERE c.user_id = '1079' AND c.comment_approved = '1' AND p.post_status = 'publish' AND comment_content REGEXP '[[::]]' = 1

Here’s the output of the EXPLAIN command. Notice the big number in the “rows” column. It means that MySQL has to examine 377,606 rows in the wp_comments table – that’s all the comments on the site.

mysql> EXPLAIN SELECT count(*) FROM wp_comments AS c JOIN wp_posts AS p ON c.comment_post_ID = p.ID WHERE c.user_id = '1079' AND c.comment_approved = '1' AND p.post_status = 'publish' AND comment_content REGEXP '[:asdf:]' = 1;
+-------------+-------+--------+------------------------------------------------------------+------------------+---------+------------------------+--------+-------------+
| select_type | table | type   | possible_keys                                              | key              | key_len | ref                    | rows   | Extra       |
+-------------+-------+--------+------------------------------------------------------------+------------------+---------+------------------------+--------+-------------+
| SIMPLE      | c     | ref    | comment_approved,comment_post_ID,comment_approved_date_gmt | comment_approved | 62      | const                  | 377606 | Using where |
| SIMPLE      | p     | eq_ref | PRIMARY                                                    | PRIMARY          | 8       | site.c.comment_post_ID |    1   | Using where |
+-------------+-------+--------+------------------------------------------------------------+------------------+---------+------------------------+--------+-------------+
2 rows in set (0.00 sec)

The SQL query is using user_id in the WHERE clause. And there is no index which would contain this (see possible_keys in above output, you can also execute “SHOW INDEXES IN wp_comments;”).

So we create a new index which will combine two fields from the WHERE clause:

CREATE INDEX userid_approved_index ON `wp_comments` (`user_id`,`comment_approved`)

The table index size will increase a bit, but SQL will search only 1,423 rows when executing our query, because it’s able to use our new index:

mysql> EXPLAIN SELECT count(*) FROM wp_comments AS c JOIN wp_posts AS p ON c.comment_post_ID = p.ID WHERE c.user_id = '1079' AND c.comment_approved = '1' AND p.post_status = 'publish' AND comment_content REGEXP '[:asdf:]' = 1;
+-------------+-------+--------+----------------------------------------------------------------------------------+-----------------------+---------+----------------------------------+------+-------------+
| select_type | table | type   | possible_keys                                                                    | key                   | key_len | ref                              | rows | Extra       |
+-------------+-------+--------+----------------------------------------------------------------------------------+-----------------------+---------+----------------------------------+------+-------------+
| SIMPLE      | c     | ref    | comment_approved,comment_post_ID,comment_approved_date_gmt,userid_approved_index | userid_approved_index | 70      | const,const                      | 1896 | Using where |
| SIMPLE      | p     | eq_ref | PRIMARY                                                                          | PRIMARY               | 8       | mondoweiss_net.c.comment_post_ID |    1 | Using where |
+-------------+-------+--------+----------------------------------------------------------------------------------+-----------------------+---------+----------------------------------+------+-------------+

Tip #5

When you create a plugin which uses custom SQL queries, have look at the tables and make sure the fields which you used in WHERE or ORDER BY statements are indexed.

Add more indexes to the table if necessary (but then be careful if you ever reinstall WordPress).
Another example would be sorting comments by comment_date. comment_date field is not indexed, but comment_date_gmt is. So use that one and your queries will be faster.

Kevin Pirnie

Kevin Pirnie

20+ Years of PC and server maintenance & over 15+ years of web development/design experience; you can rest assured that I take every measure possible to ensure your computers are running to their peak potentials. I treat them as if they were mine, and I am quite a stickler about keeping my machines up to date and optimized to run as well as they can.

Our Privacy Policy

Revised: June 8, 2021

Thank you for choosing to be part of my website at https://kevinpirnie.com (“Company”, “I”, “me”, “mine”). I am committed to protecting your personal information and your right to privacy. If you have any questions or concerns about this privacy notice, or my practices with regards to your personal information, please contact me at .

When you visit my website https://kevinpirnie.com (the “Website”), and more generally, use any of my services (the “Services”, which include the Website), I appreciate that you are trusting me with your personal information. I take your privacy very seriously. In this privacy notice, I seek to explain to you in the clearest way possible what information we collect, how I use it and what rights you have in relation to it. I hope you take some time to read through it carefully, as it is important. If there are any terms in this privacy notice that you do not agree with, please discontinue use of my Services immediately.

This privacy notice applies to all information collected through my Services (which, as described above, includes our Website), as well as, any related services, sales, marketing or events.

Please read this privacy notice carefully as it will help you understand what I do with the information that I collect.

1. WHAT INFORMATION DO I COLLECT?

Information automatically collected

In Short: Some information – such as your Internet Protocol (IP) address and/or browser and device characteristics – is collected automatically when you visit my Website.

I automatically collect certain information when you visit, use or navigate the Website. This information does not reveal your specific identity (like your name or contact information) but may include device and usage information, such as your IP address, browser and device characteristics, operating system, language preferences, referring URLs, device name, country, location, information about how and when you use our Website and other technical information. This information is primarily needed to maintain the security and operation of our Website, and for our internal analytics and reporting purposes.

Like many businesses, I also collect information through cookies and similar technologies.

The information I collect includes:
Log and Usage Data. Log and usage data is service-related, diagnostic, usage and performance information our servers automatically collect when you access or use our Website and which we record in log files. Depending on how you interact with us, this log data may include your IP address, device information, browser type and settings and information about your activity in the Website (such as the date/time stamps associated with your usage, pages and files viewed, searches and other actions you take such as which features you use), device event information (such as system activity, error reports (sometimes called ‘crash dumps’) and hardware settings).

Device Data. I collect device data such as information about your computer, phone, tablet or other device you use to access the Website. Depending on the device used, this device data may include information such as your IP address (or proxy server), device and application identification numbers, location, browser type, hardware model Internet service provider and/or mobile carrier, operating system and system configuration information.

Location Data. I collect location data such as information about your device’s location, which can be either precise or imprecise. How much information I collect depends on the type and settings of the device you use to access the Website. For example, I may use GPS and other technologies to collect geolocation data that tells me your current location (based on your IP address). You can opt out of allowing me to collect this information either by refusing access to the information or by disabling your Location setting on your device. Note however, if you choose to opt out, you may not be able to use certain aspects of the Services.

2. HOW DO I USE YOUR INFORMATION?

In Short: I process your information for purposes based on legitimate business interests, the fulfillment of my contract with you, compliance with my legal obligations, and/or your consent.

I use personal information collected via my Website for a variety of business purposes described below. I process your personal information for these purposes in reliance on my legitimate business interests, in order to enter into or perform a contract with you, with your consent, and/or for compliance with my legal obligations. I indicate the specific processing grounds I rely on next to each purpose listed below.

For other business purposes. I may use your information for other business purposes, such as data analysis, identifying usage trends, determining the effectiveness of our promotional campaigns and to evaluate and improve my Website, products, marketing and your experience. I may use and store this information in aggregated and anonymized form so that it is not associated with individual end users and does not include personal information. I will not use identifiable personal information without your consent.

3. WILL YOUR INFORMATION BE SHARED WITH ANYONE?

In Short: I only share information with your consent, to comply with laws, to provide you with services, to protect your rights, or to fulfill business obligations.

4. DO WE USE COOKIES AND OTHER TRACKING TECHNOLOGIES?

In Short: I may use cookies and other tracking technologies to collect and store your information.

I may use cookies and similar tracking technologies (like web beacons and pixels) to access or store information. Specific information about how I use such technologies and how you can refuse certain cookies is set out in our Cookie Notice.

5. IS YOUR INFORMATION TRANSFERRED INTERNATIONALLY?

In Short: We may transfer, store, and process your information in countries other than your own.

My servers are located in the United States of America, unless otherwise requested by my clients. If you are accessing my Website from outside, please be aware that your information may be transferred to, stored, and processed by me in my facilities and by those third parties with whom I may share your personal information (see “WILL YOUR INFORMATION BE SHARED WITH ANYONE?” above), in and other countries.

If you are a resident in the European Economic Area, then these countries may not necessarily have data protection laws or other similar laws as comprehensive as those in your country. I will however take all necessary measures to protect your personal information in accordance with this privacy notice and applicable law.

6. HOW LONG DO WE KEEP YOUR INFORMATION?

In Short: I keep your information for as long as necessary to fulfill the purposes outlined in this privacy notice unless otherwise required by law.

I will only keep your personal information for as long as it is necessary for the purposes set out in this privacy notice, unless a longer retention period is required or permitted by law (such as tax, accounting or other legal requirements). No purpose in this notice will require me keeping your personal information for longer than 6 months.

When I have no ongoing legitimate business need to process your personal information, I will either delete or anonymize such information, or, if this is not possible (for example, because your personal information has been stored in backup archives), then I will securely store your personal information and isolate it from any further processing until deletion is possible.

7. HOW DO WE KEEP YOUR INFORMATION SAFE?

In Short: I aim to protect your personal information through a system of organizational and technical security measures.

I have implemented appropriate technical and organizational security measures designed to protect the security of any personal information I process. However, despite our safeguards and efforts to secure your information, no electronic transmission over the Internet or information storage technology can be guaranteed to be 100% secure, so I cannot promise or guarantee that hackers, cybercriminals, or other unauthorized third parties will not be able to defeat my security, and improperly collect, access, steal, or modify your information. Although I will do my best to protect your personal information, transmission of personal information to and from my Website is at your own risk. You should only access the Website within a secure environment.

8. DO WE COLLECT INFORMATION FROM MINORS?

In Short: I do not knowingly collect data from or market to children under 18 years of age.

I do not knowingly solicit data from or market to children under 18 years of age. By using the Website, you represent that you are at least 18 or that you are the parent or guardian of such a minor and consent to such minor dependent’s use of the Website. If I learn that personal information from users less than 18 years of age has been collected, I will deactivate the account and take reasonable measures to promptly delete such data from my records. If you become aware of any data I may have collected from children under age 18, please contact me at .

9. WHAT ARE YOUR PRIVACY RIGHTS?

In Short: You may review, change, or terminate your account at any time.

If you are a resident in the European Economic Area and you believe I am unlawfully processing your personal information, you also have the right to complain to your local data protection supervisory authority. You can find their contact details here: http://ec.europa.eu/justice/data-protection/bodies/authorities/index_en.htm.

If you are a resident in Switzerland, the contact details for the data protection authorities are available here: http://ec.europa.eu/justice/data-protection/bodies/authorities/index_en.htm.

Cookies and similar technologies: Most Web browsers are set to accept cookies by default. If you prefer, you can usually choose to set your browser to remove cookies and to reject cookies. If you choose to remove cookies or reject cookies, this could affect certain features or services of my Website.

10. CONTROLS FOR DO-NOT-TRACK FEATURES

Most web browsers and some mobile operating systems and mobile applications include a Do-Not-Track (“DNT”) feature or setting you can activate to signal your privacy preference not to have data about your online browsing activities monitored and collected. At this stage no uniform technology standard for recognizing and implementing DNT signals has been finalized. As such, I do not currently respond to DNT browser signals or any other mechanism that automatically communicates your choice not to be tracked online. If a standard for online tracking is adopted that I must follow in the future, I will inform you about that practice in a revised version of this privacy notice.

11. DO CALIFORNIA RESIDENTS HAVE SPECIFIC PRIVACY RIGHTS?

In Short: Yes, if you are a resident of California, you are granted specific rights regarding access to your personal information.

California Civil Code Section 1798.83, also known as the “Shine The Light” law, permits my users who are California residents to request and obtain from me, once a year and free of charge, information about categories of personal information (if any) I disclosed to third parties for direct marketing purposes and the names and addresses of all third parties with which I shared personal information in the immediately preceding calendar year. If you are a California resident and would like to make such a request, please submit your request in writing to me using the contact information provided below.

If you are under 18 years of age, reside in California, and have a registered account with the Website, you have the right to request removal of unwanted data that you publicly post on the Website. To request removal of such data, please contact us using the contact information provided below, and include the email address associated with your account and a statement that you reside in California. I will make sure the data is not publicly displayed on the Website, but please be aware that the data may not be completely or comprehensively removed from all my systems (e.g. backups, etc.).

12. DO I MAKE UPDATES TO THIS NOTICE?

In Short: Yes, I will update this notice as necessary to stay compliant with relevant laws.

I may update this privacy notice from time to time. The updated version will be indicated by an updated “Revised” date and the updated version will be effective as soon as it is accessible. If I make material changes to this privacy notice, I may notify you either by prominently posting a notice of such changes or by directly sending you a notification. We encourage you to review this privacy notice frequently to be informed of how I am protecting your information.

13. HOW CAN YOU CONTACT ME ABOUT THIS NOTICE?

If you have questions or comments about this notice, you may email me at or by post to:

Kevin C. Pirnie

22 Orlando St.
Feeding Hills, MA 01030
United States of America

14. HOW CAN YOU REVIEW, UPDATE, OR DELETE THE DATA I COLLECT FROM YOU?

Based on the applicable laws of your country, you may have the right to request access to the personal information I collect from you, change that information, or delete it in some circumstances. To request to review, update, or delete your personal information, you may email me at or by post to:

Kevin C. Pirnie

22 Orlando St.
Feeding Hills, MA 01030
United States of America