Why is home (a lot) slower than other pages?

I beg to differ with the previous two comments.

Using a static home page results in WP using an index scan on the posts table’s primary key, vs an (oh so occasional) index scan on post_date, status or post_parent in the posts table.

In essence, the home page is dead slow because of the poor database design in WP. The schema has ludicrous multicolumn indexes on the taxonomy tables which MySQL simply ignores once you’ve a meaningful amount of posts. The fact that we’re using a table too much for taxonomies doesn’t help either.

In the database, safely add indexes on:

CREATE INDEX extra_posts ON posts (post_type,post_status,post_date DESC)
CREATE INDEX extra_term_rel ON term_relationships(term_taxonomy_id,object_id)
CREATE INDEX extra_term_tax ON term_taxonomy(taxonomy,term_taxonomy_id,term_id)

It won’t be perfect, but at least WP will be able to use index-based nested loop plans on your front page…

Oh, and… if you’re using any kind of custom post type on your front page, you also need to add:

posts(post_status,post_date DESC)

Else no index will be used at all for the main query because of the OR clauses.

Leave a Comment