我的选择类别查询有性能问题 Restructuring a DB for best performance 所以我设定了一个目标来解决它。但最后发现它是一个更复杂的查询，与原始查询相比，性能实际上有所下降。

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;           

新查询需要:(1.02 秒)- 使用正则表达式的旧查询花费了(0.23 秒)

我只能猜测是因为 Using temporary;使用文件排序

我怎样才能摆脱这个？

解释查询:

`+----+-------------+---------------+--------+-------------------+------------+---------+--------------------------+-------+----------------------------------------+
| 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 | 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 表

| 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 |

最佳答案

我认为这个查询在精神上是等价的，并且可能执行得更快。特别是如果您在 post_category (postid, categoryid)

Select
    *
From
    post
        Left Join
    post_plus
        On post.id = post_plus.news_id
Where
    Exists (
        Select
            'x'
        From
            post_category c1
        Where
            categoryid = 73 and
            c1.postid = post.postid
    ) And
    Exists (
        Select
            'x'
        From
            post_category c2
        Where
            categoryid In (130, 3, 4, 5) and
            c2.postid = post.postid
    ) And
    approve = 1
Order By
    fixed Desc,
    date Desc
Limit
    0, 7;

由于postid, categoryid在post_category上是唯一的，你也可以这样改写。它可能会受益于相反顺序的索引 (categoryid, postid):

Select
    post.*,
    post_plus.*
From
    post_category c1
        Inner Join
    post
        On c1.categoryid = 73 and c1.postid = post.postid
        Left Join
    post_plus
        On post.id = post_plus.news_id
Where
    Exists (
        Select
            'x'
        From
            post_category c2
        Where
            categoryid In (130, 3, 4, 5) and
            c2.postid = post.postid
    ) And
    approve = 1
Order By
    fixed Desc,
    date Desc
Limit
    0, 7;

关于php - : <derived2> Using temporary; Using filesort 查询非常慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/18689011/