Mysql select query with subqueries with index taking long time to excute about(28 sec)

Question

I am trying to run below query on MYsql, The query is taking too long to run. I need to extract for each supplier:

total_purchases
total_sales

If supplier has an opening balance different from null, use it in where clause in subqueries, else use '2020-01-01'

Here is the query I am using:

SELECT 
sup.name AS supplier_name,
sup.id AS supplier_id,
sup.opening_balance_date,
sup.opening_balance AS opening_balance,
(
    SELECT
    IFNULL(SUM(pop.quantity * pop.cost),0)   AS total_purchases
    FROM purchase_order_products pop
    JOIN purchase_order po ON
    pop.purchase_order_id = po.id
    WHERE
    DATE(po.created_at) >= IFNULL(sup.opening_balance_date, '2020-01-01')  
    AND DATE(po.created_at) < '2020-03-01'
    AND po.status = 'approved'
    AND po.supplier_id = sup.id
) as total_purchases,
(
    SELECT 
    IFNULL(sum(soi.total_cost),0) AS total_sales              
    FROM
    sales_order_item soi 
    JOIN sales_order so use index (date_status_completed) ON
    so.id = soi.sales_order_id
    WHERE
    soi.total_cost > 0
    AND soi.supplier_id = sup.id
    AND so.order_status = 'complete' 
    AND so.completed_returned = 0
    AND so.desired_delivery_date >= IFNULL(sup.opening_balance_date, '2020-01-01')   
) AS total_sales
FROM supplier sup
WHERE sup.is_active = 1
group by sup.id
ORDER BY sup.name;

If I run the query without the below subquery, it tooks 0.5 sec which is accepted.

(
SELECT 
IFNULL(sum(soi.total_cost),0) AS total_sales              
FROM
sales_order_item soi 
JOIN sales_order so use index (date_status_completed) ON
so.id = soi.sales_order_id
WHERE
soi.total_cost > 0
AND soi.supplier_id = sup.id
AND so.order_status = 'complete' 
AND so.completed_returned = 0
AND so.desired_delivery_date >= IFNULL(sup.opening_balance_date, '2020-01-01')   
) AS total_sales

I think the problem is here: soi.supplier_id = sup.id

here is the explain

and here the database

Create Query:

--
-- Table structure for table `purchase_order`
--

