Hello,

I recently enabled logging of queries that don't use indices in mysql, and it appears that roughly half of the queries in my log (ca. 11K out of 21K) were joins on several Ubercart tables. For example:

SELECT pa.nid, pa.aid, pa.ordering, a.name, a.ordering, ao.aid, COUNT(po.oid) FROM uc_product_attributes AS pa LEFT JOIN uc_attributes AS a ON pa.aid = a.aid LEFT JOIN uc_attribute_options AS ao ON a.aid = ao.aid LEFT JOIN uc_product_options AS po ON ao.oid = po.oid AND po.nid = 39 WHERE pa.nid = 39 GROUP BY ao.aid, pa.aid, a.name, pa.ordering, a.ordering, pa.nid HAVING count(po.oid) > 0 ORDER BY pa.ordering, a.ordering;

Doing a mysql explain on this suggests that there's no usable index on the uc_attribute_options table:

+----+-------------+-------+--------+---------------+---------+---------+-------------------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                           | rows | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------------+------+---------------------------------+
|  1 | SIMPLE      | pa    | ref    | PRIMARY       | PRIMARY | 4       | const                         |    1 | Using temporary; Using filesort | 
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | act_live_drupal6.pa.aid       |    1 |                                 | 
|  1 | SIMPLE      | ao    | ALL    | NULL          | NULL    | NULL    | NULL                          |    8 |                                 | 
|  1 | SIMPLE      | po    | eq_ref | PRIMARY       | PRIMARY | 8       | const,act_live_drupal6.ao.oid |    1 | Using index                     | 
+----+-------------+-------+--------+---------------+---------+---------+-------------------------------+------+---------------------------------+

Apologies for the poor formatting of the table.

Since the option id (oid) is the primary key of the uc_attribute_options table, I'd guess that the unindexed ao.aid column is the culprit.

I'm not hitting any performance issues since I've only got 8 entries in the uc_attribute_options table, and I don't imagine it's best practice to make changes to the database just so I don't get unindexed query entries in my slow query log, but my instinct would be to create an index on the aid column (or perhaps make both the aid and oid columns the primary key like the uc_product_attributes and uc_product_options tables). Is there a reason not to do something this?

Thanks for ubercart!
-G

CommentFileSizeAuthor
#2 841976-uc_attribute_options-index.patch865 byteslongwave

Comments

tr’s picture

Version: 6.x-2.2 » 7.x-3.x-dev
Category: bug » feature

This needs to be done. Can someone profile a site with lots of attributes before / after indexing aid? A proper patch will have to include an update function to change the schema of existing sites and update existing data. Probably a batch update, because this could potentially affect a lot of records.

Schema changes should go into 7.x-3.x first.

longwave’s picture

Status: Active » Needs review
StatusFileSize
new865 bytes

Adding an index is a single operation, there is nothing to batch. We have similar updates in uc_order.install and uc_file.install, compare with http://drupalcode.org/project/ubercart.git/commitdiff/94442b6

longwave’s picture

Status: Needs review » Fixed

Committed to 7.x, ported and committed to 6.x.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.