Lack of an Index on wp_posts.guid Slowing Down Queries

[waldojaquith]

I use both the FeedWordPress plug-in, plus some code of my own devising, which rely on the GUID field in the wp_posts table. In the case of FeedWordPress, the GUID is used to store the original URL of posts syndicated from third-party websites. On blogs with large numbers of posts (two of mine have over 150k) that rely on the GUID as a unique identifier for SQL queries, performance becomes enormously slow. That's because there is no index on GUID, despite its intended role as a unique identifier. The same problem manifests itself, regardless of FeedWordPress, with any large WordPress install with queries that are premised on posts' GUID.

Suggested fix: Create an index on wp_posts.guid by default, as a part of core.

[ryan]

Can you share some specific queries so we can EXPLAIN them to see what the best index or set of indexes would be.

[waldojaquith]

Well, sure, but I don't think you'll find that they help much—they're quite simple, as I described them.

SELECT id, post_modified_gmt
FROM wp_posts
WHERE guid="http://www.example.com/blog/123/"

And:

UPDATE wp_posts
SET post_author = "Jon Doe",
post_content = "Best stovepipes ever! Let me tell you all about them...",
post_title = "Acme Stovepipe Co.",
post_modified = "2008-08-31 08:00:00"
WHERE guid="http://www.example.com/blog/123/"

As you can see, there's only one viable index.

[ryan]

Just wondering if you had an ORDER BYs.

[waldojaquith]

Ah, that is a good question, Ryan. There are at least a couple of instances of that, in fact, such as this one from FeedWordPress:

SELECT post_date_gmt
FROM wp_posts
WHERE post_status = 'publish'
ORDER BY post_date_gmt DESC
LIMIT 1

I do intend to create an index on post_date_gmt within my own databases, but I'm not familiar enough with how that row is used within WordPress to be able to speak to the merits of modifying WP to do so as a matter of course.

[ryan]

Moving enhancements to 2.8.

[Denis-de-Bernardy]

please get the plugin's author to add the index from the plugin. this field isn't used in where clauses in WP, and the index would be useless for other users -- but still goggle up db space.

[pento]

#41223 was marked as a duplicate.