Edit Question - KnowledgeHub

Title Body

I have two tables: CREATE TABLE `articles` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(1000) DEFAULT NULL, `last_updated` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `last_updated` (`last_updated`), ) ENGINE=InnoDB AUTO_INCREMENT=799681 DEFAULT CHARSET=utf8 CREATE TABLE `article_categories` ( `article_id` int(11) NOT NULL DEFAULT '0', `category_id` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`article_id`,`category_id`), KEY `category_id` (`category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | This is my query: SELECT a.* FROM articles AS a, article_categories AS c WHERE a.id = c.article_id AND c.category_id = 78 AND a.comment_cnt > 0 AND a.deleted = 0 ORDER BY a.last_updated LIMIT 100, 20 And an EXPLAIN for it: *************************** 1. row *************************** id: 1 select_type: SIMPLE table: a type: index possible_keys: PRIMARY key: last_updated key_len: 9 ref: NULL rows: 2040 Extra: Using where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: c type: eq_ref possible_keys: PRIMARY,fandom_id key: PRIMARY key_len: 8 ref: db.a.id,const rows: 1 Extra: Using index It uses a full index scan of last_updated on the first table for sorting but does not use any index for join ( type: index in explain). This is very bad for performance and kills the whole database server since this is a very frequent query. I've tried reversing table order with STRAIGHT_JOIN , but this gives filesort, using_temporary , which is even worse. Is there any way to make MySQL use index for joining and for sorting at the same time? === update ===</stro

Tags (comma-separated) Cancel