Subquery caching in join

0 votes
0 answers
1259 views
                          I have very complex MySQL queries that use the same subquery over and over again. I was wondering if MySQL will run the subquery multiple times or use the previous resultset. Is there any way to tell MySQL to save or cache the results?

My query is as follows:

 

    SELECT
        pc.categoryId,
        COUNT(pc.productId)
    FROM
        product_categories pc
    INNER JOIN
        (
        SELECT
            t1.productId AS s1
        FROM
            product_categories t1
        JOIN
            product_highlights t2
        ON
            t1.productId = t2.productId
        WHERE
            t1.categoryId IN(7, 5) AND t2.highlightId IN(9, 10, 11)
        GROUP BY
            t1.productId
        HAVING
            2 * 3 = COUNT(
                DISTINCT t1.categoryId,
                t2.highlightId
            )
    ) productsIds
    ON
        pc.productId = productsIds.s1
    GROUP BY
        pc.categoryId


**UPDATE:** 

Here is EXPLAIN query: 

    +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------------------------+
    | id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                                           |
    +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------------------------+
    |  1 | PRIMARY     |  | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    4 |   100.00 | Using temporary; Using filesort                                 |
    |  1 | PRIMARY     | pc         | NULL       | index | PRIMARY       | PRIMARY | 4       | NULL |    5 |    20.00 | Using where; Using index; Using join buffer (Block Nested Loop) |
    |  2 | DERIVED     | t1         | NULL       | index | PRIMARY       | PRIMARY | 4       | NULL |    5 |    80.00 | Using where; Using index; Using temporary; Using filesort       |
    |  2 | DERIVED     | t2         | NULL       | index | PRIMARY       | PRIMARY | 4       | NULL |    7 |    14.29 | Using where; Using index; Using join buffer (Block Nested Loop) |
    +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------------------------------------------+
    4 rows in set, 1 warning (0.00 sec)


SQL Fiddle  of my database.



**UPDATE 2:**

Temporary table query: 

    CREATE TEMPORARY TABLE IF NOT EXISTS tempProducts
    (
        tmpProductId SMALLINT NOT NULL,
        PRIMARY KEY(tmpProductId)
    ) ENGINE = MEMORY 
   
    SELECT
        t1.productId AS tmpProductId
    FROM
        product_categories t1
    JOIN product_highlights t2 ON
        t1.productId = t2.productId
    WHERE
        t1.categoryId IN(7, 5) AND t2.highlightId IN(9, 10, 11)
    GROUP BY
        t1.productId
    HAVING
        2 * 3 = COUNT(
            DISTINCT t1.categoryId,
            t2.highlightId
        );

Temp table will look like:

    +--------------+
    | tmpProductId |
    +--------------+
    |            1 |
    |            2 |
    +--------------+

> show index from tempProducts where Key_name = 'PRIMARY' 

   

    +--------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table        | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +--------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | tempproducts |          0 | PRIMARY  |            1 | tmpProductId | NULL      |           2 |     NULL | NULL   |      | HASH       |         |               |
    +--------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


**UPDATE 3:**

Query with temporary table: 

    SELECT
        categoryId,
        COUNT(productId)
    FROM
        product_categories tb1
    INNER JOIN tempProducts tb2 ON
        tb1.productId = tb2.tmpProductId
    GROUP BY
        categoryId;

I added composite UNIQUE index to product_categories table: 
UNIQUE(productId, categoryId)

EXPLAIN query:

    +------+-------------+-------+--------+-----------------------------+---------+---------+------------------+------+-------------+
    | id   | select_type | table | type   | possible_keys               | key     | key_len | ref              | rows | Extra       |
    +------+-------------+-------+--------+-----------------------------+---------+---------+------------------+------+-------------+
    |    1 | SIMPLE      | tb1   | index  | productId_categoryId_unique | PRIMARY | 4       | NULL             |    5 | Using index |
    |    1 | SIMPLE      | tb2   | eq_ref | PRIMARY                     | PRIMARY | 2       | ss.tb1.productId |    1 |             |
    +------+-------------+-------+--------+-----------------------------+---------+---------+------------------+------+-------------+
                        
Asked by Shahin (65 rep)
Jul 24, 2019, 07:58 AM
Last activity: Jul 27, 2019, 08:15 AM
Subquery caching in join

Related Questions
