Slow queries in “All Posts”

The issue is this part of the query:

AND wp_usermeta.meta_value != '0'

None of the meta_value columns in the three _meta tables have BTREE indexes because they’re defined as LONGTEXT. I’m sure you could add a FULLTEXT index and adjust the query accordingly, but that’s beyond the scope of this site. Note, however, that such an index would be very large, which could potentially cause both memory and disk space issues.

Another possible solution is to write some code that removed the Author meta box from the two pages where it’s causing an issue.

@diondesigns

Another possible solution is to write some code that removed the Author meta box from the two pages where it’s causing an issue.

1. Well it’s a core query running in the All Posts. Do you think it’s overridable?

2. If I set an index on meta_value would it be undone if I update WordPress core?

3. By the way I have a dedicated server with 32GB of Ram and I solved the problem by setting innodb_buffer_pool_size to 16G. It completely solved the problem and it took about 1G more of the server’s ram, not all of 16Gs. But it shouldn’t be the solution. I think it has to be solved by the WordPress guys in next updates.

If you add code that removes the Author meta box, the problematic query would go away since it is in the code of that meta box.

innodb_buffer_pool_size controls the size of the ibdata1 file, not how much memory is used by MySQL. Your results would say that you only needed to increase the value by roughly 2GB from its original setting.

Throwing enough memory at MySQL to cache all the table data and indices will relieve the symptoms, but as you said, it is not a cure. The cure is to fix the DB schema. The “meta” tables were designed for usage in 2005, not 2021. The meta_key column should go away, and the values of the current meta_key column should have their own column. In that way the vast majority of the new columns can be indexed since their values are known. And those that can’t be indexed…well, (poorly-written) plugins and themes that require TEXT/LONGTEXT meta values will get a bad reputation, and the practice will stop.

(Oh…and the autologin “session” stuff that WP stores in the _usermeta table should be split off into its own table — where it belongs.)

Thanks @diondesigns, I will try to write a plugin and if it ends with a right result, I’ll notice you here.

Regards

Here is a great solution that works perfectly:

add_action('init', 'remove_cpt_author');
function remove_cpt_author() {
	remove_post_type_support('page', 'author');
	remove_post_type_support('post', 'author');
}