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:
- Displaying previous/next thumbnails for image nodes. This is the old way of doing things that we used at 2bits.com.
- Basic Next/Previous navigation for nodes
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
Has this EXPLAIN:
Extra: Using where; Using filesort
That is too many rows ...
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: