lostmouse

Wow, this query works very well!  The execution speed is actually more around 0.002 for my server which is more than acceptable. Thank you so much for the help!

Adding the composite index had no effect.  It did not seem to matter how I structured the query or what composite indexes I had on the topics table. Removing the post table had a significant difference reducing the execution time to 1/10th of the original time (0.020).  If I added the "STRAIGHT_JOIN" keyword instead, it had a very similar effect on the execution time (about 1/5th to 1/10th). This is the EXPLAIN for the straight_join: [code]1  SIMPLE  t  ALL  forum_id,topic_last_post_id  NULL  NULL  NULL  7823  Using filesort 1 SIMPLE f eq_ref PRIMARY,forum_level_to_view,forum_site PRIMARY 2 aanime_aanimedb.t.forum_id 1 Using where 1 SIMPLE p eq_ref PRIMARY PRIMARY 4 aanime_aanimedb.t.topic_last_post_id 1 [/code] The "forum" table is still being selected first.

Mysql version 4.1.10 The purpose of the query is to pull the 20 most recently replied to topics (in other words, the 20 most recently active topics).  Any sticky topics have priority. forum_level_to_view is used to allow only certain members to view certain forums (for example, the administration forum has a permission "forum_level_to_view" of 4).  It makes no difference in speed whether it's left in or out of the query.  At this point, there is no forum with forum_level_to_view greater than 5 as there are no permission levels greater than 5. I've analyzed all of the tables, there is no improvement in this query's execution speed. I made a mistake about the improvement in speed difference when the ORDER BY clause is removed.  With the clause in, the query takes about 0.2 - 0.4 seconds.  Removed it takes 0.001 to 0.002 seconds. I tried using FORCE INDEX again with a very reasonable improvement in speed to 0.02 to 0.04 seconds.  The query now looks like this: [code] SELECT t.topic_id, t.topic_title, t.topic_sticky, t.topic_views, t.num_posts, f.forum_icon, f.forum_name, p.poster_name, p.post_time FROM minibb_topics AS t FORCE INDEX(topic_last_post_id, topic_sticky) LEFT JOIN minibb_forums AS f on t.forum_id=f.forum_id INNER JOIN minibb_posts as p ON p.post_id = t.topic_last_post_id WHERE 5 >= forum_level_to_view && (forum_site = 0 || forum_site = 1) ORDER BY t.topic_sticky DESC, t.topic_last_post_id DESC LIMIT 20 [/code] And the explain looks like this: [code] 1  SIMPLE  t  ALL  topic_last_post_id  NULL  NULL  NULL  7819  Using filesort 1 SIMPLE f eq_ref PRIMARY,forum_level_to_view,forum_site PRIMARY 2 aanime_aanimedb.t.forum_id 1 Using where 1 SIMPLE p eq_ref PRIMARY PRIMARY 4 aanime_aanimedb.t.topic_last_post_id 1 [/code] From the look of the explain, it seems the query could still further be optimized.  Still, a x10 improvement in execution speed is quite good. Note that if I remove either of the ORDER BY conditions, the speed is improved to about 0.0015 seconds.  It does not matter if topic_sticky or topic_last_post_id is removed.

That does seem to be the case. I know there are times when mysql will do a filesort when there are very few rows, but in this case using an index is necessary and I cannot force it to use an index.  I feel like I need to restructure something, but I don't know where to start with this.

I've tried ordering by both, but there is no speed increase if I use ASC rather than DESC.

Thanks for the reply. It's a creative idea, but if I start restructuring my database through separate tables, I will have to rewrite a lot of my existing sql statements.  Also, it is indeed the most active forums that are shown. Is the size the reason why when I add "WHERE f.forum_id = 1" the statement executes quickly?

