Add additional indexes/keys to database
-
Add index on more table and columns:
wp_users wp_terms wp_posts => latest_posts wp_posts => sitemaps wp_posts => guid wp_usermeta wp_term_relationships wp_term_taxonomywp_posts => guid is one which makes a big difference.
-
Hi, about these requests.
> wp_users
V1.4.1 added an index on display_name. The other indexes seem to be solid, at any rate for the query pattern’s we’ve seen.
> wp_usermeta
We have added indexes to help with usermeta lookups.
> wp_terms, wp_term_relationships, wp_term_taxonomy`
Thes tables’ WordPress standard indexes look adequate.
> wp_posts => latest_posts, => sitemaps
We’ve added and adjusted a few wp_posts indexes that hopefully help with the generation of lists of recent posts, and generation of sitemaps. They function well for the query patterns we’ve seen.
> wp_posts => guid
We have not seen any query patterns that filter with `WHERE wp_posts.guid = ‘something’. There’s little point in adding indexes unless the columns are used for filtering. (If a query is very heavily used, sometimes a covering index can help. But that doesn’t seem to be the case with queries that need the guid column.)
The monitor you sent doesn’t show problems in any of the areas you mentioned.
As always, please run another monitor and do the problematic operations in your site. Monitors now have Upload buttons as well as Save as .csv buttons.
Thanks for your feedback and your interest!
wp_users
V1.4.1 added an index on display_name. The other indexes seem to be solid, at any rate for the query pattern’s we’ve seen.
wp_usermeta
We have added indexes to help with usermeta lookups.
wp_terms, wp_term_relationships, wp_term_taxonomy
Thes tables’ WordPress standard indexes look adequate
Many thanks will have a look at the new version.
wp_posts => latest_posts, => sitemaps
We’ve added and adjusted a few wp_posts indexes that hopefully help with the generation of lists of recent posts, and generation of sitemaps. They function well for the query patterns we’ve seen.
Yoast sitemaps are usually slow once growing and using many languages on a multilingual site with WPML or polylang. Did you test with yoast and wpml and polylang. Haven’t tested latest version just know there is usally a problem and slow once the site gets big.
wp_posts => guid
We have not seen any query patterns that filter with `WHERE wp_posts.guid = ‘something’. There’s little point in adding indexes unless the columns are used for filtering. (If a query is very heavily used, sometimes a covering index can help. But that doesn’t seem to be the case with queries that need the guid column.)
The monitor you sent doesn’t show problems in any of the areas you mentioned.
Hmm strange, this is a GUID problem from Oxygen page builder in their ACF integration. Oxygen builder have SQL problems it used to crash the server as it hit the database so frequently and didn’t group the calls. And some extra addons to Oxygen is using heartbeat so it adds extra stress on the database.
See the code in this file https://pastebin.com/qNxZvxfu and line 31. The problem is it’s trying to get a row for a URL using the GUID column but it’s searching LIKE ‘%Shttps://….’
Don’t this mean a full table scan on the GUID?
Oxygen builder support have been notified as it would be better if they manage to do the same with = instead of LIKE. But my hopes are low that they in the nearest future will solve that.As always, please run another monitor and do the problematic operations in your site. Monitors now have Upload buttons as well as Save as .csv buttons.
I did run above GUID query if I remember it loading template frontend and in the builder to trigger this one. But maybe I need to drop other indexes as well fully and even disable all indexes from your plugin as well to show it.
Thanks for your feedback and your interest!
No worry it’s mutual, I benefit of the fixes.
We haven’t tested with Yoast, WMPL, or Polylang. We’ll try to figure out how to do that. It involves getting access to some largeish sites using them. The first focus has been on WordPress’s core tables.
As for that Oxygen query
SELECT *
FROM $wpdb->posts
WHERE guid LIKE ‘%https://example.com/whatever/the/xxxx’;”Oh, my my my. Say it isn’t so! That’s frighteningly bad code to find in a commercial software product.
LIKE '%something'always means a full scan in MySQL / MariaDb, unless there are other WHERE clauses to narrow it down. There aren’t here. (postgreSQL has a “trigram” index type to make that kind of WHERE filter fast; maybe MySQL or MariaDB will add something similar one day.)I suppose an index on the guid column would help, but only on newer versions of MySQL / MariaDB. It would help because MySQL could do a full index scan to satisfy that query rather than a full table scan. Doing an index scan would reduce I/O.
And, it gets worse. They do
SELECT *and then only use a couple of columns. That rules out using a covering index, and wastes network bandwidth and RAM all over the place.In the meantime, WP Total Cache has a decent query-result cache. It might be worth a try, to avoid repeating that kind of rubbish query. But, they disable it on the backend (dashboard etc) by default and warn us that things might get wakky if we enable it.
- The topic ‘Add additional indexes/keys to database’ is closed to new replies.
