本文介绍了性能帮助:SUBQUERY与JOIN的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这是我的问题:
子查询2.7 secs
SUBQUERY 2.7 secs
SELECT SQL_NO_CACHE
item_id
FROM
mtrt_items_searches
WHERE
search_id IN (
SELECT
SQL_NO_CACHE
search_id
FROM
mtrt_searches_groups
WHERE
client_id =1
GROUP BY
search_id
)
LIMIT 0,350000
+----+--------------------+----------------------+-------+---------------+-----------+---------+-------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+----------------------+-------+---------------+-----------+---------+-------+--------+--------------------------+
| 1 | PRIMARY | mtrt_items_searches | index | NULL | search_id | 12 | NULL | 367362 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | mtrt_searches_groups | ref | client_id | client_id | 4 | const | 13 | Using where; Using index |
+----+--------------------+----------------------+-------+---------------+-----------+---------+-------+--------+--------------------------+
仅子查询需要0.0009秒才能返回以下数据，然后用此数据替换子查询，查询将在0.2 secs中运行:
The subquery alone takes 0.0009 secs to return the following data, and replacing the subquery with this data, the query runs in 0.2 secs:
SELECT SQL_NO_CACHE
item_id
FROM
mtrt_items_searches
WHERE
search_id IN (
1,2,3,4,5,6,7,8,9,10,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35
)
LIMIT 0,350000
+----+-------------+---------------------+-------+---------------+-----------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------+-------+---------------+-----------+---------+------+--------+--------------------------+
| 1 | SIMPLE | mtrt_items_searches | index | search_id | search_id | 12 | NULL | 367362 | Using where; Using index |
+----+-------------+---------------------+-------+---------------+-----------+---------+------+--------+--------------------------+
最后在0.4 secs中运行的JOIN:
Finally the JOIN running in 0.4 secs:
SELECT SQL_NO_CACHE
r.item_id
FROM
mtrt_items_searches r
INNER JOIN
mtrt_searches_groups sg
ON r.search_id =sg.search_id
WHERE
sg.client_id =1
GROUP BY
r.item_id
LIMIT 0,350000
+----+-------------+-------+------+---------------------+-----------+---------+------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------+-----------+---------+------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | sg | ref | search_id,client_id | client_id | 4 | const | 13 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | r | ref | search_id | search_id | 4 | clubr_new.sg.search_id | 26240 | Using index |
+----+-------------+-------+------+---------------------+-----------+---------+------------------------+-------+----------------------------------------------+
我正在尝试执行子查询或0.2 secs中的联接.有可能吗?
I'm trying to do either the subquery or the join in 0.2 secs. Is it possible?
推荐答案
尝试以下查询:
SELECT STRAIGHT_JOIN item_id
FROM (
SELECT DISTINCT search_id
FROM mtrt_searches_groups
WHERE client_id = 1
) JOIN mtrt_items_searches USING(search_id)
LIMIT 0,350000
这篇关于性能帮助:SUBQUERY与JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！