Closed (fixed)
Project:
Webform
Version:
6.x-3.x-dev
Component:
Code
Priority:
Major
Category:
Task
Assigned:
Unassigned
Reporter:
Created:
17 Feb 2011 at 08:01 UTC
Updated:
9 Mar 2011 at 17:51 UTC
Jump to comment: Most recent file
http://api.lullabot.com/webform_get_submission_count
This queries on nid, or nid and uid, or nid, uid and sid.
However:
'sid_nid' => array('sid', 'nid'),
),
'primary key' => array('sid'),
);
This doesn't help any of these.
Looks like this needs an index on nid, uid, sid
Will add a patch later.
| Comment | File | Size | Author |
|---|---|---|---|
| #7 | webform_submissions_index-d6.patch | 1.35 KB | quicksketch |
| #7 | webform_submissions_index-d7.patch | 1.33 KB | quicksketch |
| #4 | webform_submissions_index-d6.patch | 1.35 KB | quicksketch |
| #4 | webform_submissions_index-d7.patch | 1.37 KB | quicksketch |
| #3 | webform_1064326.patch | 782 bytes | catch |
Comments
Comment #1
quicksketchHah, thanks catch. Looking forward to the patch.
Comment #2
catchHere's the patch, the query is around 700ms on the site I'm looking at.
I'm seeing other queries around 70ms in in webform_node_load(), but those ought to be indexed, so now wondering if that's due to the number of records in the table for that nid, which will be harder to fix.
Comment #3
catch325, not 425.
Comment #4
quicksketchThanks catch. Here's a reroll that tidies up things a bit.
- The order of the key should match the table columns, which is sid_nid_uid, not nid_uid_sid.
- Since you put the "indexes" entry below "primary key" (which you should, it matches core), I rearranged our other existing keys to match this same ordering.
- Matching D7 patch.
Let me know if these things look good and I'll pop 'em in.
Comment #5
catchBtree indexes can only be read left to right so this really has to be nid first. Otherwise queries on just nid or just nid and uid will be unindexed. Re-ordering the schema definitions looks good.
Comment #6
quicksketchWow I never would have guessed such a requirement. I read up a little on the topic (http://craigballinger.com/blog/2010/10/useful-mysql-indexing-strategies/ was very helpful) and definitely looks like I can't just flip the columns around like that.
I wasn't aware of this requirement at all until just now, so it's not surprising that my existing indexes aren't very helpful. Does it seem like we should not only add a nid_uid_sid key but also flip the the sid_nid unique key to be nid_sid? Then that would help in queries where we're retrieving a group of submissions not by user ID at all and still maintain the same requirement.
I can reroll the patch but I'd still like your review, I'm definitely not an expert on this particular gotcha.
Comment #7
quicksketchI realized that switching the unique key to be "nid_sid" wouldn't have any additional value, since if both nid and sid columns are used the existing unique key will be fine, but if just "nid" is used this new index that we're adding will be the first item, so it will help in that situation.
I've committed this patch which matches your original patch other than moving around the "primary key" items for consistency.
Comment #8
catchThanks Nate!