MySQL – Does Search Stop After Finding One Record with Primary Key?

MySQL

Having trouble finding this answer. If I search for a record by a primary key id does mysql automatically stop searching after it finds 1 record if there was no "limit" in the query?

Say a table has 1 billion records and I search for id = 1 and id is the primary auto incrementing key in the table. SELECT * FROM logs WHERE id IN(1) Will that query stop after it finds one or search the whole billion records even after finding a match?

I am asking because a model in a framework I am using that auto-builds db queries builds them like this including using "IN(id)" instead of id = # which is why the example shows it that way.

I want to use the database model a cleanly as possible but if it has a performance issue because of how it builds the query when dealing with 1 record I have to add more code to manually specify a limit. The model also builds updates the same way I assume mysql will handle the select and update on the primary key the same way (stopping after 1 found/updated or not).

Thanks.

Best Answer

It depends.

First, let me put aside WHERE id=1 versus WHERE id IN (1). The Optimizer turns the IN version into the = version. Hence, there is no performance difference when there is only one item in the in-list. For multiple items, the answer gets more complicated (and not covered here).

Does id have an index? Let me address 3 cases:

  • No index. Then it may have to read each row of the table, checking each for id=1. Sloooow.
  • A non-unique index. And not LIMIT. Now MySQL does not know whether there are no rows, one row, or thousands. And WHERE id=1 simply says "get all of them". So the underlying code will find the first one in the index, then read until id > 1. So if there are N matching rows, it will "read" N+1 rows (but deliver only the N that you asked for).
  • A UNIQUE index. Note: The PRIMARY KEY is UNIQUE. [This probably your only question.] First, it will have to dig into the index to see if there is a row with id=1. But after that, it is smart enough to know to stop after 1 row.

In my experience, I think the difference between stopping at 1 and doing 2 is insignificant. There are so many other things going on in a query, that I would be hard-pressed to measure the timing difference.

But I am not through lecturing. What about ORDER BY x LIMIT 1? Now the efficiency depends on other things. Most important is whether both the WHERE and the ORDER BY can use the same index. If not, the data is gathered, sorted, then finally 1 row is pealed off. Corollary: For a million-row table, this may mean that adding the LIMIT does not help performance.

To counteract that example, you are unlikely to say WHERE id=1 ORDER BY x -- why bother sorting a single row.

Related Question