StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>In the worst case, where you're looking at an unindexed field, using <code>MIN()</code> requires a single full pass of the table. Using <code>SORT</code> and <code>LIMIT</code> requires a filesort. If run against a large table, there would likely be a significant difference in percieved performance. As a meaningless data point, <code>MIN()</code> took .36s while <code>SORT</code> and <code>LIMIT</code> took .84s against a 106,000 row table on my dev server.</p> <p>If, however, you're looking at an indexed column, the difference is harder to notice (meaningless data point is 0.00s in both cases). Looking at the output of explain, however, it looks like <code>MIN()</code> is able to simply pluck the smallest value from the index ('Select tables optimized away' and 'NULL' rows) whereas the <code>SORT</code> and <code>LIMIT</code> still needs needs to do an ordered traversal of the index (106,000 rows). The actual performance impact is probably negligible.</p> <p>It looks like <code>MIN()</code> is the way to go - it's faster in the worst case, indistinguishable in the best case, is standard SQL and most clearly expresses the value you're trying to get. The only case where it seems that using <code>SORT</code> and <code>LIMIT</code> would be desirable would be, as <a href="https://stackoverflow.com/users/36902/mson">mson</a> mentioned, where you're writing a general operation that finds the top or bottom N values from arbitrary columns and it's not worth writing out the special-case operation.</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