Is there any way to set the bounds of a query (for pagination as an example) using long integers instead of regular integers? Otherwise we're limited to 2 billion rows.

I'm using the following 2 methods to set bounds:


But there aren't equivalent methods that take a long. Do I have to use native SQL?

Even JDBC only has Statements.setMaxresults(int) ....not sure how you expect us to work around that one ?

/max

Before converting my application to using Hibernate instead of simple JDBC, I used SQL code to do this:
http://dev.mysql.com/doc/refman/5.0/en/select.html
LIMIT and OFFSET parameters can have 64-bit integer values

I'm not familiar with how other databases deal with query limits and offsets though. How do you guys work around JDBC's lack of long parameters for setMaxResults? I'm brand new to Hibernate... Is it difficult to modify the resulting SQL code to include the above LIMIT and OFFSET parameters instead of relying on JDBC when a long is passed into setMaxResults/setFirstResult, at least when the dialect is MySQL?

well hibernate actually does this by default for mysql but we don't really have a way to pass a long down to the code handling it (since we need to work with the jdbc api which only allow int)

btw. what kind of table has more than 2 billion rows ? (i'm just curious :)

a Person table has more than 2 billion rows... if you include everybody :). I'm aware of the other issues that can come up at that level, so this is all probably a moot discussion. But I'll do the appropriate testing and see what happens ;).

well how often do you have to iterate through more than 2 billion rows ? you should limit the query down to a more managable size with normal business criteria before applying pure rowcount-filters.

It's not about iterating through many rows. It's about being able to pick a small number of rows out of the middle or end. So the setMaxResults method isn't that important to me. The setFirstResult is much more critical.

This is nonsense. The INDEX ALONE of a 2 billion row table would be several hundred GIGABYTES. Without any data in the table.

Like I said before, I don't know yet about the feasibility of having so many rows in 1 table. Are there no 2 billion row tables in any mysql deployments?

NO, and unless you can show us one, this thread is pretty much a waste of time.

No need for the hostilities. What you think is a ridiculous amount of storage and resources today is not necessarily the case in the near future. And I understand mysql clustering is pretty advanced in these situations, though haven't experimented yet. Mysql allows 64-bit offsets for a reason...

A quick search yields plenty of >2 billion row mysql tables in use:
http://forums.mysql.com/read.php?32,6855,6855 (42billion rows in 2004)
http://www.mysql.com/why-mysql/case-stu ... estudy.pdf (cox's main mysql table is 2 billion rows)

Mysql specifically states that you need the --big-tables compile option if your tables will have more than 4.2billion (unsigned 32-bit value) rows. Binary versions of Mysql are compiled with this option.

I don't know about the issues involved with joining tables like these, their specific resource requirements or other issues... but it certainly seems realistic to have a 2 billion row table.

mueller, no matter if you have a table with 2 billon or more rows then using setFirstResult / setMaxResult to limit the data returned is *not* the right way of doing it - you should limit the possible query result up front by e.g. only getting persons with a certain criteria before even trying to iterate that much data.