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?
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.
