CODEWITHSUNDEEP
mysqldatabaseoracle-database
Ted Farven
Ted Farven

Reputation: 41

Short Circuiting SQL to Return The First X Rows Found

I know SQL is able to return only a certain number of rows like so:

MySQL: select ... order by num desc limit 10

Oracle SQL: WHERE ROWNUM <= 10 and whatever_else

but I'm under the impression that those execute by finding all the entries that meet your "where" conditions and then only returning a subset of them.

What I want is to tell it, "Give me the first N entries you come across that meet my conditions and stop executing," so that my query will execute really fast if I only want an example of some data in the DB and not all of it.

Does anyone know how to do this in MySQL and/or Oracle SQL? Oracle SQL preferred but any help is appreciated.

Also, what is the correct term for this? The term "short circuiting" describes what I'm looking for, but I'm not sure if it is the official term in regards to databases.

Upvotes: 3

Views: 57

Answers (1)

harvey
harvey

Reputation: 2953

A simple select ... where ... limit ... will stop processing once it's found the necessary items, this optimisation is built into mysql and other engines.

A common optimisation technique is to LIMIT 1 when you know there will be only one match, this prevents the database from doing a full scan.

However, when you include ... order by ... the engine has no choice but to iterate over all items to find the right elements. Even so there are optimisations over not limiting, in your example the database engine may only keep a list of 10 items and pop items out as it finds elements that should be ordered above it.

Upvotes: 4

Related Questions