Use EXISTS instead of COUNT > 0 when checking if records exist

#sql #laravel #db #optimisation

TL;DR Use exists when querying if SQL records exists instead of using count. exists is much more efficient and breaks out of the loop when first record is found.

Using `count`

Until recently, when I had to check if a DB record that satisfies some conditions exists, I’ve used a count and then check if returned result is greater than 0.

Plain SQL query:

SELECT COUNT(*) 
FROM `post_likes`
WHERE `member_id` = 1
AND `post_id` = 1

Extra tip: MySQL has `COUNT() optimised and it is faster and more efficient than using COUNT(id)` for example.*

Laravel Eloquent query (using postLikes relationship):

// Did member like a post
$member->postLikes()->count() > 0;

Extra tip: notice the brackets after postLikes() relationship name. This means we are using the relationship to generate query on the related table and setting up foreign key for us. If we used $member->postLikes->count() , without the brackets, we would fetch all related records and then do a count afterwards. This would result in a much costlier DB query, and more memory used as all those records needs to saved to memory.

This would “force” a DB to count through all of the records that satisfies these conditions. And if your table is large enough it could take some time. Granted, probably in milliseconds but still it would do unnecessary work as it does not know that you are just interested in “existence” of the record and not the exact count.

Of course, if you indexed the table “properly” and use a composite index on member_id and post_id columns result would be pretty fast in this scenario but in some others it still may be optimised.

Using `exists` subquery

Better way would be to use an exists subquery. This is available in MySQL from version 5.7 so there is no reason not to use it.

You can check MySQL docs on EXISTS here. There is also NOT EXISTS subquery if you want to query for inversion.

EXISTS works by encapsulating a query in SELECT subquery:

SELECT EXISTS(
    SELECT *
    FROM `post_likes`
    WHERE `member_id` = 1
    AND `post_id` = 1
)

Doesn’t matter if your SELECT fetches all columns (`) or plain 1` , SELECT will be discarded in EXISTS query.*

This query will return true if subquery has at least one record or false if there are no records that satisfies your conditions. MySQL will break out of the “loop” when it finds the first record and this is what makes it more performant than the COUNT.

In Laravel you can use exists method on the query builder:

// Did member like a post
$member->postLikes()->exists();

Eloquent will encapsulate the query in the EXISTS subquery.

Eloquent also provides whereExists , whereNotExists , doesntExist, withExists and several more to allow you to build a query that you need.

Proper example of using `exists`

I’ve been using this to check existence of all kinds of records and relationships. Like permissions, likes and even as a nested subquery.

For example, when fetching a list of posts to display on the page, I want to know if member did like a post in order to show a proper UI icon. This could lead to a N+1 situation where for each post we’d have to do a separate SQL query to check if record exits.

Or we can use EXISTS subquery:

SELECT `id`, `title`, `content`, exists(
    SELECT *
    FROM `post_likes`
    WHERE `posts`.`id` = `post_likes`.`post_id` 
    AND `member_id` = 1
) AND `is_liked`
FROM `posts`

This will be done in a single optimised SQL query and provide information if member liked a post in a generated is_liked column. To be precise, MySQL will do N+1 subqueries to check for existence but this will be optimised and done internally.

In Laravel you’d use withExists to do the same thing:

$posts = Post::query()
    ->select(['id', 'title', 'content'])
    ->withExists(['postLikes as is_liked' => function ($query) use ($member) {
        $query->where('member_id', $member->id);
    });

is_liked will be added as an attribute on each $post model.

This optimisation may not seem as important and it may look like a “micro” improvement. But if your tables have millions of records then you already know that every millisecond counts.