Re: Need some help with ROWNUM

I think on first glance you might think MySQL has a nice feature that Oracle doesn't. However, I'd probably be right in suggesting that MySQL has to find some criteria for those rownums that is probably as random as the data is stored. So, you would probably be able to mess it up by inserting a pile of rows inbetween 2 different LIMIT statements.

Try this..
create table ATEMPTABLE as select rownum n, rowid from THEMILLIONROWTABLE;

Then select like this..

select * from THEMILLIONROWTABLE
where rowid in
(
select rowid from ATEMPTABLE a
where n < 11000
 and 1000 > (select count(*) from ATEMPTABLE b

          where n < 11000
             and b.n > a.n)

Let me know if this is any faster.. I've never had this requirement before..

On Thu, 8 Feb 2001 10:26:10 -0000, "Stephen Livesey" <ste_at_exact3ex.co.uk> wrote:

>I am trying to work out how to develop a 'browser' (listbox) on a table that
>contains 2million records.
>
>If I try to create a resultset simply using:
> SELECT * FROM table
>As the user moves through the resultset, an out of memory error is
>generated.
>
>So to get round this problem, in mysql I am able to load say the 1st 1000
>records and allow the user to browse through these, when the user reaches
>the last page of records, I can load the next 1000 records, etc.
>
>In MySql I can also sort the file and limit the rows very quickly as
>follows:
> SELECT * FROM table ORDER BY field LIMIT 100000,1000
>
>
>"Doug C" <dcowles_at_i84.net> wrote in message
>news:ngf48tkahuhsb4la4muj6ed4pcc59jk57v_at_4ax.com...
>> Can you help me out a little bit with MySQL?
>> The reason we are doing all the sorting is because the row order does not
 have
>> any significance in and of itself. What is the "need", for lack of a
 better
>> term, of getting rows 100,000 through 101,000? What is significant about
 them?
>> In Oracle, the rows can come out in any order, usually in the order they
 are
>> placed in the table in the first place, but that has no business
 significance
>> usually. When you say you can do this easily with MySQL - what is it
 doing?
>> Is is pre-ordered by some criteria that you are interested in? What makes
 rows
>> 100,000 - through 101,000 any different from 90,000 to 91,000?
>>
>>
>> On Wed, 7 Feb 2001 09:54:55 -0000, "Stephen Livesey" <ste_at_exact3ex.co.uk>
 wrote:
>>
>> >I am having a very similar problem to yours, and your solution works.
>> >
>> >However I am finding it takes quite a while to run.
>> >
>> >I am running it on a database with 2 million records, and I am just
 trying
>> >to select say records 100000 to 1001000.
>> >
>> >I believe it takes a long time to run because of the 'sorting', even
 though
>> >I am using a primary key.
>> >
>> >Is there any way of performing the query without having to sort the data?
>> >I have tried removing the 'order by' commands, but then it does not
 return
>> >the correct results.
>> >
>> >
>> >I can perform the same query using MySQL in a fraction of the time using:
>> > select * from table limit 100000,1000
>> >
>> >
>> >Thanks
>> >Stephen Livesey
>> >
>> >
>> >"Gad Krosner" <krosner_at_excite.com> wrote in message
>> >news:95ovfj$1sa$1_at_nnrp1.deja.com...
>> >> That did (mostly) the trick - thanks. It needs a third sort. Also,
>> >> when going through the last set of results, the 26 needs to be computed
>> >> such that it shows only the remaining rows for the last page. Here's
>> >> the solution (not showing computation for last page - done in the app):
>> >>
>> >> SELECT * FROM (
>> >> SELECT * FROM (
>> >> SELECT * FROM (
>> >> SELECT columns
>> >> FROM tables
>> >> WHERE join conditions, including outer join
>> >> ORDER BY some columns)
>> >> WHERE ROWNUM < 1026
>> >> ORDER BY some columns DESC)
>> >> WHERE ROWNUM < 26)
>> >> ORDER BY some columns
>> >>
>> >>
>> >>
>> >> In article <sntu7t022qo8dvslpufuis7uv3jsjd5tf8_at_4ax.com>,
>> >> Doug C <dcowles_at_i84.net> wrote:
>> >> > Rather than running it twice, how about running it once with
>> >> > select * from
>> >> > (
>> >> > select * from
>> >> > ((
>> >> > SELECT columns
>> >> > FROM tables
>> >> > WHERE join conditions, including outer join
>> >> > ORDER BY some columns
>> >> > )
>> >> > WHERE ROWNUM < 1026
>> >> > )
>> >> > ORDER IT BACKWARDS
>> >> > )
>> >> > WHERE ROWNUM < 26
>> >> >
>> >> > Not sure if I have all the matching parenthesis etc.,
>> >> >
>> >> > On Mon, 05 Feb 2001 18:02:52 GMT, Gad Krosner <krosner_at_excite.com>
 wrote:
>> >> >
>> >> > >Thanks, Steve. I tried that route and it didn't work quite as
 expected.
>> >> > >
>> >> > >For one thing, it didn't return exactly 25 rows. I got either 21
 for
>> >> > >rownum in the range 1001 and 1026, or 27 for rownum between 2001 and
>> >> > >2026.
>> >> > >It also seems to run twice as long because these queries run
>> >> > >independently.
>> >> > >
>> >> > >
>> >> > >In article <t7to4v6mlocv5a_at_corp.supernews.co.uk>,
>> >> > > "Steve Blomeley" <steveblomeleyATyahoo.co.uk> wrote:
>> >> > >> Gad,
>> >> > >>
>> >> > >> How about ...
>> >> > >>
>> >> > >> SELECT * FROM
>> >> > >> (
>> >> > >> SELECT columns
>> >> > >> FROM tables
>> >> > >> WHERE join conditions, including outer join
>> >> > >> ORDER BY some columns
>> >> > >> )
>> >> > >> WHERE ROWNUM < 1026
>> >> > >> #
>> >> > >> MINUS
>> >> > >> #
>> >> > >> SELECT * FROM
>> >> > >> (
>> >> > >> SELECT columns
>> >> > >> FROM tables
>> >> > >> WHERE join conditions, including outer join
>> >> > >> ORDER BY some columns
>> >> > >> )
>> >> > >> WHERE ROWNUM < 1001
>> >> > >>
>> >> > >> hth
>> >> > >> Steve Blomeley
>> >> > >>
>> >> > >>
>> >> > >
>> >> > >
>> >> > >Sent via Deja.com
>> >> > >http://www.deja.com/
>> >> >
>> >> >
>> >>
>> >>
>> >> Sent via Deja.com
>> >> http://www.deja.com/
>> >
>>
>
  Received on Tue Feb 13 2001 - 23:38:15 CST