Many sites need a way to browse content by "previous" and "next" links. For example, a photo gallery site would have the visitor view one image, and then offers links, or thumbnails with links, to the previous node or the next node.

This navigational aid engages the user more, and avoids the pitfalls of them getting lost by a less than obvious multi level navigation scheme.

There are several common ways for doing this "on the fly" via SQL, as in the following articles:

However, all these solutions work for small sites that either have a relatively low number of nodes, or do not have a high number of visitors.

Once you have a site with tens of thousands of nodes and/or hundreds of thousands of page views a day, the above schemes do not scale well.

The reason it does not scale is that the queries do not result in direct index lookups by key, and involve file sorting.

So a query like this:

SELECT n.nid FROM node n
    WHERE n.nid < 25627
    AND n.type in ('image', 'video')
    AND n.status = 1
    AND n.promote = 1
    ORDER BY n.nid DESC
    LIMIT 1;

Has this EXPLAIN:

  select_type: SIMPLE
        table: n
         type: ref
possible_keys: PRIMARY,node_type,node_promote_status,nid,nid_2
          key: node_promote_status
      key_len: 8
          ref: const,const
         rows: 24601
        Extra: Using where; Using filesort

That is too many rows ...

One of our clients has just this kind of web site: Ads of the World, a Graphics.com site by WebMediaBrands.

For a long time, the above query worked well, but as more nodes got added and the site's traffic increased problems started to show.

During peak hours, the site was suffering from a high load average, MySQL slow queries, and higher CPU utilization.

Our solution was a new module Previous/Next API module for Drupal. WebMediaBrands generously agreed to share this module with the community.

The difference is evident in this graph showing the number of slow queries during peak hours for Monday and Tuesday (4 and 5 May), vs. Wednesday and Thursday (6 and 7 May).

The graphs tell the story better than I can put it in words:

This is the MySQL slow query graph:

And the server's load average:

And CPU utilization:

Articles

Comments

Visitor (not verified)

Thu, 2009/05/07 - 21:44

What about custom pager? How do they compare?

http://drupal.org/project/custom_pagers

Khalid

Thu, 2009/05/07 - 22:11

The Previous/Next API has two goals. One overlaps with Custom Pagers, which is functionality. Custom Pager is richer in this regard.

However, it is the other goal where they are different: performance. Since Custom Pager uses views, it will suffer from the same scalability issues as standalone queries, the very problem that Previous/Next API is trying to solve.

Visitor (not verified)

Thu, 2009/05/07 - 22:28

very nice modules.
hope it can limit by cotent type. (Previous/Next link to same content type)

I'm thinking...
1. indexing node cotent type into database too
2. change function to prev_next_nid_next($nid , $node_content_type)

Thanks your sharing.

Visitor (not verified)

Fri, 2009/05/08 - 02:11

We recently built a catalog website (should be launched next week, so I can't publish the URL yet), in which we needed to have contextual previous/next buttons.

This catalog present products in several grouping options:
- as search results
- in a taxonomy based gallery
- as related products, in a product page (nodereference)
- etc.

The design showed a previous/next links in a product page. Now here's the questions: What is previous, which node comes next?
The answer was - previous/next are set by the referring list, and are not globally set. So if one is looking at a list of nodes (one of the above), and then clicks one of them, the adjacent nodes should be the ones the user saw in that list.

Since all the lists are generated by views, what we do is to load the view in each product page, and find out the position of the node within a certain contextual list.

I'm not sure it's ideal, certainly in terms of performance, but so far it has performed quite well, and it allows us a great deal of flexibility.

Visitor (not verified)

Fri, 2009/05/08 - 19:29

Hey Khalid, excellent news! :)

I had one question... would it be possible/difficult to emulate a Flickr photo stream type of feature with this module? For instance while viewing a Photo node owned by User A, the Previous/Next links would only cycle through the Photo nodes belonging to User A. I've been able to produce this effect using Custom Pagers module and a user argument on the View, but of course the performance with lots of nodes is dismal. So essentially - is it currently (or in the future) possible to limit the results by a user argument?

Thanks for your always helpful performance tips and tricks with Drupal :D

Is your Drupal or Backdrop CMS site slow?
Is it suffering from server resources shortages?
Is it experiencing outages?
Contact us for Drupal or Backdrop CMS Performance Optimization and Tuning Consulting