In the post, we focus on a small, but common SQL improvement.

Don’t count on it

How many times have you seen fellow developers get a count on something when really they just want to know if it’s > 0? The code will commonly look something like this:

$russianBrides = DB::table('mail_order_brides')->where('ethnicity', 'Russian')->get();

if (!count($russianBrides)) {
    redirect('general-landing-page');
}

view('russian-brides-landing-page');

In other words, they’re grabbing every matching row when all they need out of it is whether the count is more than zero or not. A better, but still underwhelming, improvement on this is also common:

$count = DB::table('mail_order_brides')->where('ethnicity', 'Russian')->count();
if (!$count) {

The underlying SQL this produces is select count(*) as aggregate from `mail_order_brides` where `ethnicity` = `Russian`.

A better way exists

The problem here is that MySQL needs to scan the entire mail_order_brides table, checking every row’s ethnicity column to return the final result. In the first example, it also needs to pull all the data from each row.

But, here, we don’t actually care what the count is. We only want to know if any match exists or not. There’s a better way:

$exists = DB::table('mail_order_brides')->where('ethnicity', 'Russian')->exists();

This products the SQL statement select exists(select count(*) from `mail_order_brides` where `ethnicity` = `Russian`) as exists.

This is the SQL output as produced by Laravel. If I was crafting this SQL by hand, I prefer to do it like this: select exists(select NULL from `mail_order_brides` where `ethnicity` = `Russian`) as exists. This makes it more clear that the count is unimportant to the query and that we’re actually not selecting anything from the subquery at all, which is pretty damn cool I’d say.

An artist’s rendition of my formative years.

Written like this, the SQL engine will stop scanning through the table as soon as it finds one existing match. Worst-case scenario, a match is never found and it needs to scan every row just like it did before. Best-case scenario, a match is found on the very first row, and nearly the entire scan is skipped.

Even if the columns that you’re searching on are indexed, there is still some amount of improvement as exists can save it from having to walk the entire index.

Especially good for subqueries

One place where this efficiency really shines is in subqueries. Subqueries tend to be less efficient (or rather, more sensitive to inefficiency) than top-level queries because they are run once per outer loop. So:

select * from `customers` where exists (select NULL from `orders` where `customers`.`id` = `orders`.`user_id`);

is a good deal more efficient than

select * from `customers` where ((select count(*) from `orders` where `customers`.`id` = `orders`.`user_id`) > 0);

Laravel has it built in

Laravel uses the more efficient query when we use the has() query method:

Customer::has('order')->get();

The above Laravel query retrieves all customers who have previously ordered a mail order bride without sacrificing efficiency.

Another benefit exists

A less tangible benefit is the readability of the code. Let’s say we want to get rid of this if() statement later on. Do we still need $russianBrides for something else? Did we really pull all of the models just to check if the count is more than zero? Inlining helps, but it’s still less readable.

if (MailOrderBride::russian()->count()) {

If count“? What? You might write this as if (MailOrderBride::russian()->count() > 0) to improve readability, but someone will probably complain and tell you to simplify it anyway. Versus:

if (MailOrderBride::russian()->exists()) {

No ambiguity there. It reads damn near English. Even a high-quality mail order bride with tenuous grasp on English as a second language could probably make sense of this.

Conclusion

If there’s any place where efficiency actually matters, it’s in the database. And if there’s any place where code readability matters, it’s everywhere. Use exists anytime that’s all that you need.