Add an index and avoid SELECT * for better performance
Chris Bray - September 21, 2008 - 23:38
| Project: | Comment CCK |
| Version: | 6.x-1.x-dev |
| Component: | Code |
| Category: | feature request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | needs review |
Description
This patch adds an index to comment_cck_revisions and removes a SELECT * to make queries quicker (5ms to 1ms in my case).
Any comments?
| Attachment | Size |
|---|---|
| comment_cck.patch | 1.44 KB |
#1
How did you even get 5.x-1.x-dev to work for you? See: http://drupal.org/node/311272
I'd gladly test out the patch if it would make the mod work ;)
#2
I just tried to apply the patch and I got this:
patching file comment_cck.installpatching file comment_cck.module
Hunk #1 FAILED at 137.
1 out of 1 hunk FAILED -- saving rejects to file comment_cck.module.rej
****************** 137,143 ****
if (variable_get('comment_cck_node_'. $node->type, FALSE) == TRUE) {
$fields = variable_get('comment_cck_fields_'. $node->type, array());
$previous_text = $comment->comment;
- $comment_revision = db_fetch_object(db_query('SELECT * FROM {comment_cck_revisions} WHERE cid = %d', $comment->cid));
$current_node = _comment_cck_build_node((int) $comment_revision->nid, $comment_revision->vid);
$previous_node = _comment_cck_build_node((int) $comment_revision->nid, $comment_revision->previous_vid);
$cck_fields = content_types($node->type);
--- 137,143 ----
if (variable_get('comment_cck_node_'. $node->type, FALSE) == TRUE) {
$fields = variable_get('comment_cck_fields_'. $node->type, array());
$previous_text = $comment->comment;
+ $comment_revision = db_fetch_object(db_query('SELECT cid, vid, nid, previous_vid FROM {comment_cck_revisions} WHERE cid = %d', $comment->cid));
$current_node = _comment_cck_build_node((int) $comment_revision->nid, $comment_revision->vid);
$previous_node = _comment_cck_build_node((int) $comment_revision->nid, $comment_revision->previous_vid);
$cck_fields = content_types($node->type);
#3
Hmmm, I fear I may have made the patch against a previous 5.x-1.x-dev build, rather than the current one.
I just re-rolled it and this now applies cleanly against the latest 5.x-1.x-dev.
#4
Thanks!
Patch applied successfully.
From what I can tell, it seems like a good idea.
However, it still doesn't fix the functionality of the mod. Does it work for you?
txcrew
#5
Unfortunately I don't have a test site setup at the moment and I can't upgrade the production site to the latest 5.x-1.x-dev but I'll see if I can setup a test site later and have a look for you.
#6
No problem at all. Please no hurry.
If it'd be easier, could you just post the patched package of 5.x-1.x-dev you are using?
#7
With many cck_comments on a single node, there will be many revisions made, does this patch also take the performance hit from having many revisions into account?
I'm assuming yes?
txcrew
#8
@Chris Bay: Could you please update your patch to 6.x? I'm very interested in testing and applying this patch.
@txcrew: This won't solve the problem of too many revisions, but #314424: Option to disable revisions would.
#9
In comment_cck_update_6000() {comment_cck_revisions}.cid was made a primary key so it's got an index now. I don't know that removing the * will make nearly the difference adding the index would but here's a patch to get this moving.