Am Dienstag, 11. Oktober 2005 17:11 schrieb Michael Landin Hostbaek: > List, > > I'm using the OFFSET / LIMIT combo in order to split up my query, so it > only parses 20 rows at a time (for my php-scripted webpage). > > I'm using two queries; the first basically doing a select count(*) from > [bla bla]; the second grabbing the actual data while setting LIMIT and > OFFSET. > > In addition, I'm using the first query plus some calculations to parse > total hits to the query, and number of pages etc etc. > > Now, my problem is this, the first query is simply counting all rows > from the main table, whereas the second query has plenty of JOINS, and a > GROUB BY statement - it's a fairly heavy query. The total (reported by > the first query), it not at all the same as the amount of rows returned > by the second query. I'd like to avoid having to run the "heavy" query > twice, just in order to get the number of rows. > > Is there a smarter way of doing it ?
There is a smarter way of asking: Show us the queries! But it also depends on what you expect the user to do. Some hints: In generell if you count table A and afterwards you join and group your tables A,B,C,D the number of rows in the resultset may vary, of course. - You could fetch ALL rows with the second query, count them (pg_numrows), show the first ten results and keep all other results in cache for the next webpage. (if we are talking about a smal set of rows not if we are talking about 1 billion rows, of course) - You can rewrite your first query to return the correct number and see if it has a real performance impact. Optimize our query and you will be fine. Postgresql is very fast. - You can show the user an estimated count, if the correct number isn't of any interest (like google) - If you ever look at the CURSOR thing in postgresql and it looks attractive to you ( http://www.postgresql.org/docs/8.0/static/sql-fetch.html ): I think it isn't useful in a normal web environment, but it could be nice together with AJAX scripting. kind regards, janning ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
