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.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

catch’s picture

Right 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.

catch’s picture

Category: feature » bug
Issue tags: +Performance
FileSize
2.23 KB

Here'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).

catch’s picture

Component: User registration module » Base module
Status: Active » Needs review
FileSize
2.25 KB

Add language to the query properly this time.

rjbrown99’s picture

Subscribing. I now have #3 in prod.

josepvalls’s picture

I 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:

SELECT COUNT(*) AS nid
 FROM node node
 INNER JOIN content_type_cv node_data_field_cv_workflow ON node.vid = node_data_field_cv_workflow.vid
 INNER JOIN users users ON node.uid = users.uid
 LEFT JOIN node node_users use index for join (node_type_uid) ON users.uid = node_users.uid AND node_users.type = 'candidato'
 WHERE
 (node.type in ('cv'))
 AND (node.status = 1)
 AND (node_data_field_cv_workflow.field_cv_workflow_value = '360')

In MySQL query browser we observe a ten-fold performance improvement when using the "use index for join (node_type_uid)" index hint.

josepvalls’s picture

I 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:

$output = " $this->type JOIN $right_table $table[alias] ON $left_field = $table[alias].$this->field";

becomes:

    if($table[alias]=='node_users')
      $output = " $this->type JOIN $right_table $table[alias] USE INDEX FOR JOIN (node_type_uid) ON $left_field = $table[alias].$this->field";
    else
      $output = " $this->type JOIN $right_table $table[alias] ON $left_field = $table[alias].$this->field";

hefox’s picture

Status: Needs review » Needs work
+++ b/content_profile.install
@@ -39,6 +46,9 @@ function content_profile_install() {
+  db_add_index($ret, 'node', 'content_profile', array('uid', 'name'));

Wrong field: type, not name.

+++ b/content_profile.module
@@ -475,14 +475,15 @@ function content_profile_load($type, $uid, $lang = '', $reset = NULL) {
+      $nid = db_result(db_query("SELECT nid FROM {node} WHERE uid = %d AND type = '%s'", $uid, $type));

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.

techninja’s picture

Re-rolling and attaching patch to include 1 word change by hefox.

techninja’s picture

Whoops, wrong fix. Try again.

hefox’s picture

Status: Needs work » Needs review