<p>Will limiting a query to one result record, improve performance in a large(ish) MySQL table if the table only has one matching result?</p>

<p>for example</p>

<pre><code> select * from people where name = "Re0sless" limit 1
</code></pre>

<p>if there is only one record with that name? and what about if <code>name</code> was the primary key/ set to unique? and is it worth updating the query or will the gain be minimal?</p>

[{'answer_id': 34499, 'author': 'Owen Fraser-Green', 'author_id': 3382, 'author_profile': 'https://Stackoverflow.com/users/3382', 'pm_score': 0, 'selected': False, 'text': "<p>I believe the LIMIT is something done after the data set is found and the result set is being built up so I wouldn't expect it to make any difference at all. Making name the primary key will have a significant positive effect though as it will result in an index being made for the column.</p>\n"}, {'answer_id': 34500, 'author': 'John Douthat', 'author_id': 2774, 'author_profile': 'https://Stackoverflow.com/users/2774', 'pm_score': 6, 'selected': True, 'text': "<p>If the column has </p>\n\n<p><strong>a unique index: no,</strong> it's no faster</p>\n\n<p><strong>a non-unique index: maybe,</strong> because it will prevent sending any additional rows beyond the first matched, if any exist</p>\n\n<p><strong>no index: sometimes</strong></p>\n\n<ul>\n<li>if 1 or more rows match the query, <strong>yes</strong>, because the full table scan will be halted after the first row is matched.</li>\n<li>if no rows match the query, <strong>no</strong>, because it will need to complete a full table scan</li>\n</ul>\n"}, {'answer_id': 34501, 'author': 'TheSmurf', 'author_id': 1975282, 'author_profile': 'https://Stackoverflow.com/users/1975282', 'pm_score': 0, 'selected': False, 'text': '<p>If "name" is unique in the table, then there may still be a (very very minimal) gain in performance by putting the limit constraint on your query. If name is the primary key, there will likely be none.</p>\n'}, {'answer_id': 34508, 'author': 'jcoby', 'author_id': 2884, 'author_profile': 'https://Stackoverflow.com/users/2884', 'pm_score': 2, 'selected': False, 'text': '<p>To answer your questions in order:\n 1) yes, if there is no index on name.  The query will end as soon as it finds the first record.  take off the limit and it has to do a full table scan every time.\n 2) no.  primary/unique keys are guaranteed to be unique.  The query should stop running as soon as it finds the row.</p>\n'}, {'answer_id': 695083, 'author': 'jle', 'author_id': 41713, 'author_profile': 'https://Stackoverflow.com/users/41713', 'pm_score': 0, 'selected': False, 'text': '<p>Yes, you will notice a performance difference when dealing with the data.  One record takes up less space than multiple records.  Unless you are dealing with many rows, this would not be much of a difference, but once you run the query, the data has to be displayed back to you, which is costly, or dealt with programmatically.  Either way, one record is easier than multiple.</p>\n'}, {'answer_id': 695102, 'author': 'Andomar', 'author_id': 50552, 'author_profile': 'https://Stackoverflow.com/users/50552', 'pm_score': 2, 'selected': False, 'text': '<p>If you have a slightly more complicated query, with one or more joins, the LIMIT clause gives the optimizer extra information.  If it expects to match two tables and return all rows, a <a href="http://en.wikipedia.org/wiki/Hash_join" rel="nofollow noreferrer" title="hash join">hash join</a> is typically optimal.  A hash join is a type of join optimized for large amounts of matching.</p>\n\n<p>Now if the optimizer knows you\'ve passed LIMIT 1, it knows that it won\'t be processing large amounts of data.  It can revert to a <a href="http://en.wikipedia.org/wiki/Nested_loop_join" rel="nofollow noreferrer" title="loop join">loop join</a>.</p>\n\n<p>Based on the database (and even database version) this can have a huge impact on performance.</p>\n'}]
['https://Stackoverflow.com/questions/34488', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2098/']