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

Question

I'm trying to tune a wordpress website which suffers slow loading times, and I found out that the home page seems to take a lot more time to load. It's not due to content because I'm just considering the time it takes for the base request to end (viewable via firebug in firefox).

Also I tried copying the index.php code in a custom page, and the same exact code loads in about 1 sec while the main home loads in about 7. I noticed that single pages loaded faster, and at first I thought it was due to the difference in content, but after this test I'm not sure what's causing this.

Is there much stuff that wordpress does behind the scenes only for the main index? Is there any other way to explain this situation and, more importantly, fix it so that the home page loads faster?

UPDATE -- DIRTY SOLUTION

After a lot of blind tries, I created a new page called home which uses index.php as custom template (not a copy, the same file). I redirected any call to the base path to it (via wordpress' internal rewrite) and I have the same homepage as before, just loaded in 1/6th of the time. While I'm happy with the result, I'd really like to understand what's going on.

ANOTHER UPDATE

So the point seems to be that I cannot use a dynamic (in wordpress' sense) page with this site, it only works fine with a custom "static" page where I insert content via various functions, the normal Loop makes the home either very slow (with high memory limit) or just blank (low memory limit, script fails).

As suggested in this question, I created a static home linked to a custom page and it works fine. I also created a blog page (again with a custom template) which also works fine (where "fine" means it shows my empty test page containing just one word and no code) unless I specify it as "Posts page" in admin -> Reading settings. In other words it looks like as soon as wordpress sees a dynamic page (the one that's supposed to hold The main Loop) it does something very heavy which eats up a lot of ram.

Still looking for the cause of this, I can work around it but I'd really like to understand what the problem is.

Edit: added bounty

More info: I tried disabling all plugins, wordpress is updated to the latest version.

FURTHER EDIT: TABLE INDEXES

wp_posts:

PRIMARY KEY  (`ID`),
KEY `type_status_date` (`post_type`,`post_status`(1),`post_date`,`ID`),
KEY `post_status_date_gmt` (`post_status`(1),`post_date_gmt`),
KEY `post_date` (`post_date`),
KEY `post_date_gmt` (`post_date_gmt`),
KEY `post_parent` (`post_parent`),
KEY `post_name` (`post_name`),
KEY `post_status` (`post_status`),
KEY `post_author` (`post_author`),
FULLTEXT KEY `post_related` (`post_name`,`post_content`),
FULLTEXT KEY `post_content` (`post_content`,`post_title`),

wp_term_relationships:

PRIMARY KEY  (`object_id`,`term_taxonomy_id`),
KEY `term_taxonomy_id` (`term_taxonomy_id`)

wp_term_taxonomy:

PRIMARY KEY  (`term_taxonomy_id`),
UNIQUE KEY `term_id_taxonomy` (`term_id`,`taxonomy`),
KEY `taxonomy` (`taxonomy`)

Answer 1

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.

Answer 2

By default there isn't any difference for performance of home page. There is however a possibility that some plugin does something slow on that page alone.

There are plenty plugins to profile WP performance. I usually use WP Tuner but it seems to be broken for latest WP version, so I have no immediate replacement to suggest.

Simplest way is packing template full of time/memory markers.

printf(  '%d queries in %.3f seconds, using %.2fMB memory', get_num_queries(), timer_stop( 0, 3 ), memory_get_peak_usage() / 1024 / 1024 );

It's crude but often allows to pinpoint location where slowdown occurs.

Answer 3

After almost 4 years I got back on this and finally found the problem. Turns out the site had a lot of articles ALL marked as sticky. Due to the unbelievably dumb way wordpress uses to mark sticky posts (a serialized array in wp_options), the main loop of the dynamic home page took an incredibly long time. Clearing the sticky_posts field in the table fixed the problem.

Answer 4

If the home page is taking that long to load, most likely you have a plugin or a function in the theme that is making a remote request some time when the home page is rendering.

I would do a recursive search through your wp-content directory for calls to 'wp_remote_' to look for any functions that may be causing this.

Answer 5

At first, check the quereis of WOrdPress and the included images, scripts and stylesheets. You can check the queries with the plugin Debug Queries and you become more information about your install and mistakes with the plugin Debug Objects.