how to improve performance for this subquery-CodePudding

I use this query to get result with aggregate subqueries but the performance is very bad , it take about 12 seconds although rows is ONLY 92 , tborders table has about 37000 row but it think it's two much to take all this time

can you suggest some improvements to this query ?

select id,  group_name,merchant_id,
                   (select count(id) from tborders where tborders.group_id = og.id)    as ords_count,
                   (select SUM(order_price) from tborders where tborders.group_id = og.id)          as total_order_price,
                   (select SUM(delivery_price) from tborders where tborders.group_id = og.id) as total_delivery_price
            from tborders_groups og

also this is the schema for this table

CREATE TABLE `tborders_groups` (
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `merchant_id` int(11) DEFAULT NULL,
  `group_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='orders groubs';

--
-- Dumping data for table `tborders_groups`
--


--
-- Indexes for table `tborders_groups`
--
ALTER TABLE `tborders_groups`
  ADD PRIMARY KEY (`id`),
  ADD KEY `id` (`id`),
  ADD KEY `merchant_id` (`merchant_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tborders_groups`
--
ALTER TABLE `tborders_groups`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=95;
COMMIT;

this is the schema for tborders

CREATE TABLE `tborders` (
  `id` int(11) NOT NULL,
  `device_type` varchar(255) DEFAULT 'android',
  `order_code` int(20) NOT NULL,
  `order_status` tinyint(1) NOT NULL DEFAULT 1,
  `merchant_id` int(11) NOT NULL,
  `driver_id` int(11) DEFAULT NULL,
  `client_id` int(11) DEFAULT 0,
  `from_lng` decimal(9,6) DEFAULT NULL,
  `to_lat` decimal(8,6) DEFAULT NULL,
  `to_lng` decimal(9,6) DEFAULT NULL,
  `distance` int(11) DEFAULT NULL,
  `speed` int(11) DEFAULT NULL,
  `orders_count` tinyint(1) NOT NULL DEFAULT 0,
  `order_address` varchar(500) CHARACTER SET utf8 NOT NULL,
  `canceled_at` time DEFAULT NULL,
  `taken_at` time DEFAULT NULL,
  `canceled_by` enum('merchant','driver') DEFAULT NULL,
  `accepted_at` time DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `from_lat` decimal(8,6) DEFAULT NULL,
  `order_price` int(11) NOT NULL DEFAULT 0,
  `order_type` tinyint(1) NOT NULL DEFAULT 2,
  `group_id` int(10) UNSIGNED NOT NULL,
  `client_address` varchar(100) CHARACTER SET utf8 NOT NULL,
  `delivery_price` int(10) UNSIGNED NOT NULL,
  `notes` varchar(100) CHARACTER SET utf8 NOT NULL,
  `has_paid` tinyint(1) NOT NULL DEFAULT 0,
  `finished_at` timestamp NULL DEFAULT NULL,
  `finished_attt` timestamp NULL DEFAULT current_timestamp(),
  `area_id` tinyint(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tborders`
--

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tborders`
--
ALTER TABLE `tborders`
  ADD PRIMARY KEY (`id`),
  ADD KEY `id` (`id`),
  ADD KEY `order_status` (`order_status`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tborders`
--
ALTER TABLE `tborders`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=37169;
COMMIT;

CodePudding user response：

You should use join & group by for this query:

select og.id,  og.group_name,og.merchant_id, count(tb.id) as ords_count, 
    sum(tb.order_price) as total_order_price, sum(tb.delivery_price) as total_delivery_price
from tborders_groups og
join tborders tb on tb.group_id=og.id
group by og.id,  og.group_name,og.merchant_id

Anyways, the time for a query doesn´t depend (only) in the amount of rows for the answer. It depends on the time for processing the query to analyse all the dataset to get those rows.

And for your indexes, this should be your indexes for tborders:

ALTER TABLE `tborders`
  ADD PRIMARY KEY (`id`),
  ADD KEY `group_id` (`group_id`),
  ADD KEY `order_status` (`order_status`);

It doesn´t make sense to make id as primary key and as key at the same time because primary keys are keys that are unique.

So for tborders_groups they should be:

ALTER TABLE `tborders_groups`
  ADD PRIMARY KEY (`id`),
  ADD KEY `merchant_id` (`merchant_id`);