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 | <derived2> | 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 | |
+------+-------------+-------+--------+-----------------------------+---------+---------+------------------+------+-------------+
EXPLAIN SELECT ...
against the whole query. With that we can discuss this particular case, then discuss other possible cases. – Rick James Jul 24 '19 at 23:09VIEWs
are syntactic sugar, not an Optimization. – Rick James Jul 24 '19 at 23:10Memory
, will it cause performance problems? I can spare enough space for MySQL heap. – Shahin Jul 25 '19 at 8:57EXPLAIN
query. Please, take a look at it. – Shahin Jul 25 '19 at 10:33