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?
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 '19 at 10:02CREATE 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 '19 at 10:10