MySQL with subquery becomes slow

Question

I have 3 MySQL tables: products, orders and users.

I want to show all ordered products where not all products are "pre-orders" or "discounts". Note: one user can have multiple orders.

I use a subquery to count products with "discount" or "preorder" in the title and compare that with a count of all ordered products. If the result is not the same, I show all products.

Working query, but slow

This query works just fine. But it's very slow, while the query without the comparison (subquery) was very fast. There are 215303 rows in the orders table and 8406 rows in the products table.

SELECT * FROM products p1,orders o1 
LEFT JOIN users ON users.id = o1.user_id 
WHERE o1.barcode = p1.barcode AND 
(SELECT count(orders.barcode) FROM orders, products WHERE orders.barcode=products.barcode AND order_id=o1.order_id AND (title LIKE('%preorder%') OR title LIKE('%discount%'))) 
<> 
(SELECT count(barcode) FROM orders WHERE order_id=o1.order_id)
ORDER BY order_date

Sample data

table : users
+------+-----------+
|  id  |   name    |
+------+-----------+
|  1   |   User 1  |
|  2   |   User 2  |
|  3   |   User 3  |
+------+-----------+

table : products p1
+-----------+-----------------------+
|  barcode  | title                 |
+-----------+-----------------------+
|  A        | 7 Wonders: Architects |
|  B        | PREORDER - Frosthaven |
|  C        | Discount 10%          |
|  D        | Lords of Waterdeep    |
+-----------+-----------------------+

table : orders o1
+-----------+----------+-----------+--------------+
|  order_id |  user_id |  barcode  | order_date   |
+-----------+----------+-----------+--------------+
|  1        |  1       |  A        | 2023-02-11   |
|  1        |  1       |  C        | 2023-02-11   |
|  2        |  2       |  A        | 2023-02-07   |
|  2        |  2       |  B        | 2023-02-07   |
|  2        |  2       |  C        | 2023-02-07   |
|  3        |  3       |  B        | 2023-02-10   |
|  3        |  3       |  C        | 2023-02-10   |
|  4        |  1       |  D        | 2023-02-05   |
|  5        |  2       |  B        | 2023-02-04   |
+-----------+----------+-----------+--------------+

Desired outcome

I want to show all ordered products where not all products are "pre-orders" or "discounts", orderd by order_date.

+-----------+----------+-----------------------+--------------+
|  order_id |  user_id | title                 | order_date   |
+-----------+----------+-----------------------+--------------+
|   5       |  1       | Lords of Waterdeep    | 2023-02-05   |
|   2       |  2       | 7 Wonders: Architects | 2023-02-07   |
|   2       |  2       | PREORDER - Frosthaven | 2023-02-07   |
|   2       |  2       | Discount 10%          | 2023-02-07   |
|   1       |  1       | 7 Wonders: Architects | 2023-02-11   |
|   1       |  1       | Discount 10%          | 2023-02-11   |
+-----------+----------+-----------------------+--------------+

Answer 1

Use a subquery to get all order_id's of orders with at least an eligible product, then join it with the rest.

SELECT * 
FROM products p1 JOIN orders o1 ON o1.barcode = p1.barcode 
JOIN users ON users.id = o1.user_id 
JOIN (SELECT order_id FROM orders 
      JOIN products ON  orders.barcode=products.barcode 
      GROUP BY order_id 
      HAVING count(case when title NOT LIKE('%preorder%') AND title NOT LIKE('%discount%') then 1 end) > 0
     ) filtered_orders ON o1.order_id = filtered_orders.order_id
ORDER BY order_date

EDIT: In the subquery you can actually filter out the unwanted products and just get the orders with at least a valid product, without the need to count anything.

SELECT * 
FROM products p1 JOIN orders o1 ON o1.barcode = p1.barcode 
JOIN users ON users.id = o1.user_id 
JOIN (SELECT DISTINCT order_id FROM orders 
      JOIN products ON orders.barcode=products.barcode 
      WHERE title NOT LIKE('%preorder%') AND title NOT LIKE('%discount%')
     ) filtered_orders ON o1.order_id = filtered_orders.order_id
ORDER BY order_date

Of course you need indexes on products(barcode), users(user_id), orders(order_id), but also an index on orders(barcode,order_id) could speed up things.

Answer 2

First, I'll give you a hint of an answer:

Since the LIKE will be the slowest part, let's start with "count items with "discount" or "preorder" in the title and compare that with a count of all ordered items. If the result is not the same, I show all items."

That should be equivalent to "items ordered without 'discount' or 'preorder'". I'll probably use NOT EXISTS ( SELECT ... ) or LEFT JOIN ... WHERE ... IS NULL

Second, I need a clarification:

I can't go any further because I don't see a table or column named "item". Please tell me what I should be showing all of.

That is, the first step is to find the ids of the product line(s) with no discounts.

The second step is to JOIN that back to the table(s) to get the desired orders.