0

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 |             |
+------+-------------+-------+--------+-----------------------------+---------+---------+------------------+------+-------------+
  • I was wondering if MySQL will run the subquery multiple times or use the previous resultset. Yes. Is there any way to tell MySQL to save or cache the results? If your queries which uses this subq are executed one-by-one in the same connection than you can store subq result into temporary table (and maybe ever create some indices on it) than use it in your queries. Of course you can try to convert this subq to view but I'm not sure that its result will be cached into query cache and re-used. – Akina Jul 24 at 8:03
  • Please provide EXPLAIN SELECT ... against the whole query. With that we can discuss this particular case, then discuss other possible cases. – Rick James Jul 24 at 23:09
  • VIEWs are syntactic sugar, not an Optimization. – Rick James Jul 24 at 23:10
  • @Akina, thank you for your reply. Actually, the temp table perfectly fits my case. But I am concerning about performance. Approximately 1500 rows will be in a temp table with one smallint, PK column, which equals (1500 x 2bytes) 3 kilobytes. If I choose the engine of my temp table as Memory, will it cause performance problems? I can spare enough space for MySQL heap. – Shahin Jul 25 at 8:57
  • @RickJames I added EXPLAIN query. Please, take a look at it. – Shahin Jul 25 at 10:33

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.