I have just re-launched a site containing about 43000 topics and over 300000 posts...
I had severe performance issues on all sites using the get_latest_topics functionality (the frontpage was severe because it is using more than once in my custom theme).
Because of the "index" design it file-sorting because it's sorting on topic_time and is mysql is hitting the stickies index.
The solution was to create a new index only with the topic_time field in it. However I was unable to force this index in the query without touching the core files.
This is what i did:
Could there be a filter there so I could move this out into a plugin?
BB_Query::generate_topic_sql function:
$index_append = '';
switch ($this->query_id) {
case 'get_latest_topics':
$index_append = ' FORCE INDEX (topics_time_order) ';
break;
}
$this->request = $this->_filter_sql( $bits, "$bbdb->topics AS t $index_append" );
I guess I will find more places where I could do some index optimization that fit's my install... I can't be the first running into this issue?