Does adding 'LIMIT 1' to MySQL queries make them faster when you know there will only be 1 result?

mysql optimization limit
Logan Serman picture Logan Serman · Jan 18, 2009 · Viewed 30.2k times · Source

When I add LIMIT 1 to a MySQL query, does it stop the search after it finds 1 result (thus making it faster) or does it still fetch all of the results and truncate at the end?

Answer

Eran Galperin picture Eran Galperin · Jan 18, 2009

Depending on the query, adding a limit clause can have a huge effect on performance. If you want only one row (or know for a fact that only one row can satisfy the query), and are not sure about how the internal optimizer will execute it (for example, WHERE clause not hitting an index and so forth), then you should definitely add a LIMIT clause.

As for optimized queries (using indexes on small tables) it probably won't matter much in performance, but again - if you are only interested in one row than add a LIMIT clause regardless.

Related questions

How do I add indices to MySQL tables?

I've got a very large MySQL table with about 150,000 rows of data. Currently, when I try and run SELECT * FROM table WHERE id = '1'; the code runs fine as the ID field is the primary index. However, for a …

Read More
delete all from table

what's faster? DELETE FROM table_name; or DELETE FROM table_name where 1=1; why? does truncate table work in access?

Read More
Storing JSON in database vs. having a new column for each key

I am implementing the following model for storing user related data in my table - I have 2 columns - uid (primary key) and a meta column which stores other data about the user in JSON format. uid | meta -------------------------------------------------- 1 | {name:[…

Read More