WordPress.org

Make WordPress Core

Opened 6 years ago

Closed 5 years ago

#7633 closed enhancement (wontfix)

Lack of an Index on wp_posts.guid Slowing Down Queries

Reported by: waldojaquith Owned by:
Milestone: Priority: normal
Severity: normal Version: 2.6.1
Component: Optimization Keywords: guid, mysql
Focuses: Cc:

Description

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.

Change History (7)

comment:1 ryan6 years ago

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

comment:2 waldojaquith6 years ago

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.

comment:3 ryan6 years ago

Just wondering if you had an ORDER BYs.

comment:4 waldojaquith6 years ago

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.

comment:5 ryan6 years ago

  • Milestone changed from 2.7 to 2.8

Moving enhancements to 2.8.

comment:6 FFEMTcJ5 years ago

  • Milestone changed from 2.8 to Future Release

comment:7 Denis-de-Bernardy5 years ago

  • Milestone Future Release deleted
  • Resolution set to wontfix
  • Status changed from new to closed

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.

Note: See TracTickets for help on using tickets.