Remove unnecessary queries to count number of results in pager_query
lyricnz - April 12, 2007 - 09:56
| Project: | Drupal |
| Version: | 6.x-dev |
| Component: | database system |
| Category: | task |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | won't fix |
Description
When using pager_query, we don't need a seperate query to ask the database for the number of rows, if we run the main query itself first and it returns less than $limit. This will happen on the last page of a paginated query (which probably happens a bit more often than you expect)
Example (user search):
20.31 1 pager_query SELECT COUNT(*) FROM users WHERE LOWER(name) LIKE LOWER('%ab%')
0.67 1 pager_query SELECT name, uid FROM users WHERE LOWER(name) LIKE LOWER('%ab%') LIMIT 0, 15See attached patch - applies to both 5.1 and HEAD
| Attachment | Size | Status | Test result | Operations |
|---|---|---|---|---|
| page-optimization.patch | 1.6 KB | Ignored | None | None |
#1
Update - remove debugging code - sorry.
#2
Update - coding style
#3
#4
Assigning to 6.x-dev
#5
I haven't tested it, but the code looks good to me. I'm not sure I buy the benchmark of the current code, but this change could only make things faster.
#6
Yeah, the query cache is likely to make the second query pretty fast, no matter which order they're in - but the less queries the better.
#7
This still applies but I'm not sure how to test it. Seems like it could be a decent performance boost though.
#8
fatal error on admin/content/node
#9
Now that db_num_rows() has been removed, I don't think this optimization is possible - we don't fetch each of the rows in the query here, so we no longer know how many rows are returned.