473,372 Members | 1,018 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

How to get the total number of rows with a query "limit" ?

Hi

I would like to paginate the results of a query on several pages. So I
use a query with a limit X offset Y to display X results on a page,
ok.

But for the first page, I need to run the same query with a count(*)
to know how many pages I will get (number total of rows/ X).

The problem is my query is very slow (maybe 5s) because there is much
worch to do, and on the first page, I need to run this query twice
(not exactly, but ...) so the page is very very slow to load.

My question is : is there a function to get the total number of rows
even on a query with "limit" ? Or what could I do else ?

Has anybody an idea ?
Thanks for the help
Krystoffff
Nov 11 '05 #1
3 7759
On Wed, 2003-08-13 at 08:43, krystoffff wrote:
Hi

I would like to paginate the results of a query on several pages. So I
use a query with a limit X offset Y to display X results on a page,
ok.

But for the first page, I need to run the same query with a count(*)
to know how many pages I will get (number total of rows/ X).

The problem is my query is very slow (maybe 5s) because there is much
worch to do, and on the first page, I need to run this query twice
(not exactly, but ...) so the page is very very slow to load.

My question is : is there a function to get the total number of rows
even on a query with "limit" ? Or what could I do else ?


Presuming that this is your own app, and not psql, why not suck the
result set into a doubly linked list (or dynamic list, if you use
Python, Perl, etc)?

There's also the possibility of "chunked buffers", where you malloc,
say, 8KB before the query runs, and when that gets full, realloc
to add more space, and continue until the query completes.

--
+---------------------------------------------------------------+
| Ron Johnson, Jr. Home: ro***********@cox.net |
| Jefferson, LA USA |
| |
| "Man, I'm pretty. Hoo Hah!" |
| Johnny Bravo |
+---------------------------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 11 '05 #2
Or maybe you could just execute the full query (no limit, no offset),
and you can get the whole row count using PQntuples (C), pg_num_rows
(php), etc.

When you iterate the resultset to show the rows, you just show the rows
that belong to the showed page, and skip the rest.

On Wed, 2003-08-13 at 18:53, Ron Johnson wrote:
On Wed, 2003-08-13 at 08:43, krystoffff wrote:
Hi

I would like to paginate the results of a query on several pages. So I
use a query with a limit X offset Y to display X results on a page,
ok.

But for the first page, I need to run the same query with a count(*)
to know how many pages I will get (number total of rows/ X).

The problem is my query is very slow (maybe 5s) because there is much
worch to do, and on the first page, I need to run this query twice
(not exactly, but ...) so the page is very very slow to load.

My question is : is there a function to get the total number of rows
even on a query with "limit" ? Or what could I do else ?


Presuming that this is your own app, and not psql, why not suck the
result set into a doubly linked list (or dynamic list, if you use
Python, Perl, etc)?

There's also the possibility of "chunked buffers", where you malloc,
say, 8KB before the query runs, and when that gets full, realloc
to add more space, and continue until the query completes.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA/Or2U21dVnhLsBV0RAsDhAJ0e+aku+RQHQR5/IRsJJzK1SuABWwCfZ4QN
eiZ35M8qfJeVNIW73hTrm3s=
=nARV
-----END PGP SIGNATURE-----

Nov 11 '05 #3
Hmmm.... Processing the entire query as such would make the entire
query take longer, at least in my experience. I ran into the same
problem, since count() is an aggregate function you cant actually get it
with the data without putting it in every row. You _can_ do a subselect
which select the count in the same query... On my test case this ran
pretty quick in only 1 query. However, simply running a count at the
top of the page, and then executing the limited query should be very
fast. I took a system with around 115k entries in it (and it was
displaying them all !) and paginated it using LIMIT 40 OFFSET X. The
performance went from approx. 1 min to load to page, to loading in less
than a second. All math operations are handled by the DB, even.
However, this APP is written in LXP, so I'm not sure what the
performance difference there would be.

-Steve
My test case was:

mydb=# SELECT username, (SELECT count(username) AS count FROM users) AS
count FROM users ORDER BY username LIMIT 10 OFFSET 10;
username | count
------------+-------
a | 5678
a96larol | 5678
aaguiar | 5678
aaguy | 5678
aahash | 5678
aalalji | 5678
aamirwahid | 5678
aanaya | 5678
aaniceto | 5678
aapala | 5678

Franco Bruno Borghesi wrote:
Or maybe you could just execute the full query (no limit, no offset),
and you can get the whole row count using PQntuples (C), pg_num_rows
(php), etc.

When you iterate the resultset to show the rows, you just show the
rows that belong to the showed page, and skip the rest.

On Wed, 2003-08-13 at 18:53, Ron Johnson wrote:
/On Wed, 2003-08-13 at 08:43, krystoffff wrote:
Hi

I would like to paginate the results of a query on several pages. So I
use a query with a limit X offset Y to display X results on a page,
ok.

But for the first page, I need to run the same query with a count(*)
to know how many pages I will get (number total of rows/ X).

The problem is my query is very slow (maybe 5s) because there is much
worch to do, and on the first page, I need to run this query twice
(not exactly, but ...) so the page is very very slow to load.

My question is : is there a function to get the total number of rows
even on a query with "limit" ? Or what could I do else ?


Presuming that this is your own app, and not psql, why not suck the
result set into a doubly linked list (or dynamic list, if you use
Python, Perl, etc)?

There's also the possibility of "chunked buffers", where you malloc,
say, 8KB before the query runs, and when that gets full, realloc
to add more space, and continue until the query completes./


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Wm | last post by:
I'm trying to get a handle on the best way to handle setting up my listings so that I display groups of about 25 records per page. I currently have a page that returns over 1,000 names/addresses,...
8
by: joe | last post by:
Can anyone help me out by either telling me how to get to the result i need or by pointing me to some documentation about the following question: A certain query to a database give me eg 100...
0
by: krystoffff | last post by:
Hi I would like to paginate the results of a query on several pages. So I use a query with a limit X offset Y to display X results on a page, ok. But for the first page, I need to run the...
5
by: Jeremy | last post by:
I am relatively inexperienced with SQL, and I am trying to learn how to analyze some data with it. I have a table with the following information. COMPANY ID , DATE, MarektValue I would like...
4
by: mairhtin o'feannag | last post by:
Hello, I have a tablespace striped across three drives, call them 1,2,3, just to be clever. :) I allocated a lot more space (DMS) than I should have, since I didn't know a way to estimate the...
4
by: meganrobertson22 | last post by:
Hi Everyone- I have a question about how to add and then use the "All" selection in a combo box. I am trying to figure out how to: (1) add "All" as a selection to a combo box and then (2)...
1
by: laredotornado | last post by:
Hi, I'm using PHP 4.4.4 on Apache 2 on Fedora Core 5. PHP was installed using Apache's apxs and the php library was installed to /usr/local/php. However, when I set my "error_reporting"...
2
by: contractsup | last post by:
Environment: $ uname -a AIX <withheld2 5 000100614C00 $ db2level DB21085I Instance "<withheld>" uses "32" bits and DB2 code release "SQL08024" with level identifier "03050106"....
3
by: cmsimmers | last post by:
I'm trying to display the available space on a disk drive. This could be tricky if the user choose a drive on which they are limited to a quota. The "FreeSpace" property returns the total number...
5
by: Martin | last post by:
I'm trying to adapt a PHP script that was written to use MySQL, so that it will work with an MSAccess MDB file. An important part of the script makes use of the SQL "LIMIT" keyword available in...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.