Queries take too long while bulk updating huge amount of URL aliases

There's not anything we can really do to make that query better. If you've got any ideas, let us know, otherwise this is kinda how this has to work.

SELECT n.nid AS nid FROM node n LEFT OUTER JOIN url_alias ua ON CONCAT('node/', n.nid) = ua.source;
28013 rows in set (4 min 58.56 sec)

SELECT nid FROM node;
28008 rows in set (0.02 sec)

Why are we doing a left join when we are only taking data from the left table?

$ diff /tmp/2011-10-11_LEFT_OUTER_JOIN.txt /tmp/2011-10-11_SELECT_nid.txt
27924,27927d27923
< 1
< 1
< 1
< 1
27933d27928
< 3

The only difference on my site between the queries is:
1. The second query is 14,928 times faster
2. nid 1 comes up once instead of 5, and nid 3 comes up once instead of twice. Why would we even want the same nid coming up multiple times? The query only returns the nid.

You're missing the important part of needing WHERE ua.alias IS NULL which was missing from the original report. And I'm not sure why this query would be performing an outer join. It's now how the query is coded and it's not how its executed on my version of MySQL.

I've hit the same wall (and same error) with bulk updating a site with ~9,000 nodes that need some nice new aliases.

I've tried accomplishing the task with drush, but hit another snag with that as well (looks like pathauto function names have shuffled a little). Described it here.

Same problem here. No time-out, but the bulk update is extremely slow.

70,000 nodes
MySQL 5.0.86
And an outer join:

SELECT n.nid AS nid
FROM node n
LEFT OUTER JOIN url_alias ua ON CONCAT( 'node/', n.nid ) = ua.source
WHERE (
ua.source IS NULL
)
AND (
n.nid > '0'
)
ORDER BY n.nid ASC
LIMIT 25
OFFSET 0 

Same here... same AJAX error after running through around 3,000 of our 50,000 users. :(

It's also running incredibly slow, but I'll post that on the other ticket.

I've changed the query, and this seems to do the trick:

• Don't do a CONCAT( 'node/', n.nid ). Instead cut the 'node/' form ua.source using SUBSTRING. This will give you the nid.
• Create a temporary table with these nids, so they can be indexed.

function node_pathauto_bulk_update_batch_process(&$context) {
  if (!isset($context['sandbox']['current'])) {
    $context['sandbox']['count'] = 0;
    $context['sandbox']['current'] = 0;
  }

  // Create a temporary table with an index on nid.
  $result = db_query('CREATE TEMPORARY TABLE tmp_alias (nid int PRIMARY KEY)');
  // Cut the nid from the source field.
  $result = db_query("INSERT INTO tmp_alias (nid) SELECT SUBSTRING(source, 6) FROM url_alias WHERE (SUBSTRING(source, 1, 5) ='node/')");
  // The next four lines are adjusted to use the temporary table.
  $query = db_select('node', 'n');
  $query->leftJoin('tmp_alias', 'ta', "n.nid = ta.nid");
  $query->addField('n', 'nid');
  $query->isNull('ta.nid');
  $query->condition('n.nid', $context['sandbox']['current'], '>');
  $query->orderBy('n.nid');
  $query->addTag('pathauto_bulk_update');
  $query->addMetaData('entity', 'node');

  // Get the total amount of items to process.
  if (!isset($context['sandbox']['total'])) {
    $context['sandbox']['total'] = $query->countQuery()->execute()->fetchField();

    // If there are no nodes to update, the stop immediately.
    if (!$context['sandbox']['total']) {
      $context['finished'] = 1;
      return;
    }
  }

  $query->range(0, 25);
  $nids = $query->execute()->fetchCol();

  pathauto_node_update_alias_multiple($nids, 'bulkupdate');

  // Drop the temporary table so it can be recreated in the next run.
  $result = db_query('DROP TABLE tmp_alias');

  $context['sandbox']['count'] += count($nids);
  $context['sandbox']['current'] = max($nids);
  $context['message'] = t('Updated alias for node @nid.', array('@nid' => end($nids)));

  if ($context['sandbox']['count'] != $context['sandbox']['total']) {
    $context['finished'] = $context['sandbox']['count'] / $context['sandbox']['total'];
  }
}

I didn't find out how to implement this using the database API.

Wow! While typing this the bulk update finished: "Generated 59841 URL aliases."
In less than 30 minutes!

I'll check the results.

Do I need to delete any existing aliases first? I have about half the aliases generated for content, users, etc... and when I try to finish aliasing them with this patch, I get the exact same behavior as before... incredibly high load on the CPU and very slow progress... it would take me 3 or 4 days at this speed to alias just my articles. A few more days for my users, etc. :(

Hi phreadom, the adjusted version of node_pathauto_bulk_update_batch_process() worked for me. I didn't delete the existing aliases.

I commented out function node_pathauto_bulk_update_batch_process(&$context) in pathauto.pathauto.inc, and pasted in your new version, and tried running the bulk update for content, and later for users, and both behaved the same way as before, running high load for hours. :(

Am I doing something wrong? I'd really love to get this working.

I cleared my existing content aliases, and deleted any leftover batches from my batches table and then ran the content bulk alias update again and it's working!

It's gone through around 7,000 already in ~20 minutes and there's almost no load on the CPU either! Fantastic! Thank you!

UPDATE:

Status message
Generated 16555 URL aliases.

4 hours total, but that's a hell of a lot better than the 4 days it would have taken previously!

Now I'm going to try porting the same changes to the other functions for bulk updating my user aliases, as I have around 50,000 users, so the same problems definitely apply there as well. I'll update with the code if I get it to work.

Let's see what Dave thinks of this (#1).

I've put the temporary table creation in a separate, generic function, so it can be used for nodes, users, terms etc.

@phreadom: updating your 50,000 users should be fairly easy using this patch.

@gaele: "Status message Generated 54934 URL aliases." took a few hours, but it made it through all of the user aliases as well. :D Thanks again!

Since it "works" for me at the moment, since I patched the other functions I needed as well, I'll just leave it as is for the moment and hope the maintainer adds these changes into the module itself in some form so that I won't have to maintain a set of custom patches for it.

I just ran the bulk update again. Generated 70970 URL aliases in 34 minutes.

Sorry, this is my first real encounter with the D7 database API. I couldn't find out how to create a temporary table that has an index on the (n)id in a different way.

Why not adding the fields entity_type and entity_id to the url_alias table?

Are you sure this is not the same of http://drupal.org/node/1415930 ?
Are you on mysql 5.0 ? I experienced the same issue you describe here, and it worked very fast after I applied http://drupal.org/node/1415930.

When I run Bulk Update, I see error message: "An AJAX HTTP error occurred. HTTP Result Code: 500 Debugging information follows. Path: /batch?id=306&op=do StatusText: Internal Server Error ResponseText:".

When I generate URL aliases with module Views Bulk Operations (VBO) it was all OK :)

I recommend patch http://drupal.org/node/1415930#comment-5511014

+1 for the patch in #22... I have 13,000+ users and over 29,000 nodes that needed updated and that patch made it happen!