I want to optimize my sql query. Becasuse long response time-CodePudding

First İ use wherehas but then I decided use this way. This way result better than wherehas but It isn't satisfy me. Query response time is a 873ms. So I have 400k data in the table.

select count(*) as aggregate 
from `orders` 
where (`pickup_address_id` in (
    select `id` 
    from `addresses` 
    where `region_id` = 12) 
 or `delivery_address_id` in (
    select `id` 
    from `addresses` 
    where `region_id` = 12)
) and `orders`.`status` = 2

CodePudding user response：

Try this:

select count(distinct o.`id`) as aggregate 
from `orders` o
inner join `addresses` a ON a.`id` IN (o.`pickup_address_id`, o.`delivery_address_id`)
    AND a.`region_id` = 12
where o.`status` = 2

Alternatively:

SELECT count(distinct id) as aggregate
FROM (
    select o.`id`
    from `orders` o
    inner join `addresses` a ON a.`id` = o.`pickup_address_id`
        AND a.`region_id` = 12
    where o.`status` = 2

    UNION

    select o.`id` 
    from `orders` o
    inner join `addresses` a ON a.`id` = o.`delivery_address_id`
        AND a.`region_id` = 12
    where o.`status` = 2
) t

But I don't know you'll improve much to look through 400K rows in less than a second.

CodePudding user response：

First, you can try to eliminate multiple (twice, to be more precious) same subquery evaluation using a Common Table Expression

WITH CTE(id) AS (
    SELECT id 
    FROM addresses
    WHERE region_id = 12
)

This CTE would be evaluated once.

Second, get row count from orders table joined with cte on existence of pickup_address_id and delivery_address_id in cte.

WITH CTE(id) AS (
    SELECT id 
    FROM addresses
    WHERE region_id = 12
)
SELECT COUNT (*)
FROM orders
CROSS JOIN CTE ON CTE.id = orders.delivery_address_id OR CTE.id = orders.pickup_address_id

Finally, add filter by status = 2 and query would be like

WITH CTE(id) AS (
    SELECT id 
    FROM addresses
    WHERE region_id = 12
)
SELECT COUNT (*)
FROM orders
CROSS JOIN CTE ON CTE.id = orders.delivery_address_id OR CTE.id = orders.pickup_address_id
WHERE orders.status = 2

Also you should have following indexes:

addresses table:

INDEX (region_id)

orders table:

INDEX (pickup_address_id),
INDEX (delivery_address_id),
INDEX (status)

Give it a try.

With empty tables I've got this

Schema (MySQL v8.0)

create table addresses (
  id int primary key,
  region_id int not null,
  index(region_id)
  );
  
create table orders (
  id int primary key,
  pickup_address_id int,
  delivery_address_id int,
  status int not null,
  index (pickup_address_id),
  index (delivery_address_id),
  index(status),
  foreign key (pickup_address_id) references addresses(id),
  foreign key (delivery_address_id) references addresses(id)
);

Query #1

explain with cte(id) as (
  select id from addresses where region_id = 12)
select count(*) from orders 
cross join cte on cte.id = orders.delivery_address_id or cte.id = orders.pickup_address_id
where status = 2;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	orders		ref	pickup_address_id,delivery_address_id,status	status	4	const	1	100
1	SIMPLE	addresses		ref	PRIMARY,region_id	region_id	4	const	1	100	Using where; Using index