CODEWITHSUNDEEP
mysqlsqlpostgresql
M. Montoya
M. Montoya

Reputation: 5

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

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?

Upvotes: 0

Views: 210

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

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.

Upvotes: 3

Related Questions