Let's take a look at this SQL query to a MySQL database:

UPDATE `messages` SET `read`=1 WHERE `id`=1234;

Let's assume that messages is a really long table and id is auto increasing and primary key.

My question is:

Does MySQL automatically stop searching, when it finds one message with id=1234? Or in other words: is there any point using LIMIT in query like this one?

1 Answers

2
O. Jones On Best Solutions

Does MySQL automatically stop searching, when it finds one message with id=1234?

Because id is a primary key, it has an index. MySQL uses that index to find the row to update. It isn't searching in the sense of looking at many rows. It simply random-accesses the table at the right row. Because it's a primary key, MySQL knows it must update only one row. As we say in Boston, Mass, USA, it's wicked fast.

Is there any point using LIMIT in query like this one?

No.

Don't try to outsmart SQL. It's a declarative language, not procedural. Use your queries, like your UPDATE, to describe what you want. MySQL has, literally, thousands of programmer-years in the code it uses to figure out the fastest procedure to use.