Fast check of existence of an entry in a SQL database

Question

Nitpicker Question:

I like to have a function returning a boolean to check if a table has an entry or not. And i need to call this a lot, so some optimizing is needed.

Iues mysql for now, but should be fairly basic...

So should i use

select id from table where a=b limit 1;

or

select count(*) as cnt from table where a=b;

or something completly different?

I think SELECT with limit should stop after the first find, count(*) needs to check all entries. So SELECT could be faster.

Simnplest thing would be doing a few loop and test it, but my tests were not helpful. (My test system seemd to be used otherwise too, which diluted mny results)

Answer 1

The limit 1 will tell the MySQL to stop searching after it finds one row. If there can be multiple rows that match the criteria, this is faster than count(*).

There are more ways to optimize this, but the exact nature would depend on the amount of rows and the spread of a and b. I'd go with the "where a=b" approach until you actually encounter performance issues. Databases are often so fast that most queries are no performance issue at all.

Answer 2

this "need" is often indicative of a situation where you are trying to INSERT or UPDATE. the two most common situations are bulk loading/updating of rows, or hit counting.

checking for existence of a row first can be avoided using the INSERT ... ON DUPLICATE KEY UPDATE statement. for a hit counter, just a single statement is needed. for bulk loading, load the data in to a temporary table, then use INSERT ... ON DUPLICATE KEY UPDATE using the temp table as the source.

but if you can't use this, then the fastest way will be select id from table where a=b limit 1; along with force index to make sure mysql looks ONLY at the index.