mysql创建临时表慢_MySQL子查询和临时表很慢

我想优化以下查询：

SELECT SQL_NO_CACHE t.topic_id

FROM bb_topics t, bb_posters ps

WHERE t.topic_id = ps.topic_id

AND forum_id IN (2, 6, 7, 10, 15, 20)

ORDER BY ps.timestamp desc

LIMIT 20

Query took 0.1475 sec

所以一开始我用INNER JOIN子查询替换了WHERE IN：

SELECT SQL_NO_CACHE t.topic_id

FROM ( SELECT * FROM bb_topics WHERE forum_id IN (2, 6, 7, 10, 15, 20) ) t

INNER JOIN bb_posters ps ON t.topic_id = ps.topic_id

ORDER BY ps.timestamp desc

LIMIT 20

Query took 0.1541 sec

然后我尝试创建一个临时表：

CREATE TEMPORARY TABLE IF NOT EXISTS bb_topics_tmp ( INDEX(topic_id) )

ENGINE=MEMORY

AS ( SELECT * FROM bb_topics WHERE forum_id IN (2, 6, 7, 10, 15, 20) );

SELECT SQL_NO_CACHE t.topic_id

FROM bb_topics_tmp t, bb_posters ps

AND t.topic_id = ps.topic_id

ORDER BY ps.timestamp desc

LIMIT 20

Query took 0.1467 sec

我不明白为什么从一个包含38,522行的完整表中选择比从具有9,943行的临时表快得多：

SELECT SQL_NO_CACHE t.topic_id

FROM bb_topics t, bb_posters ps

WHERE t.topic_id = ps.topic_id

ORDER BY ps.timestamp desc

LIMIT 20

Query took 0.0006 sec

topic_id和timestamp都有索引.

有趣的是,即使使用这样的东西也比论坛列表快得多：

AND pt.post_text LIKE '%searchterm%'

UPD：

这是EXPLAIN的输出：

SELECT SQL_NO_CACHE t.topic_id, t.topic_title, ps.timestamp, u.username,

u.user_id, ps.size, ps.downloaded, ROUND(a.rating_sum/a.rating_count) AS Rating,

a.attach_id, pt.bbcode_uid, pt.post_text

FROM bb_topics t

JOIN bb_posters ps ON ps.topic_id = t.topic_id

LEFT JOIN bb_users u ON u.user_id = t.topic_poster

LEFT JOIN bb_posts_text pt ON pt.post_id = bt.post_id

LEFT JOIN bb_attachments_desc a ON bt.attach_id = a.attach_id

WHERE t.forum_id IN (2, 6, 7, 10, 15, 20)

ORDER BY ps.timestamp desc

LIMIT 1, 20

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE t range PRIMARY,forum_id forum_id 2 NULL 8379 Using where; Using temporary; Using filesort

1 SIMPLE ps eq_ref topic_id topic_id 3 DB.t.topic_id 1

1 SIMPLE u eq_ref PRIMARY PRIMARY 3 DB.t.topic_poster 1 Using index

1 SIMPLE pt eq_ref PRIMARY PRIMARY 3 DB.bt.post_id 1 Using index

1 SIMPLE a eq_ref PRIMARY PRIMARY 3 DB.bt.attach_id 1 Using index

Query took 0.8527 sec

没有WHERE t.forum_id IN的相同查询：

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE ps index topic_id timestamp 4 NULL 21

1 SIMPLE t eq_ref PRIMARY PRIMARY 3 DB.bt.topic_id 1

1 SIMPLE u eq_ref PRIMARY PRIMARY 3 DB.t.topic_poster 1

1 SIMPLE pt eq_ref PRIMARY PRIMARY 3 DB.bt.post_id 1

1 SIMPLE a eq_ref PRIMARY PRIMARY 3 DB.bt.attach_id 1

Query took 0.0022 sec

UPD 2：

添加USE INDEX(时间戳)解决了问题：

SELECT SQL_NO_CACHE t.topic_id, t.topic_title, ps.timestamp, u.username,

u.user_id, ps.size, ps.downloaded, ROUND(a.rating_sum/a.rating_count) AS Rating,

a.attach_id, pt.bbcode_uid, pt.post_text

FROM bb_topics t

JOIN bb_posters ps USE INDEX (timestamp) ON ps.topic_id = t.topic_id

LEFT JOIN bb_users u ON u.user_id = t.topic_poster

LEFT JOIN bb_posts_text pt ON pt.post_id = bt.post_id

LEFT JOIN bb_attachments_desc a ON bt.attach_id = a.attach_id

WHERE t.forum_id IN (2, 6, 7, 10, 15, 20)

ORDER BY ps.timestamp desc

LIMIT 1, 20

Query took 0.0023 sec