1

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 with required tables, 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 around 1500-1700 rows, 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 storage engine in the CREATE TEMPORARY TABLE statement? Or it will be created in MEMORY and MySQL will move it to the disk if it becomes too large? If MySQL will move it to the disk which storage engine will be used? Perhaps MySQL default?

  • Temptable is local for a connection. If your app opens separate connection for each query, uses connection pool, or connection will break and app needs to reconnect - the temptable will be unavailable. The best way is to create static table (and store its last update timestamp in some service table) with ENGIME = Memory, and check that the data in current table is not ancient enough for to use its data without refreshing. The table with 1500-1700 SMALLINTINTs is compact and do not need in any optimization. – Akina Aug 14 at 10:02
  • Have you considered using CTEs? – Vérace Aug 14 at 10:05
  • @Akina, CREATE TEMPORARY TABLE will be executed only once and after that, all queries will be executed in the same session one by one. Is it possible the temptable be unavailable? Creating a static table is not good, because when a user clicks on a filter element the new table must be created. – Shahin Aug 14 at 10:10
  • Is it possible the temptable be unavailable? Connection may be lost or broken. After reconnect you must create/fill temptable again, then query it. Creating a static table is not good, because when a user clicks on a filter element the new table must be created. Unclear... when user clicks you need to forget existing data from temptable andd to get another data using another filter values? TRUNCATE, then SELECT... – Akina Aug 14 at 10:13
  • @Akina, I got your point, sir. You are suggesting to create a static table with MEMORY engine and populate it. However, I did a quick research on lost and broken connection problem, according to MySQL docs it can happen due to network connectivity problem, lots of rows as a part of a query or connection time out. What else can cause lost or broken connection? I am really worried about lots of static tables in RAM, at specific intervals a cleaner bot must run, etc.. – Shahin Aug 14 at 10:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.