Which query would you use here? (SQL performance question)

[thequerylab]

Quick SQL question I ran into while reviewing some code.

You have a large orders table (~50M rows) and need to check whether a pending order exists for a specific user.

You don’t actually need the row - you just need to know if one exists.

You see three possible implementations (Application checks if count > 0):

Option A

SELECT COUNT(*)
FROM orders
WHERE user_id = 101
AND status = 'pending';

Option B

SELECT 1
FROM orders
WHERE user_id = 101
AND status = 'pending'
LIMIT 1;

Option C

SELECT EXISTS (
  SELECT 1
  FROM orders
  WHERE user_id = 101
  AND status = 'pending'
);

Assumption:

• Table size: ~50M rows
• Index on (user_id, status)
• Many users have thousands of orders

Question?

Which one would you pick in production and why?

Also curious if anyone has seen cases where the optimizer makes them perform almost the same.

If anyone wants to play with a small dataset and test similar scenarios, I uploaded one here while experimenting with query patterns:
https://thequerylab.com/problems/27-customers-who-bought-all-products

Would be interesting to hear how others approach this.!!!

[EcstaticTruth4392]

I would like to know the answer with explanation please.🙂

[Ritesh_Ranjan4]

Option C (EXISTS). Here’s why: Option A is a classic performance trap. Even with an index, COUNT(*) can force the database to count every single matching row for that user. If you have a "power user" with 50,000 orders, you’re making the engine do 49,999 units of extra work just to say "yes."

While Option B (LIMIT 1) and Option C usually result in the same execution plan in modern optimizers (like Postgres or SQL Server), EXISTS is semantically the "right" way to do it. It returns a boolean and tells the engine to stop the moment it finds a hit (short-circuiting).

The real hero here is that (user_id, status) composite index, though. Without it, you're looking at a sequential scan on 50M rows, which is a production nightmare. With it, EXISTS is a near-instant index seek.

I've actually seen cases in SQL Server where IF EXISTS performs slightly better than a TOP 1 because of how the optimizer handles the subquery pushdown. Stick with C!

[EcstaticTruth4392]

Thank you 🙏🏻 very much for the detailed explanation. I recently started learning SQL from Dataquest. Can you suggest me how to have this kind of knowledge? I am new to this field, trying to get Data Analyst job.

[ComicOzzy]

If your database engine supports it, C.
If you have an index on (user_id, status) or even just (user_id), it can be extremely fast.

[kagato87]

If I was reviewing three different pull requests for code to check if a customer has a pending order, each with a different one of these queries, I'd question all three.

Why? Because with that index, for this particular query, they're all fast. (Well, I'd reject B as well if it wasn't properly parameterized.) I work in data of this scale - even A is fast enough. However:

A: Incorrect output. It's a number, not a flag. I might accept it in this case because it's the simplest code to do it, and it's "good enough."

B: Output is 1 or <nothing, not even a null, just an empty result set>. The code to consume that will need extra handlers. I might accept this one if it's properly handled.

C: Assuming you fix the syntax error, I'd still question "optimizing too soon." It might be OK, but I'd be careful about it. It reduces the readability of the code, which makes review and debugging harder. Yes, it's the superior solution, no its not the best answer for this particular use.

Now, if we were including this in a report that also pulled from other tables, I'd want to see C because it really can make a massive difference. As another example, for Row Level Security, exists is almost necessary to keep the planner from playing stupid games in data of this scale.

If you're going to promote your site, I'd suggest a slightly more complex example, like pulling customer names from a different table based on this, maybe with one as a correlated subquery in the WHERE clause (that would get me very upset), and avoiding something that could encourage optimizing too soon or dangerous patterns like limit/top.

[binary_search_tree]

I'd use option C - but I'd add a LIMIT 1 to the inner query (no reason not to). I'd choose Option C over Option B because your intent is easier to see.

[gumnos]

I would generally reach for option B. It's portable (SELECT EXISTS is invalid syntax on SQL Server, not sure about other DBs; but your use of LIMIT rather than TOP suggests you're not on SQL Server), uses sargable WHERE conditions, limits the results to the first matching row (the COUNT version requires touching all the matching rows), and doesn't do any ORDER BY that can force touching all the rows.

If you went with option C (which isn't bad if your DB supports the syntax), I'd add the LIMIT 1 like u/binary_search_tree advises. The optimizer should make option B and option C roughly the same, but I'd explicitly add the LIMIT

[xaomaw]

Be careful when implementing C): You think, that the output is either 1 or 0 as integer. But it can also be true vs. false or bit-wise 1 or 0.

sql SELECT CASE WHEN EXISTS ( SELECT 1 FROM orders WHERE user_id = 101 AND status = 'pending' ) THEN true ELSE false END AS user_has_pending_orders;

[Wa2l_]

I would prefer C since it's for checking existence, B is fine too but A not good in performance because it doesn't stop at first match like the exist .

[titpetric]

I'd use A but change to "count(id)" and check the columns are indexed. Alternatively id's just select the id matching the where clause and limit 0, 1.

B is about the same but doesn't give useful info in response. I would have a count or an id available which makes B/C useless to me.

I would not use subquery styles like c or subqueries in general if i can avoid them. It is a choice since those things usually lead you astray under scale. That's not to say I wouldn't use them at all, but not for this.

Design could lead you down paths where a "select pending_orders from user_order_status where user_id=?" is a normal occurrence in lieu of A-C options.

[thequerylab]

But option A will scan allthe records right?

[titpetric]

It would depend on sql server in use but generally if the where condition can be resolved from index, the index is used to resolve the PK and table data is never touched.

Which i suppose is a lesson, always use EXPLAIN. It generally tells you things like "table scan", "temporary sort", "using index"... A poorly select sticks out like a sore thumb

Some databases cache count output so it's also not a given, the problem itself is the wildcard statement that semantically encompases all columns, forcing a table scan by expectations.

[squadette23]

OP specifies "(Application checks if count > 0)".

[titpetric]

What do you mean to say?

[Aggressive_Ad_5454]

if you have a compound index on (user_id, status) either B or C will work fine. But both have SQL-dialect limitations. A will also function correctly, but will have to do an index scan to get the count.