Optimizing Existence Checks: Use SELECT 1 LIMIT 1 Instead of COUNT(*)
The article explains why using SELECT 1 with LIMIT 1 to test for record existence is more efficient than counting rows with SELECT count(*), and shows how to rewrite both SQL and Java code for better performance.
When checking whether records exist based on certain conditions, many developers write SELECT count(*) FROM table WHERE ... and then test if the returned count is greater than zero.
This approach works but can be inefficient because the database must count all matching rows.
A more efficient pattern is to query for a single row using SELECT 1 FROM table WHERE ... LIMIT 1 and then check whether the result is non‑null.
In Java, instead of calling a DAO method that returns a count, you can call a method like Integer exist = xxDao.existXxxxByXxx(params); and test exist != null.
Using LIMIT 1 stops the database as soon as it finds the first matching row, reducing I/O and CPU usage, especially when the underlying query would match many rows; it can also lessen the need for composite indexes.
In summary, replacing COUNT(*) with a limited existence check can noticeably improve performance in many scenarios.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
Top Architect
Top Architect focuses on sharing practical architecture knowledge, covering enterprise, system, website, large‑scale distributed, and high‑availability architectures, plus architecture adjustments using internet technologies. We welcome idea‑driven, sharing‑oriented architects to exchange and learn together.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.