Performance help: SUBQUERY vs JOIN

Question

Performance help: SUBQUERY vs JOIN

it is my problem:

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

Only one subquery takes 0.0009 sec to return the following data and replace the subquery with this data, the query is executed 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 |
+----+-------------+---------------------+-------+---------------+-----------+---------+------+--------+--------------------------+

Finally, JOIN works 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                                  |
+----+-------------+-------+------+---------------------+-----------+---------+------------------------+-------+----------------------------------------------+

I am trying to execute either a subquery or a join in 0.2 secs

. Is it possible?

0

performance join mysql subquery

Keyne viana 19 Aug 11 at 6:20 am

source to share

3 answers

try adding the following indices

mtrt_items_searches(search_id,item_id)
mtrt_searches_groups(client_id, search_id)

+1

John Petrak 19 Aug 11 at 6:32 am

source to share

Mmm, I don't think so. Subqueries are too slow, unfortunately the fastest way is to use the data directly.

-1

juanmhidalgo 19 Aug 11 at 6:36 am

source to share

The scrum meister · Accepted Answer · 2011-08-19T07:10:57+0000

Try the following query:

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

Performance help: SUBQUERY vs JOIN

More articles: