Attributes admin list page doesn't load with too many attributes

I'm helping a company migrate from an old ecommerce platform to Ubercart. They're old database has 15,000+ Attributes. Now granted, that's insane, I agree. I'm working with them to consolidate them, because most of them are "Color" or "Colors". But I figured let's give it a try, and pull them all in (with the Migrate API: http://www.drupal.org/project/migrate), in case we need to wait until after the launch to reorganize them.

However, after doing so, I was unable to load the Attributes admin list page (/admin/store/attributes). It would just hang indefinitely.

I took a look at the page callback, uc_attribute_admin(), and was able to isolate the problem. Line 26 of uc_attribute/uc_attribute.admin.inc:

  $result = pager_query("SELECT a.aid, a.name, a.label, a.required, a.ordering, a.display, COUNT(ao.oid) AS options FROM {uc_attributes} AS a LEFT JOIN {uc_attribute_options} AS ao ON a.aid = ao.aid GROUP BY a.aid, a.name, a.label, a.ordering, a.required, a.display". tablesort_sql($header), 30, 0, "SELECT COUNT(aid) FROM {uc_attributes}");

What it's doing is querying the database for a list of attributes, and it's using the pager_query() function to return that list in chunks so it can be displayed 30 at a time, with a pager at the bottom for browsing through them. The problem is, it's also trying to squeeze in an Option-count lookup via a LEFT JOIN in the same query, so that it can populate the column on the page that tells you how many Options are in each Attribute. This JOIN is very costly, because it is performed to the entire table BEFORE the results are limited by the pager.

In most circumstances, the time it takes to perform this JOIN is negligible, but if you have 15,000+ products, you're in for a wait.

So, here's a patch for anyone that has way too many Attributes. It basically just moves the Option counting out of the original query, and performs it in the while() loop AFTER the results have been limited down to 30 by pager_query().

Probably makes sense to put this into core (minus the extraneous comments I made) just so all sites can be as scalable as possible.