Drupal defaults to a node list on the front page based upon the fields promote and status, then ordered by sticky and created:

SELECT n.nid, n.sticky, n.created FROM node n WHERE n.promote = 1 AND n.status = 1 ORDER BY n.sticky DESC, n.created DESC LIMIT 0, 10

This query results in a file sort on MySQL:

I recommend we add a new index: promote, status, sticky, created (in that order to follow ltr semantics of MySQL indexes). This removes the filesort from the query and improves efficiency, especially on systems with a large number of nodes. I'm reluctant to say we should add more indexes to {node}, but seeing as the front page is generally the most heavily viewed page, this should be very beneficial.

CommentFileSizeAuthor
#17 front_page_index-295283-17.patch1.06 KBAlbert Volkman
#9 295283-index-frontpage-view.patch1.29 KBDamien Tournoud
#8 295283-index-frontpage-view.patch0 bytesDamien Tournoud
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Jamie Holly’s picture

Jamie Holly CreditAttribution: Jamie Holly commented

Clicked the wrong link grrr. If there is interest in this, I will gladly submit a patch.

NancyDru’s picture

NancyDru
she/her/hers
Primary language English
Location Boston
CreditAttribution: NancyDru commented

There are already indexes on promote+status and created. Admittedly, there is not on sticky. How much more would this buy?

Jamie Holly’s picture

Jamie Holly CreditAttribution: Jamie Holly commented

It really depends upon the number of nodes in the table. With the current indexes, a filesort if performed. Adding the index I propose eliminates that filesort.

Here's the execution time and explain without the new index (on a table with just under 30,000 nodes):

0.3429 sec

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	n 	ref 	node_promote_status,node_status_type 	node_promote_status 	8 	const,const 	22210 	Using where; Using filesort

And with my proposed index:

0.0013 sec

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	n 	ref 	node_promote_status,node_status_type,promote 	promote 	8 	const,const 	21548 	Using where

(this was ran on my test server with all MySQL caching turned off)

Considering the front page is generally the most viewed page of any website, the benefit will be high and the MySQL server will be much happier.

Damien Tournoud’s picture

Damien Tournoud CreditAttribution: Damien Tournoud commented

An index on sticky would probably do the job, and should take much less space than adding a new full index.

Damien Tournoud’s picture

Damien Tournoud CreditAttribution: Damien Tournoud commented

Ok, no of course it doesn't.

Why not extending the node_promote index to add sticky and create?

killes@www.drop.org’s picture

killes@www.drop.org CreditAttribution: killes@www.drop.org commented

Since d.o is a very old website, its schema might not correspond to any released Drupal version:

CREATE TABLE `node` (
`nid` int(10) unsigned NOT NULL auto_increment,
`type` varchar(32) NOT NULL default '',
`title` varchar(128) NOT NULL default '',
`uid` int(10) NOT NULL default '0',
`status` int(4) NOT NULL default '1',
`created` int(11) NOT NULL default '0',
`comment` int(2) NOT NULL default '0',
`promote` int(2) NOT NULL default '0',
`moderate` int(2) NOT NULL default '0',
`changed` int(11) NOT NULL default '0',
`sticky` int(2) NOT NULL default '0',
`vid` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`nid`,`vid`),
UNIQUE KEY `vid` (`vid`),
KEY `uid` (`uid`),
KEY `node_title_type` (`title`,`type`(4)),
KEY `node_moderate` (`moderate`),
KEY `node_promote_status` (`promote`,`status`),
KEY `node_type` (`type`(10)),
KEY `node_created` (`created`),
KEY `node_changed` (`changed`),
KEY `node_status_type` (`status`,`type`,`nid`),
KEY `nid` (`nid`),
KEY `tracker_user` (`uid`,`status`,`changed`),
KEY `tracker_global` (`status`,`changed`),
KEY `node_status_type_uid` (`status`,`type`,`nid`,`uid`)
)

Jamie Holly’s picture

Jamie Holly CreditAttribution: Jamie Holly commented

That would probably work best. I don't see any other place where just the promote and status indexes are used.

Damien Tournoud’s picture

Damien Tournoud CreditAttribution: Damien Tournoud commented
Status: Active » Needs review
FileSize
295283-index-frontpage-view.patch0 bytes

Here is a patch for this: we drop the existing node_promote_status and recreate a node_frontpage index that extends it to properly index the frontpage query.

Some benchmarking; a node table with 25,000 entries, of which 12,500 are promoted. No Drupal caching + MySQL query cache disabled, ab -n 100 on the frontpage:

- Without the patch: Requests per second: 3.70 [#/sec] (mean)
- With the patch: Requests per second: 4.38 [#/sec] (mean)

Damien Tournoud’s picture

Damien Tournoud CreditAttribution: Damien Tournoud commented
FileSize
295283-index-frontpage-view.patch1.29 KB
moshe weitzman’s picture

moshe weitzman CreditAttribution: moshe weitzman commented
Status: Needs review » Reviewed & tested by the community

Simple patch, with benchmarks. RTBC.

Dries’s picture

Dries CreditAttribution: Dries commented
Status: Reviewed & tested by the community » Fixed

Committed to CVS HEAD. Thanks.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.

Damien Tournoud’s picture

Damien Tournoud CreditAttribution: Damien Tournoud commented
Version: 7.x-dev » 6.x-dev
Status: Closed (fixed) » Patch (to be ported)

This is useful on 6 too ;)

Gábor Hojtsy’s picture

Gábor Hojtsy
he/him
Primary language Hungarian
Location Hungary
CreditAttribution: Gábor Hojtsy commented

I am a little bit hesitant to modify the schema in the stable branch, but it does sound it makes a lot of sense, and the benchmarks sound perfect. Should only be a problem for those using index hints in MySQL queries, which should hopefully be a *very* limited number of people.

NancyDru’s picture

NancyDru
she/her/hers
Primary language English
Location Boston
CreditAttribution: NancyDru commented

Absolutely this is beneficial, even without the promote column (and maybe better without). I was investigating performance problems on my 6.17 site and [re-] discovered that the standard front-page query was taking almost 30 ms (with 8,000 nodes, limit 10) to run. Adding an index on status, sticky, created (in that order) reduced the query to 0.2 ms (better than an order of magnitude reduction). Granted, this is only one performance issue that I found, but a significant one. Since many, if not most, node queries are on those same columns, I would expect to see many benefits throughout my site, not just the front page. Just for grins, I also increased the limit to 100 and the query still ran in under 5 ms.

NancyDru’s picture

NancyDru
she/her/hers
Primary language English
Location Boston
CreditAttribution: NancyDru commented

Just a follow up. SELECT n.nid, n.sticky, n.created FROM node n WHERE n.status = 1 ORDER BY n.sticky DESC, n.created DESC LIMIT 0, 100 (isn't this a fairly standard node query?) run with the index containing "promote" took 23.3 ms, whereas removing "promote" it took 0.1 ms. Adding "promote = 1" to the query gave virtually the same timing. I vote for removing "promote" from the index.

Albert Volkman’s picture

Albert Volkman CreditAttribution: Albert Volkman commented
Status: Patch (to be ported) » Needs review
FileSize
front_page_index-295283-17.patch1.06 KB

Not sure if we're interested in removing 'promote' from the index (or any extraneous ramifications spurred from that), but here is a straight backport for D6.

NancyDru’s picture

NancyDru
she/her/hers
Primary language English
Location Boston
CreditAttribution: NancyDru commented

Looks good to me.

Status: Needs review » Closed (outdated)

Automatically closed because Drupal 6 is no longer supported. If the issue verifiably applies to later versions, please reopen with details and update the version.