Drupal Performance: Pagination and COUNT queries

Drupal's core pagination system does not scale well on large, high-traffic sites because of it's need to count the total number of items in the list. This involves wrapping the original query around a COUNT query like below.

SELECT COUNT(*) FROM (SELECT nid FROM node);

Count queries are usually very fast for tables using the MyISAM engine, but most high-traffic Drupal sites use the InnoDB engine for most of the tables. COUNT queries on tables using the InnoDB engine are notoriously slow and become painfully so the more records are stored in that table (think 100K+ nodes or comment).

I've seen one work-a-round for this from Robert Douglass on Aquia's blog, but, in my specific situation the issue involved Views and I needed a way to automatically use a pager that did not require these expensive, and sometimes crippling, COUNT queries.

As a solution, I wrote a simple module to integrate with Views that adds a new "Lite" option when adding a pager to a view. This "Lite" pager allows users to navigate between pages of content, but without counting the number of total results available. This new pager is only slightly less useful than Drupal's core pager in that users can't see the total number of pages of content or navigate to the last page, but I think the trade off in performance gains is well worth while.

If you are interested in using the Views Litepager module, checkout the project page on this site. If there is enough interest, I can add it as an official contrib module on Drupal.org.

Comments

The count(*) should be

The count(*) should be count(1) in MySQL. Significant performance increase in some cases.

Drupal rocks.

Drupal rocks.