StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<blockquote> <p>The userid column is the primary key of the table. Is it good practice to use LIMIT 1 on the end of that mySQL statement? Or are there any speed benefits?</p> </blockquote> <p>It is not good practice to use <code>LIMIT 1</code> at the end of the example - it's completely unnecessary, because the userid column is a primary key. A primary key means there is only one row/record in the table with that value, only one row/record will ever be returned.</p> <p>But the ultimate indicator is the explain plan:</p> <pre><code>explain SELECT t.name FROM USERS t WHERE t.userid = 4 </code></pre> <p>...returns:</p> <pre><code>id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra ----------------------------------------------------------------------------------------------------- 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 4 | const | 1 | </code></pre> <p>...and:</p> <pre><code>explain SELECT t.name FROM USERS t WHERE t.userid = 4 LIMIT 1 </code></pre> <p>...returns:</p> <pre><code>id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra ----------------------------------------------------------------------------------------------------- 1 | SIMPLE | users | const | PRIMARY | PRIMARY | 4 | const | 1 | </code></pre> <h2>Conclusion</h2> <p>No difference, no need. It appears to be optimized out in this case (only searching against the primary key).</p> <h2>The LIMIT clause</h2> <p>Using <code>LIMIT</code> without an <code>ORDER BY</code> will return an arbitrary row/record if more than one is returned. For example, using the "John Smith" scenario where 2+ people can have the name "John Smith":</p> <pre><code>SELECT t.userid FROM USERS t WHERE t.first_name = 'John' AND t.last_name = 'Smith' LIMIT 1 </code></pre> <p>...risks returning any of the possible <code>userid</code> values where the first name is "John" and the last name is "Smith". It can't be guaranteed to always return the same value, and the likelihood of getting a different value every time increases with the number of possible records.</p> <p>Personally I don't care for the use of LIMIT. The syntax isn't supported on Oracle, SQL Server or DB2 - making queries less portable. LIMIT is a tool to be used conservatively, not the first thing you reach for - know when to use aggregate and/or analytic functions.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload