2

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.

  • 1
    Interesting (first) question (+1) - what's the framework that constructs this SQL? I'd hate to see what would happen if you had 10,000 values in your IN clause. p.s. welcome to the forum! – Vérace Jul 11 at 5:15
2

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.

| improve this answer | |
  • Thanks its actually returning one result using primary unique key so the answer would be for me it is smart enough to stop after it finds the first result without needing to specifically define a limit it sounds like. – Danny Jul 11 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.