Is a performance improvement for this query with subselect possible?

Question

I have the following table (simplified non-sense example):

table fruits:
+-----+---------+-------------+
| id  | fruit   | customer_id |
+-----+---------+-------------+
| 1   | apple   | 1           |
| 2   | pear    | 2           |
| 3   | kiwi    | null        |
| 4   | mango   | 1           |
| 5   | guava   | 2           |
| 6   | kiwi    | 1           |
| 7   | kiwi    | 2           |
| 8   | khaki   | null        |
| 9   | date    | null        |
| 10  | date    | 1           |
+-----+---------+-------------+

Fruits can be assigned to a customer (customer_id). If they are not assigned to a customer (customer_id IS NULL), they are relevant for every customer.

Fruits can occur multiple times with the same name (but for a customer only once), i.e. such a thing does not occur in the table:

+---------+------------+
| fruit  | customer_id |
+-----+---------+------+
| apple   | 1          |
| apple   | 1          |
+-----+---------+------+

I use the following query to get all the fruits where the customer_id is null or the customer_id matches a certain value (in this example customer_id = 1). For fruits that occur twice (in this case kiwi and date), I want to get the rows where the customer_id is not null.

SELECT * FROM fruits WHERE customer_id = 1 OR (customer_id IS NULL AND fruit NOT IN (SELECT fruit FROM fruits WHERE customer_id = 1))

Result:

+-----+---------+-------------+
| id  | fruit   | customer_id |
+-----+---------+-------------+
| 1   | apple   | 1           |
| 4   | mango   | 1           |
| 6   | kiwi    | 1           |
| 8   | khaki   | null        |
| 10  | date    | 1           |
+-----+---------+-------------+

Is there a better query (better performing)? Thank you very much!

Answer 1

Two problematic constructs: OR and NOT IN ( SELECT ... ). This addresses both:

    (  SELECT  *
        FROM  fruits
        WHERE  customer_id = 1
    )
    UNION ALL
    (  SELECT *
        FROM  fruits AS f1
        WHERE  customer_id IS NULL
          AND  NOT EXISTS (
                SELECT  1
                    FROM  fruits AS f2
                    WHERE  f2.customer_id = 1
                      AND  f2.fruit = f1.fruit ) 
    ) 

fruits needs INDEX(customer_id, fruit)

Answer 2

There is a way to solve this with a single logical hit on the table, keeping the OR condition though. It may or may not work faster. The solution requires MySQL 8+ as it uses a CTE and the ROW_NUMBER function – features introduced in MySQL 8.

WITH
  enumerated AS
  (
    SELECT
      *
    , ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY customer_id DESC) AS r
    FROM
      fruits
    WHERE
      customer_id IS NULL
      OR customer_id = 1
  )
SELECT
  id
, fruit
, customer_id
FROM
  enumerated
WHERE
  r = 1
;

The enumerated CTE produces a rowset where each instance of fruit has a number based on the descending order of customer_id, where a null comes last. The main SELECT filters that rowset further by only getting the rows with the row number of 1. A row whose customer_id is null can get a row number of 1 only when the fruit is the only instance in the set. In all other cases, the non-null customer_id gets number 1.