Hacker News Reader

Am I correct in thinking that if it weren't for the order-by statement in the first query, it would have stopped after the first occurrence?

View on HN

It seems that way to me, certainly.

No. Without any indication that the value in that column is unique the query engine must read the entire table in order to insure that it has found every row that meets the search criteria.

But the original query includes "limit 1". Why is it necessary to find every row meeting the search criterion? Only one will be returned, and without an ORDER BY clause it doesn't matter which one.

Yes, without the limit it will stream only the first matching row.

You appear to be assuming that the limit is applied while fetching tuples, rather than while filtering the tuple set after fetch.

It occurs to me that if you handle LIMIT during fetch, you'll add complexity the fetch, and might only see run time gains in the cases where the number of desired rows is small.

If a column contains unique data it should be marked as such in whatever way your RDBMS requires (e.g. UNIQUE constraint and/or UNIQUE index)

Reply on news.ycombinator.com