Split database on large site?

I had a similar issue on a website with ~100k rows in wp_posts and ~500k in wp_postmeta.

Truth is WordPress have some really slow queries in the admin when you start to have a lot of posts.

The best way to find the bottleneck is to use the Debug Bar plugin.

You will probably find out some crazy slow queries like the following:

SELECT DISTINCT meta_key
FROM lc_postmeta
WHERE meta_key NOT BETWEEN '_' AND '_z'
HAVING meta_key NOT LIKE '\\_%'
ORDER BY meta_key
LIMIT 30

SELECT ID
FROM lc_posts
WHERE post_type="attachment"
AND post_mime_type LIKE 'audio%'
LIMIT 1

and

SELECT ID
FROM lc_posts
WHERE post_type="attachment"
AND post_mime_type LIKE 'video%'
LIMIT 1

These are known issues unfortunately but you can tweak them to load faster.

For example i simply removed the custom fields metabox which i didn’t use and gain 50% load time.

add_action( 'admin_menu' , 'pu_remove_post_custom_fields' );

function pu_remove_post_custom_fields() {
    remove_meta_box( 'postcustom' , 'post' , 'normal' ); 
}

I am not sure splitting your DB into multiple read instances would help.

Related Posts: