Initialization process uses query that causes full table scan

For what it's worth here is what the query looks like in a profiler.

Per a tweet from Steven Merrill, it appears that having a function (CONCAT() in this case) in the query is what generates a full table scan. The options appear to be:

• Re-write the query so it doesn't use a function
• Get the data into PHP and use it to do a count.

Changing title and category to match issue.

As demonstrated by profiler screen shots here and here, the queries used in taxonomy_pathauto_bulk_update_batch_process() and node_pathauto_bulk_update_batch_process() use queries that cause full table scans because of the use of the CONCAT() MySQL function in the query. The attached patch offloads all that processing to PHP using arrays. With this code, it gets right to the updating functionality and doesn't get stuck on the initialization part.

I'm sure there are ways this could be improved in terms of memory management, so I welcome suggestions on how to improve it. I looked at ways to do it in MySQL but the fact that the "node/" and "taxonomy/term/" strings have to be concatenated with tids and nids, I'm not sure there's another way to do it in the db. I do remember some type of query type in MSSQL and Oracle where you could do a query that is basically a loop through records, but I'd have to do some research on that. Even so, I think CONCAT() would have to be used, unless you want to create the whole src value first and then do an individual query for each row.

Anyway, I'm open to suggestions as to how this could be improved (or why it should be thrown out).

As a side note, in looking at the different process functions (taxonomy, node, forum, user, etc.) they are similar enough that I'm thinking they could be combined into one function. I'll look at that when I get a chance.

Thanks.

Here's a completely different approach that worked for updating ~80k nodes. It involves three parts:

1. An index table to store the nodes/users/terms to be aliased.
2. A drush command to fill the table
3. Modified queries in the *_pathauto_bulk_update_batch_process() functions to use the index table.

The idea is to run the drush command to fill the index table with any combination of the three types (node, user, and term), and then run the bulk update at admin/build/path/update_bulk (after deleting the appropriate aliases at admin/build/path/delete_bulk). It can take a while to run, but it works.

I only used this process for nodes and terms, but I modified user_pathauto_bulk_update_batch_process() anyway, even though I haven't actually tested it. It should work, though.

Possible improvements:

• Instead of a drush command, implement the index table filling as part of the generation process.
• Integrate it all together so that it delete existing aliases and generate url redirects (which would have been ideal in my case, since I had to go back and write some code to create new aliases for all those nodes), similar to the option at admin/build/path/settings.