How to Efficiently Check if a Record Exists

When checking if a record exists in the database, many developers use ->count() > 0.

While this approach works, it’s not the most efficient way.

In this Laravel tutorial, I’ll explain why using ->count() > 0 isn’t the best choice for checking if a record exists.

By the end of this post, you’ll learn about a method that is much more efficient than ->count() when used with large datasets.

Let’s dive in! 🤿

🎥 Prefer the video format of this tutorial? Check this out:

The Problem 🤔

For this tutorial, I’ve seeded 1 million rows into a products table with the following structure:

CREATE TABLE `products` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `price` decimal(8,2) NOT NULL,
  `is_listed` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

You’ll notice no indexes are added here — this is to keep things as straightforward as possible.

Let’s assume we want to check if there’s a listed product with a price greater than 50:

use App\Models\Product;

if (
    Product::query()
        ->where([['is_listed', true], ['price', '>', 50]])
        ->count() > 0
) {
    // domain logic here
}

Let’s now take a look at the query that results from using ->count() and also how fast it performs:

use App\Models\Product;
use Illuminate\Support\Facades\DB;

DB::enableQueryLog();

if (
    Product::query()
        ->where([['is_listed', true], ['price', '>', 50]])
        ->count() > 0
) {
    // domain logic here
}

dd(DB::getQueryLog());

// Output 👇

array:1 [
  0 => array:3 [
    "query" => "select count(*) as aggregate from `products` where (`is_listed` = ? and `price` > ?)"
    "bindings" => array:2 [
      0 => true
      1 => 50
    ]
    "time" => 263.87 // ⏲ milliseconds, on average
  ]
]

The query scans through the table to count all matching rows where is_listed and price columns meet the criteria.

Even if it finds a match early in the scan, it continues through the entire dataset to get an exact count which makes it more costly, especially on large datasets similar to the one I’m using in this example.

On a table with 1 million records, without indexes, this query took 260 milliseconds on average on my machine.

The query time will increase exponentially with the number of records. 📈

The Solution 💡

Let’s now look at a better alternative.

Instead of using ->count() > 0, we can use ->exists().

use App\Models\Product;
use Illuminate\Support\Facades\DB;

DB::enableQueryLog();

if (
    Product::query()
        ->where([['is_listed', true], ['price', '>', 50]])
        ->exists() // 👈 here
) {
    // domain logic here
}

dd(DB::getQueryLog());

// Output 👇

array:1 [
  0 => array:3 [
    "query" => "select exists(select * from `products` where (`is_listed` = ? and `price` > ?)) as `exists`"
    "bindings" => array:2 [
      0 => true
      1 => 50
    ]
    "time" => 1.75 // ⏲ milliseconds, on average
  ]
]

This query checks for the existence of at least one row that meets the conditions.

It stops as soon as it finds the first matching record.

This is great because it minimizes the workload on the database, making it significantly faster for large datasets.

This query took only 1.75 milliseconds on average compared to 260 milliseconds from the previous query. 📉✅
This means a performance improvement of approximately 99% on my machine. ⚡

The percentage difference depends on the size of the dataset, which is known as the scaling effect.
When the dataset is small, the performance difference is also small. However, as the dataset grows larger, the performance difference becomes more noticeable.

Conclusion

In this tutorial, we explored why it’s much more efficient to use ->exists() instead of ->count() > 0 when we want to check if a record exists in the database regardless of how large the dataset is.

If you know any other efficient ways to check if a record exists, leave me a comment below. I do read all the comments.

Comments