Mysql subquery slow-CodePudding

I have two tables that track sales:

orders                 order_line_items
------                 ----------------
id                     id
customer_id            order_id
created_datetime       item_id
                       quantity
                       was_paid_for

An order can have many order_line_items. For some orders, all of the line items have been paid for. For other orders, they have not all been paid for.

I am trying to fetch a list of all the orders for a specific customer, and indicate if the order was fully paid for, or not. I have it working with this query:

SELECT o.id,
       (SELECT count(*) from order_line_items WHERE order_id = o.id AND was_paid_for = 0) = 0 as isFullyPaid
FROM orders o
WHERE o.customer_id = 12345

However some customers have 1000 orders and the query takes 70 seconds to run (this is a simplified example, the real one joins in five other tables).

Is indexes the only way to speed this up? Thanks.

CodePudding user response：

You could try adding the following index to the order_line_items:

CREATE INDEX idx ON order_line_items(order_id, was_paid_for);

That being said, you also could try using the following join version of your query:

SELECT o.id, COALESCE(oli.cnt, 0) = 0 AS isFullyPaid
FROM orders o
LEFT JOIN
(
    SELECT order_id, COUNT(*) AS cnt
    FROM order_line_items
    WHERE was_paid_for = 0
    GROUP BY order_id
) oli
    ON oli.order_id = o.id;

The same index suggestion applied to the above join query.