Difference between SELECT COUNT(*) and SELECT true finding specific row in a billion rows table

Question

I have a table with maybe 3-5 billions rows. I need to check if a specific value is in that table, which is the fastest way?

SELECT COUNT(*) AS total FROM schema.table WHERE row = 'pattern';   -- Must return 1 or 0

vs

SELECT true AS is_in_table FROM schema.table WHERE row = 'pattern'   -- Must return true or no one row at all

Which is the best way to get the 'fastest' result using the appropriate column indexing?

Possible duplicate of [Sql: How to properly check if a record exists](https://stackoverflow.com/questions/4253960/sql-how-to-properly-check-if-a-record-exists) — clinomaniac, Dec 19 '17 at 00:42

score 3 · Accepted Answer · answered Dec 19 '17 at 00:43

The fastest way is to put an index on schema.table(row).

Then you can execute:

SELECT true AS is_in_table
FROM schema.table
WHERE row = 'pattern' 
LIMIT 1;

For this formulation, the LIMIT is important, unless you have explicitly declared row as unique (and even then I'm not 100% sure that MySQL will keep this in mind for the query).

The COUNT(*) will need to look for every value that might match, before returning a row. If the column is declared unique, then the performance should be similar between the two versions.

Difference between SELECT COUNT(*) and SELECT true finding specific row in a billion rows table

1 Answers1