Skip to:
Content

Opened 6 years ago

Closed 6 years ago

Last modified 5 years ago

#876 closed defect (fixed)

forum_stickies query is not very useful on large sites

Reported by: mdawaffe Owned by:
Milestone: 1.0 Priority: normal
Severity: normal Version: 0.9.0.1
Component: Back-end Keywords: mysql
Cc:

Description

We were having some SQL issues on WordPress.org's support forums and found that

KEY forum_stickies (topic_status,forum_id,topic_sticky,topic_time)

wasn't all that useful.

For queries like

SELECT t.* FROM bb_topics AS t
WHERE t.forum_id = '1' AND t.topic_status = '0' AND t.topic_sticky = '0'
ORDER BY t.topic_time DESC LIMIT 30

the index was not used. Instead KEY forum_time (forum_id,topic_time) was used. Possibly because on large sites, that index is so large that working with a smaller index actually give a performance boost. I'm not sure.

And, since forum_id is in the middle there, the index was not used on queries like

SELECT t.* FROM bb_topics AS t
WHERE t.topic_status = '0' AND t.topic_sticky = '0'
ORDER BY t.topic_time DESC LIMIT 30;

We ended up removing that index and instead putting in

KEY stickies (topic_status,topic_sticky,topic_time)

Which greatly improved the efficiency of that second query (and didn't effect the first one since it still used the forum_time index).

Attachments (1)

876.diff (556 bytes) - added by mdawaffe 6 years ago.

Download all attachments as: .zip

Change History (4)

mdawaffe6 years ago

comment:1 mdawaffe6 years ago

  • Resolution set to fixed
  • Status changed from new to closed

comment:2 anonymous5 years ago

  • Milestone 1.0-beta deleted

Milestone 1.0-beta deleted

comment:3 sambauers5 years ago

  • Milestone set to 1.0
Note: See TracTickets for help on using tickets.