Add support for database hints and make PagerDefault properly pluggeable

Open on Drupal.org →

Created on 21 April 2010, about 15 years ago

Updated 10 May 2025, 27 days ago

The current implementation of a pager query always runs TWO queries against actual data. MySQL allows you to grab the total number of rows that would have been returned if no LIMIT was present by adding the "SQL_CALC_FOUND_ROWS" statement in your SELECT query.

This causes MySQL to return the normal result set and store the full number of matching records in a variable, which can then easily be retrieved via the FOUND_ROWS() function.

Ie:

SELECT SQL_CALC_FOUND_ROWS nid, title from {node} LIMIT 500,10;
SELECT FOUND_ROWS();

As it stands, the database layer doesn't really allow us to make use of this sort of functionality, but it would be nice if it did. For large tables there is a potentially large performance gain, especially when many changes are made to the data being paged through, as the count query can't be cached in such a case.

I don't doubt that other databases have their own versions of these sorts of tricks, for various kinds of queries and I wonder if it might be worth modifying the database layer to be able to make use of them.

📌 Task

Status

Postponed: needs info

Version

11.0 🔥

Component

database system

Created by

🇦🇺Australia cafuego

Live updates comments and jobs are added and updated live.

stale-issue-cleanup
To track issues in the developing policy for closing stale issues, [Policy, no patch] closing older issues

Incomplete comments

Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

Comment 27 days ago →
🇺🇸United States smustgrave
Thank you for creating this issue to improve Drupal.

We are working to decide if this task is still relevant to a currently supported version of Drupal. There hasn't been any discussion here for over 8 years which suggests that this has either been implemented or is no longer relevant. Your thoughts on this will allow a decision to be made.

Since we need more information to move forward with this issue, the status is now Postponed (maintainer needs more info). If we don't receive additional information to help with the issue, it may be closed after three months.

Thanks!
Comment 25 days ago →
🇦🇺Australia cafuego
There is support for database hints, I don't know if there still is PagerDefault or whether the pager query is pluggable. As far as I knwo Drupal still runs a count query (which is more terrible now that MyISAM is not the default storage engine on the preferred database) so in theory it would still be a good idea to not have to run those at all just to render a pager.

contrib.social Blog FAQ Discussions

Production build 0.71.5 2024