Mysql – How to improve the query, using joins & improving performance

aggregatemysql-5.7pivot

I have a really smelly query which I'm not sure how to simplify, this is quite an intensive query. I have tried various union and inner joins in an attempt to simplify it and my biggest problem is getting around the following error:

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

My query below solves this but it is not very efficient.

What am I trying to achieve?

When a media item has been deleted, I wish to soft delete all products where that media is associated providing that product does not have any other media files associated.

Schema

+-----------+----------+
| entity_id | media_id |
+-----------+----------+
|       192 |        1 |
|       193 |        1 |
|       193 |        2 |
+-----------+----------+
  • I have a composite Primary Key index on entity_id and media_id
  • I have a Foreign Key constraint on entity_id to product table
  • I have a Foreign Key constraint on media_id to product_media table

When I delete media #1 I would like to be able to update entity_id #192 to set deleted_at to the current timestamp. I DO NOT want to update entity_id #193 because another media is associated to it.

UPDATE product
SET deleted_at = NOW()
WHERE id IN (
    SELECT entity_id
    FROM product_media
    WHERE media_id = ?
    AND entity_id NOT IN (
        SELECT entity_id
        FROM product_media
        GROUP BY entity_id HAVING COUNT(media_id) > 1
    )
)

As you can see there are two sub-queries, the fact I have 1 sub-query is somewhat of a concern but having two I know i'm doing something wrong but I cannot think for the life of my how to get around this?

Best Answer

You have an extra unnecessary subquery check, and your subquery seems to only return a rowset if there is a single value in it, so you may want to use = instead of in. You can achieve the same by doing this:

UPDATE product
  SET deleted_at = NOW()
WHERE id = (
    SELECT entity_id
    FROM product_media
    WHERE media_id = ?
    GROUP BY entity_id
    HAVING COUNT(media_id) = 1
)
Related Question