Massyn has asked for the wisdom of the Perl Monks concerning the following question:

#!/fellow/monks.pl

I'm using DBI to create some HTML tables through a CGI interface. One of the queries are generating a lot of data, so the resulting HTML gets very big. I want to build a function that will allow me to browse through the records, by only displaying a certain number of records at a time. Now, the easiest way of doing this is by letting DBI get all the records, and just display the ones you want. This is very bad for performance. Is there a way I can ask DBI to display eg. record 20 to 50? Is this a DBI thing or rather a SQL thing? I'm using mySQL and Oracle 8i.

Thanks!

#!/massyn.pl The early worm gets caught by the bird.

Replies are listed 'Best First'.
Re: mySQL query
by PodMaster (Abbot) on Jul 07, 2003 at 07:56 UTC
[reply]
Re: mySQL query
by Abigail-II (Bishop) on Jul 07, 2003 at 08:01 UTC
    It's an SQL thing. Note that for unordered queries, talking about the 20th to the 50th row doesn't make any sense. There's no order, so there's no 20th or 50th. There are some databases out there that allow you to specify "20th to 50th row", but that's not portable, and you can't be sure you get the same results when doing the same query again (if the database would garantee the same results, it would have an implicite order, which would prevent the database from doing all kinds of optimizations).

    The standard way is to use an index, sort the query using the index, and using an appropriate where clause to select the 20th to 50th row. This will give you a repeatable query.

    Abigail

[reply]
Re: mySQL query
by Massyn (Hermit) on Jul 07, 2003 at 08:26 UTC

    GOT IT!

    Create a test table.. Here's the code 

    drop table test_table;
create table test_table (string varchar(10), num integer);
insert into test_table(string,num) values('a',1);
insert into test_table(string,num) values('b',2);
insert into test_table(string,num) values('c',3);
insert into test_table(string,num) values('d',4);
insert into test_table(string,num) values('e',5);
insert into test_table(string,num) values('f',6);
insert into test_table(string,num) values('g',7);
insert into test_table(string,num) values('h',8);
insert into test_table(string,num) values('i',9);
insert into test_table(string,num) values('j',10);

    [download]

    We want to see record 5 to 7..

    For mySQL

    SELECT string, num
FROM test_table limit 4,3

    [download]

    For Oracle

    select t1.string, t1.num from
(select string, num, rownum rn from test_table) t1 
where t1.rn between 5 and 7;

    [download]
[reply]
[d/l]
[select]
      Yes, this will work - but it is not portable: you need a different syntax for different data servers.

      The solution outlined by Abigail-II is generic for any Relational database server, and should be as fast (or faster) with proper indexes.

      Michael

[reply]
Re: mySQL query
by Aurynn (Novice) on Jul 07, 2003 at 08:03 UTC 
    SELECT * FROM myTable WHERE (id > 20) AND (id < 50);

    [download]
    would work rather nicely, but does assume that you're using an autoincrementing value in your tables. Can't think of a way to do it without autoincrements, though
[reply]
[d/l]
      That would return at most 30 rows, not necessarely 30 rows. If rows get deleted over time, the autoincremented values stay the same; they aren't recalculated, or reused.

      Abigail

[reply]

Back to Seekers of Perl Wisdom