How to avoid running multiple selects with the same subquery join?

by Shahin   Last Updated August 14, 2019 09:06 AM

I am using MySQL 5.7.26.

I have 12 SELECT queries which run the same subquery join; since the subquery in a join is very complex (can be more complex, it is a filter query in a web app) I want to reuse "calculated" resultset through all my queries.

Here is one of my queries:

SELECT
    pc.categoryId,
    COUNT(pc.productId)
FROM
    product_categories pc
INNER JOIN
    (
    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
        )
) productsIds
ON
    pc.productId = productsIds.tmpProductId
GROUP BY
    pc.categoryId 

I looked around for solutions, a dba.stackexchange user offered to use temporary tables, which fits my case because temporary tables are available and accessible to the client that creates it, also get destroyed automatically.

My plan is as follows:

1) Create a temporary table:

CREATE TEMPORARY TABLE IF NOT EXISTS tempProducts(
    tmpProductId SMALLINT NOT NULL,
    PRIMARY KEY(tmpProductId)
)
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
    );

2) Then JOIN it, like:

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

EXPLAIN:

+----+-------------+--------------+------------+-------+--------------------------+------------------+---------+------------------------------+------+----------+----------------------------------------------+
| id | select_type | table        | partitions | type  | possible_keys            | key              | key_len | ref                          | rows | filtered | Extra                                        |
+----+-------------+--------------+------------+-------+--------------------------+------------------+---------+------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | tempProducts | NULL       | index | PRIMARY                  | PRIMARY          | 2       | NULL                         |    2 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | pc           | NULL       | ref   | PRIMARY,unq_catid_prodid | unq_catid_prodid | 2       | rr.tempProducts.tmpProductId |    2 |   100.00 | Using index                                  |
+----+-------------+--------------+------------+-------+--------------------------+------------------+---------+------------------------------+------+----------+----------------------------------------------+

Please, consider that the maximum amount of rows in a temporary table will be 1500-1700, not more. All of my tables are InnoDB with 90/10 read/write ratio.

Now, my questions are:

1) Is it wise using temporary tables in that situation? What else would you do if you were in my shoes?

2) Which MySQL settings I should tune for it? tmp_table_size, max_heap_table_size, innodb_buffer_pool_size?

3) Should I specify a memory engine in the CREATE TEMPORARY TABLE statement? Or it will be created in MEMORY and MySQL move it to the disk if it becomes too large? If MySQL move it to the disk which storage engine will be used? MySQL default?



Related Questions


Can't see temp table from subquery

Updated March 15, 2016 08:02 AM

mysql - calculate traffic for tmp tables

Updated October 22, 2018 15:06 PM

Slow CREATE TEMPORARY TABLE from SELECT in MySQL

Updated January 09, 2018 14:06 PM

MySQL: physical table vs temporary table

Updated May 27, 2016 09:02 AM