Adding an index to comments table speeds performance of /forum a great deal
ghankstef - July 24, 2009 - 01:35
| Project: | Advanced Forum |
| Version: | 6.x-2.x-dev |
| Component: | Miscellaneous |
| Category: | feature request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Jump to:
Description
While trying to improve performance of the /forum page, I added this index and shaved over 4 full seconds of db/pageload time on a large comments table (more than 500,000 comments in this case).
ALTER TABLE comments ADD INDEX cid_nid (cid, nid);Might be worth adding to the install file. I am not sure if its considered hacking core to add an index a core table like this. It does provide quite a performance boost. I would provide a patch but I'm not so good a CVS yet.
Additionally this query: http://drupal.org/node/529180 is slow on a large users table. This query is shared with the "Who's New" block in core so I filed a core issue on this.
Thanks Michelle for a great module!
#1
Well, merlinofchaos thought it was ok when we discussed it on IRC, so that's good enough for me. :) Will need to find out how one adds an index in an install file and then will get it in.
Thanks,
Michelle
#2
Something like this should do the trick
db_query("ALTER TABLE {comments} ADD INDEX cid_nid (cid, nid);");#3
Thanks. BTW, I changed the version intentionally. Feature requests go against 2.x. 1.x is all done.
Michelle