I have hired someone to look at the database and this is his comments:
==========================================
so, basically your query is processing too many records in a couple of tables wp_posts(1’980’935 records) and wp_postmeta(10’818’678). It matches and returns 57’639 records.
The core query is:
select SQL_NO_CACHE wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND (wp_posts.ID = 515125 OR wp_posts.post_parent = 515125);
which returns 399’291 record for 2.1s
which is a huge join
Then the extra “where” conditions add extra time. The query bellow:
select SQL_NO_CACHE wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND (wp_posts.ID = 515125 OR wp_posts.post_parent = 515125) AND wp_postmeta.meta_key = ‘_bbp_forum_id’;
reduces the returned records to: 57’639 and in fact the rest of the where statements do not change the final result but are only adding more time for processing.
The query takes about 3s.
Both queries above are well indexed and are execucuted in the optimal way.
With this amount of returned records matched against the 2 big tables (~11mln and ~2mln records in each) executon time of less than 3s can not be achieved.
is this forum using a custom php code developed in house?
ME: It is using bbPress, which runs on WordPress
the thing is, this code is not optimal in term of database design
because it’s using a couple of wordpress general storage tables
to store the forum posts and threads
as you can guess this 2 tables have a lot more data in it along the forum data
from other hand the query you sent has too many “where” conditions
which are not relevant at all
AND CAST(wp_postmeta.meta_value AS SIGNED) NOT IN (‘515120′,’515123’) ) )
AND wp_posts.post_type IN (‘topic’, ‘reply’)
AND (
(wp_posts.post_status = ‘publish’ OR (wp_posts.post_status = ‘pending’ AND wp_posts.post_author = 0) OR wp_posts.post_status = ‘closed’ OR wp_posts.post_status = ‘hidden’)
OR
(wp_posts.post_author = 0 AND (wp_posts.post_status = ‘private’))
)
non of this are changing the final result at all, but are slowing the core query with extra time
in short – there is not an easy “magic” fix in the database side
as I said the core query is well indexed and runs optimal by the SQL engine
the only thing can be done is to change the query a bit
but we can hardly get it under 2.5s
and this will require PHP code changes
ME: What would be your suggestion to achieve a similar performance with database?
get rid of the WP
the WP pluggin you use is a forum implementation on top of WP
understand that, but you are expecting something by this WP pluggin that it can’t provide
with storring everyhing in a couple of tables – that can’t be done
won’t even comment:
AND CAST(wp_postmeta.meta_value AS SIGNED) NOT IN (‘515120′,’515123’) ) )
this part
wp_postmeta | CREATE TABLE wp_postmeta (
meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
post_id bigint(20) unsigned NOT NULL DEFAULT ‘0’,
meta_key varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
meta_value longtext COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (meta_id
)
as you can see meta_value field is a longtext!!!
you can imagine how can you filter by this field
without index
scanning millions of records by a field that holds huge strings
in same table wp stores all sessions, etc..
just to imagine how much extra data is stored in wp_postmeta table
here is a some stats by meta_key and the number of records (top 20 or so):
| external_image_id | 100828 |
| external_place_lat | 100845 |
| external_place_lng | 100862 |
| external_upload_batch | 110848 |
| amazonS3_cache | 121546 |
| _bbp_activity_id | 199233 |
| filter | 239951 |
| id_field | 239952 |
| __defaults_set | 239974 |
| _bbp_post_id | 1418227 |
| _bbp_topic_id | 1426899 |
| _bbp_author_ip | 1426899 |
| _bbp_forum_id | 1426909 |
| _barcelona_vote_up | 1732202 |
| _barcelona_views | 1732206 |
in fact this WP pluggin is using mysql as a simple storage and not as a relational DB
as I said above it’s a bad DB design problem
because it’s a just a pluggin/extension of a CMS system
==========================================================
Basically he is telling me that we won’t be able to achieve good loading time, because it is running on WordPress and the database design is a problem.
What do you think, is there hope?
We run a forum with 1.4 million posts and almost 100k members, if it can give you an idea.
