MySQL with slow queries I have a website running on Wordpress Multisite + Woocommerce with over 5700 users on a Nginx + Apache server. I recently experienced some down time but didn’t get any errors, just a blank page.

I contacted my hosting company (WP Engine) and they said the following:


  your site is suffering from slow queries being run on tables with too
  many rows.


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Name                           Rows
wp_users                       6046
wp_groups_user_group           6867
wp_rg_form_view                7657
wp_posts                       12574
wp_rg_lead_detail              12925
wp_woocommerce_order_items     13510
wp_term_relationships          14581
wp_options                     17169
wp_comments                    57730
wp_commentmeta                 65568
wp_usermeta                    176243
wp_woocommerce_order_itemmeta  278636
wp_postmeta                    491916
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 



  The reason the errors are happening is because the queries on the
  above tables are not completing fast enough. This is due to excessive
  table rows. Here is an example query that is having the issue:


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# Time: 140219 13:00:28
# User@Host: Websitename @ localhost [127.0.0.1]
# Thread_id: 3948879  Schema: wp_dbname  Last_errno: 0  Killed: 0
# Query_time: 2.394413  Lock_time: 0.000171  Rows_sent: 1  Rows_examined: 510794  Rows_affected: 0  Rows_read: 510794
# Bytes_sent: 89
SET timestamp=1392814828;
SELECT COUNT( order_items.order_item_id )
        FROM wp_woocommerce_order_items as order_items
        LEFT JOIN wp_woocommerce_order_itemmeta AS itemmeta ON order_items.order_item_id = itemmeta.order_item_id
        LEFT JOIN wp_postmeta AS postmeta ON order_items.order_id = postmeta.post_id
        LEFT JOIN wp_term_relationships AS rel ON postmeta.post_id = rel.object_ID
        LEFT JOIN wp_term_taxonomy AS tax USING( term_taxonomy_id )
        LEFT JOIN wp_terms AS term USING( term_id )
        WHERE   term.slug IN ('completed','processing','on-hold')
        AND     tax.taxonomy        = 'shop_order_status'
        AND     (
                    (
                        itemmeta.meta_key = '_variation_id'
                        AND itemmeta.meta_value = '1261'
                    ) OR (
                        itemmeta.meta_key = '_product_id'
                        AND itemmeta.meta_value = '1261'
                    )
        )
        AND     (
                    (
                        postmeta.meta_key = '_billing_email'
                        AND postmeta.meta_value IN ( 'john@email.com' )
                    ) OR (
                        postmeta.meta_key = '_customer_user'
                        AND postmeta.meta_value = '2936' AND postmeta.meta_value &gt; 0
                    )
                )
     /* From [example.com/product/example_product/] in [example.com/wp-content/plugins/woocommerce/woocommerce-core-functions.php:1862] */;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


And finally they suggested for me to work with my database/web developer to slim the database and this will speed the queries up and allow them to work properly.

As I am not database expert, I just wanted to ask here - is what WP Engine recommended to me reasonable? I ask because I know there are many websites running on much bigger databases that are larger than mine and they're working properly! That being said, Could it possibly be that WP Engine just has slow server processing or should I do what they recommended?

Thanks
If you are using MySQL v5.6 because Performance_Schema has lot of metadata about your query/table and how the processing of a query happened (almost everything that is helpful for you to debug a problem/query internals)


You can have a look at my existing answer here:
Is there a way to get slow-query-log-like profiling information on a single query?


Otherwise, You can try Show Profiling functionality of MySQL to find out where most of the time has been taken by a query execution and optimize based on result:

set profiling=1;

Run your slow query (eg SELECT * FROM table WHERE name='xxx';

SHOW PROFILES;


http://dev.mysql.com/doc/refman/5.0/en/show-profile.html

Analyze EXPLAIN EXTENED output and optimize query accordingly and profile the query again to see gain in performance.