user3233336 user3233336 - 6 months ago 10
SQL Question

Mysql long execution query

I have table with 38k rows and I use this query to compare item id from items table with item id from posted_domains table.

select * from `items` where `items`.`source_id` = 2 and `items`.`source_id` is not null and not exists (select * from `posted_domains` where `posted_domains`.`item_id` = `items`.`id` and `domain_id` = 1) order by `item_created_at` asc limit 1


This query took 8s. I don't know if is a problem with my query or my mysql is bad configured. This query is generated by Laravel relations like

$items->doesntHave('posted', 'and', function ($q) use ($domain) {
$q->where('domain_id', $domain->id);
});

Answer

CORRELATED subqueries can be rather slow (as they are often executed repeatedly, once for each row in the outer query), this might be faster.

select * 
from `items` 
where `items`.`source_id` = 2 
   and `items`.`source_id` is not null 
   and item_id not in (
      select DISTINCT item_id 
      from `posted_domains` 
      where `domain_id` = 1) 
order by `item_created_at` asc 
limit 1

I say might because subqueries in where are also rather slow in MySQL.

This LEFT JOIN will probably be the fastest.

select * 
from `items` 
LEFT JOIN (
      select DISTINCT item_id 
      from `posted_domains` 
      where `domain_id` = 1) AS subQ
ON items.item_id = subQ.item_id
where `items`.`source_id` = 2 
   and `items`.`source_id` is not null 
   and subQ.item_id is null
order by `item_created_at` asc 
limit 1;

Since it is a no matches scenario, it technically doesn't even need to be a subquery; and might be faster as a direct left join, but that will depend on indexes, and possibly actual data values.

Comments