Question 166774 in Wordpress Question posted in
The official Wordpress documentation can be found here.

How can I speed up MySQL queries on WordPress tables with millions of rows?

I have 2 big tables in my database (wp_frm_items with about 3 million rows and wp_frm_item_metas with about 35 million rows).

I’m using the Query Monitor WordPress plugin and noticed that queries using joins and making counts are slow.

Some queries were taking more than 8s to finish! I fixed that by adding DISTINCT so that the query execution time is reduced into about 2s!

But still it is somehow slow for the page firing the queries to load fully.

I have 3 slow queries as detected by the Query Monitor.

1.6813s

SELECT DISTINCT it.item_id
FROM wp_frm_item_metas it
LEFT OUTER JOIN wp_frm_fields fi
ON it.field_id=fi.id
INNER JOIN wp_frm_items e
ON (e.id=it.item_id)
WHERE fi.id=110
AND ( it.meta_value='jack' )

2.7120s

SELECT COUNT(*)
FROM wp_frm_items it
LEFT OUTER JOIN wp_frm_forms fr
ON it.form_id=fr.id

1.1344s

SELECT id
FROM wp_frm_items
WHERE form_id=10

I had added indexes (BTREE) on all columns that I’m using in my queries, but still queries are slow! I’m using InnoDB.

How can I optimize the query execution time in my scenario?

I had read this article https://www.percona.com/blog/2007/11/01/innodb-performance-optimization-basics about tuning some parameters like innodb_buffer_pool_size=50G (about 80% of the server 64GB RAM), innodb_flush_log_at_trx_commit=2, and innodb_flush_method=O_DIRECT but the queries get more slower with these parameters!

How to know what is the best values for tuning the InnoDB performance?

This is my current /etc/my.cnf file:

[mysqld]
innodb_file_per_table=1
default-storage-engine=InnoDB
performance-schema=0
max_allowed_packet=268435456
open_files_limit=40000
secure-file-priv = ""
Tags:

2

Answers


  1. 0

    Amend your query as:

    SELECT DISTINCT it.item_id
           FROM wp_frm_item_metas it
           JOIN wp_frm_fields fi
             ON it.field_id = fi.id
           JOIN wp_frm_items e
             ON e.id = it.item_id
          WHERE fi.id = 110
            AND it.meta_value = 'jack'

    And remove all indexes, except the PRIMARY KEYs, and add a composite index on some combination of (it.field_id,it.item_id,it.meta_value);

    Login or Signup to reply.
  2. 0

    Please provide SHOW CREATE TABLE for each table.

    The last query would probably benefit if wp_frm_items had INDEX(form_id)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search