MySQL: What is faster? Count or Limit 1

When trying to see if a record exist there are a few ways to find it. The most logical might be to simply issue a SELECT field FROM database WHERE field = value LIMIT 1, however another option exists, which is to use count instead of field and omit the LIMIT 1.

When it comes to performance, there is no clear winner based on a hypothetical example however:

The reason is that fundamentally the two function differently. FindByOne (a Doctrine ORM convention causing a LIMIT 1 to be applied to the query) will stop traveling as soon as it finds a match, whereas Count will travel the entire table. Now Count has some internal optimizations within SQL that makes it more performant than even FindByOne when the result (hit) is near the end of the table.

This carries over to real world tests — when I take a database of 50,000 records, if the “hit” is near the front, then FindByOne finishes nearly 3x faster than Count, however when the “hit” is near the end, then FindByOne finishes 50% slower than Count. And if the “hit” was near the middle, then Count again wins by a margin. But all of this is still measured in the single digit milliseconds (ms)

Considering that in most cases you probably will not know where the match will be found, using Count will probably be faster more often than not if you’re simply trying to confirm if an entry exists.