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!

Comments

Michelle’s picture

Michelle
she/her
Primary language English
Location Wisconsin, USA
CreditAttribution: Michelle commented
Version: 6.x-1.1 » 6.x-2.x-dev

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

ghankstef’s picture

ghankstef CreditAttribution: ghankstef commented
Version: 6.x-2.x-dev » 6.x-1.1

Something like this should do the trick

db_query("ALTER TABLE {comments} ADD INDEX cid_nid (cid, nid);");
Michelle’s picture

Michelle
she/her
Primary language English
Location Wisconsin, USA
CreditAttribution: Michelle commented
Version: 6.x-1.1 » 6.x-2.x-dev

Thanks. BTW, I changed the version intentionally. Feature requests go against 2.x. 1.x is all done.

Michelle

Fogg’s picture

Fogg CreditAttribution: Fogg commented

Is still a valid issue? I added this index to my db as my forum is also quite slow on the overview page. it did not change too much, but it also did not hurt so far.

Michelle’s picture

Michelle
she/her
Primary language English
Location Wisconsin, USA
CreditAttribution: Michelle commented

The queries on the overview page are very expensive. I haven't tried adding this index, yet. No idea how much good it does.

Michelle

Fogg’s picture

Fogg CreditAttribution: Fogg commented

based on my page I can confirm it helps! my typical page load time is now less than 10 sec whereas it has been close to 20 before. so i would reccommend this index as well!

Dries Arnolds’s picture

Dries Arnolds
Primary language Dutch
Location 🇳🇱 Amsterdam
CreditAttribution: Dries Arnolds commented

Michelle, I'm also seeing very good results with this tweak. Are there any plans on adding this?

In my opinion there's a lot that can be optimised about forum performance. A lot is core Drupal stuff, but since many serious forums use your module I'd think it would be good if your module helped out on that issue.

Michelle’s picture

Michelle
she/her
Primary language English
Location Wisconsin, USA
CreditAttribution: Michelle commented

@Pixelstyle: Yes, it's still an active feature request. I'm working my way through the bugs first, so haven't gotten to this, yet.

Michelle

Michelle’s picture

Michelle
she/her
Primary language English
Location Wisconsin, USA
CreditAttribution: Michelle commented
Status: Active » Needs review

Setting anything with code to look at to "needs review" to move things along.

mcdruid’s picture

mcdruid
Location 🇬🇧🇪🇺
CreditAttribution: mcdruid commented

This is interesting, but I'd like to see a bit more evidence of what difference the new index would actually make - especially as we're talking about messing with core tables.

e.g. a before / after EXPLAIN statement on a query which is showing up in the slow query log?

I'd have thought the correct way to add the new index - if we do - would be using db_add_index in the hook_install / hook_update_N