mySQL: Most efficient method to update records in large DB?

Say I have a table with 1000s of records and I only want to update one record. Does it make the query faster if I specify more 'WHERE' clauses to narrow down the search to fewer possible matching records, or is it faster to just state one WHER开发者_如何转开发E clause (eg. recordID)?

EXAMPLE:

UPDATE table
SET record_name = 'new name'
WHERE record_ID = 'x'
LIMIT 1

or

UPDATE table
SET record_name = 'new name'
WHERE record_ID = 'foo'
AND record_city = 'bah'
LIMIT 1

---

Assuming record_ID is a primary key, this is definitely the fastest way to update a single record.

---

if record_ID is primary key, the first solution is the fastest. I think that it is not necesary to put "LIMIT 1" because it is implicit in the primary key

---

It's worth noting that the two commands actually are not functionally equivalent as well. You should always run the query, which does what you want to do.

The optimizer in the database will figure it out. In this case, even if you specify both, it will have to access the location of the row to delete it, reading an extra WHERE against a column is a very minor penalty compared to navigating to the row in the first place.

Note also that using LIMIT without an ORDER BY will break serialization for replication and restoration of binary logs for point in time recovery in STATEMENT mode.

It's in this case better to specify in the WHERE all that is needed and not need the LIMIT.