[Resolved] limit is sql

[bike5]

I am trying to figure out if this will execute faster.

Say my tables has 5000 rows and I want to update one row by a unique value (it will only find and update one no matter what), if I put a " LIMIT 1" in the sql statement, does that mean after it finds and updates the 1 record it will stop searhing through the rest of the table and thus save time? Or does is search the rest of the table anyway till it reaches the end?

[drew010]

Yes, with LIMIT once mysql has found the required number of rows it aborts the query. It was created for efficiency in the case where you only need a few rows, and safety so you can LIMIT 1 in the case of a delete query.

[AstroTeg]

Originally posted by bike5
I am trying to figure out if this will execute faster.

Say my tables has 5000 rows and I want to update one row by a unique value (it will only find and update one no matter what), if I put a " LIMIT 1" in the sql statement, does that mean after it finds and updates the 1 record it will stop searhing through the rest of the table and thus save time? Or does is search the rest of the table anyway till it reaches the end?

Think about this for a moment (assuming your unique value is indexed):

Using some database magic, it'll be very easy for the database to find the unique ID out of the 5000 or more IDs and update it. It doesn't take it long. I'd say using LIMIT 1 is redunant and you won't notice any difference (if and only if you have an index on the unique values).

Doing a limit on an update query is down right dangerous, in my opinion. If you have multiple IDs, how do you know which one got updated if you limited it to just one?

[bike5]

There couldn't be multiple id's because its a unique feild and it checks for that before ever putting a new record in.

Thanks drew010, thats what I thought but just wassn't "sure."