Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
If you have a lot (thousands) of nodes with the same user id, you may find that the node_load() call in content_profile_load() gets slow. This is because out of the box the {node} table doesn't have a compound index on (type, uid). A solution is to add one, e.g.:
function my_module_update_6xxx() {
$ret = array();
// Critical for fast return of profile given uid
$ret[] = update_sql('CREATE INDEX node_type_uid ON {node} (type, uid)');
return $ret;
}
A better solution, I think, is for content_profile to create this index when a new content profile type is added. Might save some developers some head-scratching.
Comment | File | Size | Author |
---|---|---|---|
#9 | content_profile-add_db_index-903648-9.patch | 2.25 KB | techninja |
#8 | content_profile-add_db_index-903648-8.patch | 2.25 KB | techninja |
#3 | content_profile_903648.patch | 2.25 KB | catch |
#2 | content_profile_903648.patch | 2.23 KB | catch |
Comments
Comment #1
catchRight there are two issues here:
1. That query doesn't use indexes properly. There are plenty of sites where one users can have hundreds or thousands of nodes attached. Adding the index as described in an update (and a hook_schema_alter() to document it) works for this.
2. By doing a node_load() with arguments, static caching doesn't kick in very well (and the site I found this one is using the Pressflow node_load_cache branch/patch - and that can't fetch from cache if node_load() is passed something other than nid). Switching to a db_result(db_query()) for the nid, then node_load(nid) would avoid this.
Should be able to get a patch for this either this evening or tomorrow.
Comment #2
catchHere's the patch, I made both changes. The index is added on install and in an update, removed on uninstall. Also I switched the node_load() for a db_result(db_query()) - if the node has already been loaded on this page from somewhere else, then this allows node_load()'s own static cache to kick in, otherwise without an nid, it has to go look in the database.
Also switched the query and index order to uid, name, instead of name, uid. The int should be a faster lookup, and there will also be more variation in the index for uid than type (at least on any site with more users than node types).
Comment #3
catchAdd language to the query properly this time.
Comment #4
rjbrown99 CreditAttribution: rjbrown99 commentedSubscribing. I now have #3 in prod.
Comment #5
josepvalls CreditAttribution: josepvalls commentedI stumbled into this ticket trying to solve a performance issue we've been having with views.
Creating the index is something we have already figured but we are facing the problem that MySQL is not using it; I would like to know if someone already faced a similar issue and how should we proceed to patch content_profile/views to add an index hint:
In MySQL query browser we observe a ten-fold performance improvement when using the "use index for join (node_type_uid)" index hint.
Comment #6
josepvalls CreditAttribution: josepvalls commentedI should probably open a new ticket and I am leaving the following comment just in case it can be of any use to any one, it is a quick and dirty hack and I am aware that I should burn in hell.
In views/includes/handlers.inc
line:
becomes:
Comment #7
hefox CreditAttribution: hefox commentedWrong field: type, not name.
I sorta wish this entire logic was in one function that could call by it's own just in case just need that nid but not the node.
Comment #8
techninja CreditAttribution: techninja commentedRe-rolling and attaching patch to include 1 word change by hefox.
Comment #9
techninja CreditAttribution: techninja commentedWhoops, wrong fix. Try again.
Comment #10
hefox CreditAttribution: hefox commented