MySQL's LIMIT with JDBC

[Albretch]

I was wondering about ways to kinda standardize the non SQL92
compliant MySQL-specific "LIMIT" clause

http://dev.mysql.com/doc/

with JDBC.

Say you only need to SELECT a given number of records at a time.

I think (and I am NOT a DB speciallist) that you ease the DB engine's
work if you just tell it:

'I only need the next 3 records';

since it might optimally be a one-time operation.

Maybe this is very QOI (quality of implementation) issue, but I
think, if you iterate over a ResultSet object and then break out of
the look when you have the number of records you need you might tax
the DB eng. more than if you just tell it how many records you need
beforehand.

Am I correct?

Are there ways to stream line this other than simply using MySQL or a
DB that supports it ?

What would you suggest?

 

[Luke Webber]

I was wondering about ways to kinda standardize the non SQL92
compliant MySQL-specific "LIMIT" clause

http://dev.mysql.com/doc/

with JDBC.

Say you only need to SELECT a given number of records at a time.

I think (and I am NOT a DB speciallist) that you ease the DB engine's
work if you just tell it:

'I only need the next 3 records';

since it might optimally be a one-time operation.

Maybe this is very QOI (quality of implementation) issue, but I
think, if you iterate over a ResultSet object and then break out of
the look when you have the number of records you need you might tax
the DB eng. more than if you just tell it how many records you need
beforehand.

Am I correct?

Probably not in any serious way, provided your select is making use of
indices. If it has to pass over an entire table, you're almost certainly
right.

Are there ways to stream line this other than simply using MySQL or a
DB that supports it ?

What would you suggest?

IMO the SAMPLE keyword is generally only of use in testing environments. I
can't see why you'd want to do a select and only retrieve a limited number
of rows unless you could be sure you were retrieving the records in sorted
sequence.

Luke

 

[Christopher Browne]

In an attempt to throw the authorities off his trail, (e-mail address removed) (Albretch) transmitted:

I was wondering about ways to kinda standardize the non SQL92
compliant MySQL-specific "LIMIT" clause

http://dev.mysql.com/doc/

with JDBC.

It's an "open source" database, so presumably you could look at the
source code, see what needs to be changed to make it conform with the
standard, and change the source code to support that.

That's supposedly one of the merits of "open source," right???

 

[Troels Arvin]

On Mon, 03 May 2004 09:26:17 -0700, Albretch wrote:

[...]

I was wondering about ways to kinda standardize the non SQL92
compliant MySQL-specific "LIMIT" clause

http://dev.mysql.com/doc/

with JDBC.

[...]

Actually, SQL:2003 has standardized the area, by its introduction of
"ROW_NUMBER() OVER" and other "window functions". See
http://troels.arvin.dk/db/rdbms/#select-limit

Apart from that: Your message is unclear on what you actually want to
know/do/obtain.

I think (and I am NOT a DB speciallist) that you ease the DB engine's
work if you just tell it:

'I only need the next 3 records';

It sure sounds like it could be relieving for a DBMS to be told that only
a fraction of the result set will be needed. However, LIMIT is most often
useless without an ORDER BY expression, so the effect of LIMIT may not be
as great as you think: The DBMS needs to do some (potentially heavy)
sorting of the whole potential result set anyways.

Oh, and another thing: Judging from usenet postings, it looks like LIMIT
is often misused. Often, people seem to be using LIMIT in situations where
they _really_ want a top-N query. See the previously mentioned link for a
discussion of this issue.

 

[Albretch]

Say you need to query de DB this way:

'SELECT the next 25 records with Record Index bigger then 50 SORT BY Index';

saying

'SELECT records with Record Index bigger then 50 SORT BY Index';

and then break out of the loop when you get 25 records


I think the first options is easier to optimize

By the way I was talking about MySQL as an example