CREATE TABLE `purchase_order` (
  `id` int(11) NOT NULL,
  `supplier_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` varchar(150) DEFAULT 'Pending',
  `total` float NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `purchase_order_products`
--

CREATE TABLE `purchase_order_products` (
  `id` int(11) NOT NULL,
  `purchase_order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(50) NOT NULL,
  `cost` decimal(15,3) NOT NULL,
  `reporting_quantity` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------


--
-- Table structure for table `sales_order`
--

CREATE TABLE `sales_order` (
  `id` varchar(20) NOT NULL,
  `order_status` varchar(50) DEFAULT NULL,
  `desired_delivery_date` date DEFAULT NULL,
  `completed_returned` int(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `sales_order_item`
--

CREATE TABLE `sales_order_item` (
  `id` varchar(20) NOT NULL,
  `sales_order_id` varchar(20) NOT NULL,
  `total_cost` float NOT NULL DEFAULT '0',
  `supplier_id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `supplier`
--

CREATE TABLE `supplier` (
  `id` int(11) NOT NULL,
  `name` varchar(400) NOT NULL,
  `opening_balance` decimal(10,2) NOT NULL DEFAULT '0.00',
  `opening_balance_date` date DEFAULT NULL,
  `is_active` tinyint(4) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------


--
-- Indexes for dumped tables
--

--
-- Indexes for table `credit_note`

--
-- Indexes for table `purchase_order`
--
ALTER TABLE `purchase_order`
  ADD PRIMARY KEY (`id`),
  ADD KEY `id` (`id`),
  ADD KEY `supplier_id` (`supplier_id`),
  ADD KEY `created_at` (`created_at`,`status`),
  ADD KEY `supplier_id_2` (`supplier_id`,`created_at`),
  ADD KEY `id_date_status` (`supplier_id`,`created_at`,`status`) USING BTREE;

--
-- Indexes for table `purchase_order_products`
--
ALTER TABLE `purchase_order_products`
  ADD PRIMARY KEY (`id`),
  ADD KEY `purchase_order_id` (`purchase_order_id`),
  ADD KEY `product_id` (`product_id`),
  ADD KEY `cost` (`cost`,`reporting_quantity`);

--
-- Indexes for table `sales_order`
--
ALTER TABLE `sales_order`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `idx_id` (`id`),
  ADD KEY `idx_desired_delivery_date` (`desired_delivery_date`),
  ADD KEY `date_status_completed` (`order_status`,`desired_delivery_date`,`completed_returned`) USING BTREE,
  ADD KEY `completed_returned` (`completed_returned`),
  ADD KEY `order_status` (`order_status`),
  ADD KEY `order_status_2` (`order_status`,`completed_returned`);

--
-- Indexes for table `sales_order_item`
--
ALTER TABLE `sales_order_item`
  ADD PRIMARY KEY (`id`),
  ADD KEY `sales_order_id` (`sales_order_id`),
  ADD KEY `reporting_supplier` (`supplier_id`) USING BTREE,
  ADD KEY `total_cost` (`total_cost`),
  ADD KEY `supplier_cost` (`supplier_id`,`total_cost`) USING BTREE,
  ADD KEY `supplier_id` (`supplier_id`);

--
-- Indexes for table `supplier`
--
ALTER TABLE `supplier`
  ADD PRIMARY KEY (`id`),
  ADD KEY `id` (`id`),
  ADD KEY `is_active` (`is_active`);


--
-- AUTO_INCREMENT for table `purchase_order`
--
ALTER TABLE `purchase_order`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=26763;
--
-- AUTO_INCREMENT for table `purchase_order_products`
--
ALTER TABLE `purchase_order_products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=41884;

--
-- AUTO_INCREMENT for table `supplier`
--
ALTER TABLE `supplier`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=182;

user_1766831 · Answer

A PRIMARY KEY is a unique key:

ADD PRIMARY KEY (`id`),
ADD KEY `id` (`id`),  -- Toss this; it is redundant

Do not mix INT and VARCHAR:

so.id = soi.sales_order_id

Don't use DATE() when you don't need to:

DATE(po.created_at) < '2020-03-01'

-->

po.created_at < '2020-03-01'

When you have INDEX(a,b), you don't also need INDEX(a).

Some of the above may be severely impacting preformance.

Here are some composite indexes that may help:

sup:  (is_active, id)
so:  (completed_returned, order_status, desired_delivery_date, id)
soi:  (supplier_id, total_cost, sales_order_id)
po:  (supplier_id, status, created_at, id)

user_2962756 · Answer

There's not enough information to answer this, but it looks like the USE INDEX clause you're using in the second subquery may be forcing MySQL to scan more rows than necessary. Try removing use index (date_status_completed).

user_4232381 · Answer

A revision to the query tested on mysql version 8.0.19, assuming the purchase_order and sales_order_item tables to be sparse against each other but both (should have a FOREIGN KEY) reference the supplier table (but not necessarily any of the same records on supplier).

You can trim down the number of necessary scans by pushing down either one of purchase_order or sales_order_item into a single EXISTS subquery and then converting the other into LEFT OUTER JOINs.
The functionality of SUM() obviates the need for a null-coalesce operation for purchase_order_product.
Because sales_order.completed_returned is a BOOLEAN INT, we can combine it into the (1-so.completed_returned) to convert the total_cost of 'returned' items into harmless zeroes in the aggregation; GREATEST() likewise obviates the need for the total_cost >0 filter criteria.
Additionally, if every purchase_order supplier is also a sales_order_supplier then you can replace LEFT OUTER JOIN with INNER JOINs for a proper output.
Finally, as @RickJames noted, don't bother using DATE() here either, since the conditions already imply it.

SELECT sup.name AS supplier_name, sup.id AS supplier_id, MIN(sup.opening_balance_date) AS opening_balance_date, MIN(sup.opening_balance) AS opening_balance, SUM( pop.quantity * pop.cost ) as total_purchases, SUM( COALESCE( GREATEST( soi.total_cost, 0) * (1-so.completed_returned),0) ) AS total_sales
FROM (( supplier sup, purchase_order_products pop ) LEFT OUTER JOIN sales_order_item soi ON soi.supplier_id = sup.id ) LEFT OUTER JOIN sales_order so ON so.id = soi.sales_order_id AND so.order_status = 'complete' AND so.desired_delivery_date >= COALESCE(sup.opening_balance_date, '2020-01-01')
WHERE sup.is_active = 1 AND EXISTS ( SELECT 1 FROM purchase_order po WHERE po.created_at >= COALESCE(sup.opening_balance_date, '2020-01-01')
AND po.created_at < '2020-03-01' AND po.status = 'approved' AND po.supplier_id = sup.id AND pop.purchase_order_id <=> po.id ) group by sup.id, sup.name ORDER BY sup.name;

EXPLAIN FORMAT=TREE

-> Sort: .name
    -> Table scan on 
        -> Aggregate using temporary table
            -> Nested loop inner join  (cost=1.75 rows=1)
                -> Nested loop inner join  (cost=1.40 rows=1)
                    -> Nested loop left join  (cost=1.05 rows=1)
                        -> Nested loop left join  (cost=0.70 rows=1)
                            -> Index lookup on sup using is_active (is_active=1)  (cost=0.35 rows=1)
                            -> Index lookup on soi using reporting_supplier (supplier_id=sup.id)  (cost=0.35 rows=1)
                        -> Filter: ((so.order_status = \'complete\') and (so.desired_delivery_date >= coalesce(sup.opening_balance_date,\'2020-01-01\')))  (cost=0.35 rows=1)
                            -> Single-row index lookup on so using PRIMARY (id=soi.sales_order_id)  (cost=0.35 rows=1)
                    -> Filter: ((po.created_at >= coalesce(sup.opening_balance_date,\'2020-01-01\')) and (po.created_at < TIMESTAMP\'2020-03-01 00:00:00\') and (po. = \'approved\'))  (cost=0.35 rows=1)
                        -> Index lookup on po using supplier_id (supplier_id=sup.id)  (cost=0.35 rows=1)
                -> Index lookup on pop using purchase_order_id (purchase_order_id=po.id), with index condition: (pop.purchase_order_id <=> po.id)  (cost=0.35 rows=1)

See if this might work better in your use case - it's hard to know exactly how a re-work will play out without knowing the contents of all the tables involved.

A cursory look at the EXPLAIN plan indicates that further optimization might be possible by employing hints, i.e. SEMIJOIN and BNL/NO_NBL optimizations for this query in particular.

Mysql select query with subqueries with index taking long time to excute about(28 sec)

3 Answers