I cannot seem to properly optimize this statement used to show topics across all forums that have been recently replied to: SELECT t.topic_id, t.topic_title, t.topic_sticky, t.topic_views, t.num_posts, f.forum_icon, f.forum_name, p.poster_name, p.post_time FROM minibb_topics AS t LEFT JOIN minibb_forums AS f ON t.forum_id = f.forum_id INNER JOIN minibb_posts AS p ON p.post_id = t.topic_last_post_id WHERE 5 >= forum_level_to_view && ( forum_site =0 || forum_site =1 ) ORDER BY t.topic_sticky DESC , t.topic_last_post_id DESC LIMIT 20 I have isolated the issue to the ORDER BY clause.  If I remove the ORDER BY clause, the query takes 1/10th of the time to execute.  Both ORDER BY fields are indexed.  Also, if I limit the query to one forum such as, "WHERE f.forum_id = 1", the statement executes in 1/10th of the time. This is the explain: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE f ALL PRIMARY,forum_level_to_view,forum_site NULL NULL NULL 12 Using where; Using temporary; Using filesort 1 SIMPLE t ref forum_id,topic_last_post_id forum_id 2 aanime_aanimedb.f.forum_id 651 1 SIMPLE p eq_ref PRIMARY PRIMARY 4 aanime_aanimedb.t.topic_last_post_id 1 This is the table structure: -- -- Table structure for table 'minibb_forums' -- CREATE TABLE minibb_forums (   forum_id smallint(6) NOT NULL auto_increment,   forum_name varchar(150) NOT NULL default '',   forum_desc varchar(255) NOT NULL default '',   forum_order tinyint(2) NOT NULL default '0',   forum_icon varchar(255) NOT NULL default 'default.gif',   forum_group varchar(30) NOT NULL default '',   forum_site tinyint(1) NOT NULL default '0',   forum_level_to_view tinyint(2) unsigned NOT NULL default '0',   forum_level_to_post tinyint(2) NOT NULL default '0',   forum_level_to_topic tinyint(2) NOT NULL default '0',   num_topics mediumint(8) unsigned NOT NULL default '0',   num_posts mediumint(8) unsigned NOT NULL default '0',   is_spam tinyint(1) NOT NULL default '0',   PRIMARY KEY  (forum_id),   KEY forum_order (forum_order),   KEY forum_level_to_topic (forum_level_to_topic),   KEY forum_level_to_view (forum_level_to_view),   KEY forum_site (forum_site) ) ENGINE=MyISAM  DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table 'minibb_posts' -- CREATE TABLE minibb_posts (   post_id int(11) NOT NULL auto_increment,   topic_id int(10) NOT NULL default '1',   poster_name varchar(16) NOT NULL default 'Anonymous',   post_text text NOT NULL,   post_time datetime NOT NULL default '0000-00-00 00:00:00',   poster_ip varchar(15) NOT NULL default '',   post_status tinyint(1) NOT NULL default '0',   PRIMARY KEY  (post_id),   KEY topic_id (topic_id),   KEY poster_name (poster_name) ) ENGINE=MyISAM  DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table 'minibb_topics' -- CREATE TABLE minibb_topics (   topic_id int(10) NOT NULL auto_increment,   topic_title varchar(100) NOT NULL default '',   topic_sticky tinyint(1) NOT NULL default '0',   topic_poster_name varchar(16) NOT NULL default 'Anonymous',   topic_time datetime NOT NULL default '0000-00-00 00:00:00',   topic_views mediumint(9) NOT NULL default '0',   forum_id smallint(6) NOT NULL default '1',   topic_status tinyint(1) NOT NULL default '0',   topic_last_post_id int(10) NOT NULL default '1',   num_posts mediumint(8) unsigned NOT NULL default '0',   PRIMARY KEY  (topic_id),   KEY forum_id (forum_id),   KEY topic_poster_name (topic_poster_name),   KEY topic_sticky (topic_sticky),   KEY topic_last_post_id (topic_last_post_id) ) ENGINE=MyISAM  DEFAULT CHARSET=latin1; Does anyone have any ideas about this? Thanks.