Very slow query with: Using temporary; Using filesort

Very slow query with: Using temporary; Using filesort

I've performance problems with my select category query Restructuring a DB
for best performance So I set a goal to fix it. but at the end it turned
out to be a much more complex query and performance actually decreased
compared to original query.
SELECT *
FROM post
LEFT JOIN post_plus
ON ( post.id = post_plus.news_id )
INNER JOIN (SELECT DISTINCT c1.postid
FROM post_category c1
JOIN post_category c2
ON c1.postid = c2.postid
WHERE c1.categoryid IN ( 130, 3, 4, 5 )
AND c2.categoryid = 73) post_category
ON ( post_category.postid = post.id )
WHERE approve = 1
ORDER BY fixed DESC,
date DESC
LIMIT 0, 7;
the new query takes: (1.02 sec) - old query with regexp took (0.23 sec)
I can only guess it's because of the Using temporary; Using filesort
How can I get rid of this?
explain query:
`+----+-------------+---------------+--------+-------------------+------------+---------+--------------------------+-------+----------------------------------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra
|
+----+-------------+---------------+--------+-------------------+------------+---------+--------------------------+-------+----------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL
| NULL | NULL | 10470 | Using temporary; Using
filesort |
| 1 | PRIMARY | post | eq_ref | PRIMARY,approve | PRIMARY |
4 | post_category.postid | 1 | Using where
|
| 1 | PRIMARY | post_plus | ref | news_id | news_id |
5 | post_category.postid | 1 | NULL
|
| 2 | DERIVED | c1 | range | postId,categoryId |
categoryId | 2 | NULL | 10470 | Using index
condition; Using temporary |
| 2 | DERIVED | c2 | ref | postId | postId
| 4 | online_test.c1.postId | 1 | Using index
|
+----+-------------+---------------+--------+-------------------+------------+---------+--------------------------+-------+----------------------------------------+
`
post table
| post | CREATE TABLE `post` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`autor` varchar(40) NOT NULL DEFAULT '',
`date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`short_story` text NOT NULL,
`full_story` text NOT NULL,
`xfields` text NOT NULL,
`title` varchar(255) NOT NULL DEFAULT '',
`descr` varchar(200) NOT NULL DEFAULT '',
`keywords` text NOT NULL,
`category` varchar(200) NOT NULL DEFAULT '0',
`alt_name` varchar(200) NOT NULL DEFAULT '',
`comm_num` mediumint(8) unsigned NOT NULL DEFAULT '0',
`allow_comm` tinyint(1) NOT NULL DEFAULT '1',
`allow_main` tinyint(1) unsigned NOT NULL DEFAULT '1',
`approve` tinyint(1) NOT NULL DEFAULT '0',
`fixed` tinyint(1) NOT NULL DEFAULT '0',
`allow_br` tinyint(1) NOT NULL DEFAULT '1',
`symbol` varchar(3) NOT NULL DEFAULT '',
`tags` varchar(255) NOT NULL DEFAULT '',
`metatitle` varchar(255) NOT NULL DEFAULT '',
`FileTempUUID` varchar(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `autor` (`autor`),
KEY `alt_name` (`alt_name`),
KEY `category` (`category`),
KEY `allow_main` (`allow_main`),
KEY `date` (`date`),
KEY `symbol` (`symbol`),
KEY `comm_num` (`comm_num`),
KEY `tags` (`tags`),
KEY `descr` (`descr`),
KEY `title` (`title`),
KEY `approve` (`approve`,`allow_main`),
FULLTEXT KEY `full_story` (`full_story`),
FULLTEXT KEY `short_story` (`short_story`,`full_story`,`xfields`,`title`),
FULLTEXT KEY `title_2` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=32586 DEFAULT CHARSET=utf8 |
post_plus
| post_plus | CREATE TABLE `post_plus` (
`pid` int(11) NOT NULL AUTO_INCREMENT,
`news_id` int(11) DEFAULT NULL,
`kp_id` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`pdate` datetime DEFAULT NULL,
`news_read` int(11) NOT NULL DEFAULT '0',
`user_id` int(11) NOT NULL DEFAULT '0',
`allow_rate` tinyint(1) NOT NULL DEFAULT '1',
`rating` mediumint(8) NOT NULL DEFAULT '0',
`vote_num` mediumint(8) NOT NULL DEFAULT '0',
`votes` tinyint(1) NOT NULL DEFAULT '0',
`editdate` int(11) DEFAULT NULL,
`view_edit` tinyint(1) NOT NULL DEFAULT '0',
`editor` varchar(40) CHARACTER SET utf8 NOT NULL DEFAULT '',
`reason` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
`access` varchar(150) CHARACTER SET utf8 NOT NULL DEFAULT '',
PRIMARY KEY (`pid`),
KEY `user_id` (`user_id`),
KEY `news_id` (`news_id`)
) ENGINE=InnoDB AUTO_INCREMENT=32819 DEFAULT CHARSET=latin1 |
post_category table
| post_category | CREATE TABLE `post_category` (
`cid` bigint(11) NOT NULL AUTO_INCREMENT,
`postId` int(11) NOT NULL,
`categoryId` smallint(6) NOT NULL,
PRIMARY KEY (`cid`),
KEY `postId` (`postId`,`categoryId`),
KEY `categoryId` (`categoryId`)
) ENGINE=InnoDB AUTO_INCREMENT=100870 DEFAULT CHARSET=latin1 |