What's faster?

Hi,

Second option.

For the first option, if your query returns say 10,000 rows then php has
to actually fetch 10,000 rows from the database before anything else
happens even though you're only displaying 15.

Silas Justiniano wrote:

Silas Justiniano wrote:

Hello all!

I'm performing a query that returns me hundreds of records... but I
need cut them in pages that have 15 items! (using PHP)

So, is it faster:

select blablabal from _complex_query
if (count($result) > 15) show_pages;
show_only_15_rows($result);

or:

select count(*) from _complex_query
if ($result1 > 15) show_pages;
select blablabal from _complex_query LIMIT ... (see the LIMIT!)
show $result

If you don't care about how many pages you'll get, try:

select blablabal from _complex_query LIMIT (15+1);
if (count($result) > 15) show_pages;
show_only_15_rows($result);

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

//Showing your Vision to the World//

Depending on your keys neither.
Rather let the DB handle the resultset. count(*) is quite slow.

How about something like

select blablabla from _complex_query order by _key_ (optional DESC or ASC)
OFFSET xxx LIMIT 15

where your offset would be a parameter from the php side and is basically the
page number of the number of pages you want to display.
The only drawback of that is that you will never see the total number of hits.
So maybe you do a count(*) ONCE and then use the above query to loop over the
resultset - or you don't show the number of pages and just have a "next
results" and "previous results" button that adjusts the offset parameter.

On Wednesday 08 February 2006 19:45, Silas Justiniano wrote:

Hello all!

I'm performing a query that returns me hundreds of records... but I
need cut them in pages that have 15 items! (using PHP)

So, is it faster:

select blablabal from _complex_query
if (count($result) > 15) show_pages;
show_only_15_rows($result);

or:

select count(*) from _complex_query
if ($result1 > 15) show_pages;
select blablabal from _complex_query LIMIT ... (see the LIMIT!)
show $result

On the first, I can use pg_num_rows instead of count(), too.

what do you think?

Thank you!

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
UC

--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax: +1 707 568 6416

On Thu, Feb 09, 2006 at 10:52:03AM -0800, Uwe C. Schroeder wrote:

Depending on your keys neither.
Rather let the DB handle the resultset. count(*) is quite slow.

How about something like

select blablabla from _complex_query order by _key_ (optional DESC or ASC)
OFFSET xxx LIMIT 15

where your offset would be a parameter from the php side and is basically the
page number of the number of pages you want to display.
The only drawback of that is that you will never see the total number of hits.
So maybe you do a count(*) ONCE and then use the above query to loop over the
resultset - or you don't show the number of pages and just have a "next
results" and "previous results" button that adjusts the offset parameter.

Another possibility is to put a limit of 151. If you get 151 rows you
print 1 2 3 .. 8 9 10 More. If you get less you know how many pages. As
you get to page 5 you can limit to 225+1.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Hi all,

I'm trying to insert more than one t-uple in a single sql query. Just like

copy table (field1,field2,...) from ....

It must be something like

insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...),
(c1,c2,...)

Thanks in advance!

Gonzalo A. Villegas

Hi,

You can't do that in postgres, sorry. That's a mysql-ism.

Gonzalo Villegas wrote:

On Thu, 9 Feb 2006 17:57:03 -0500, "Gonzalo Villegas" <chalo1970@hotmail.com> wrote:

Hi all,

I'm trying to insert more than one t-uple in a single sql query. Just like

copy table (field1,field2,...) from ....

It must be something like

insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...),
(c1,c2,...)

insert into table (field1,field2)
select v1,v2
union all
select b1,b2
union all
select c1,c2

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+

There is a little trick you can do though, it goes something like this:

insert into table (field1, field2, field3) select v1, v2, v3 union
b1, b2, b3 union select c1, c2, c3

I originally did this because it was significantly faster on SQL
Server 2000 than doing the inserts individually. Usually I did it
with up to maybe 20 rows at a time that were all grouped to some kind
of common parent.

Some version of postgres a long time ago broke my code because it did
some stricter type checking and so I had to make sure that I was
never putting single quotes around int and that date fields were
strictly typecasted so that it wouldn't think they were strings. It
does work now though as long as I do that and I use it all the time.
I don't know if it gets the same sort of speed boost in postgres as
it did in sql server. As long as they are all done within a single
transaction in postgres it may not matter whether you do them
individually or batched like that.

If you really have a lot of data you want to insert at once why not
just use COPY?

Rick

On Feb 9, 2006, at 4:13 PM, Chris wrote:

You can't do that in postgres, sorry. That's a mysql-ism.

Gonzalo Villegas wrote:

It must be something like
insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...),
(c1,c2,...)

Actually, that's not a mysql-ism, it's SQL-spec syntax. We haven't got
round to implementing it, partly because the SELECT ... UNION ALL ...
syntax provides a perfectly good substitute. It is on the TODO list
though.

I wouldn't recommend trying to insert more than a few dozen rows with
the UNION ALL approach, else the planner overhead might swamp any
savings. If you want to insert thousands of rows at once, you almost
certainly want to find a way to use COPY.

regards, tom lane

On Thu, Feb 09, 2006 at 07:12:45PM -0500, Tom Lane wrote:

You can't do that in postgres, sorry. That's a mysql-ism.

Gonzalo Villegas wrote:

It must be something like
insert into table (field1,field2,...) values (v1,v2,...),(b1,b2,...),
(c1,c2,...)

Actually, that's not a mysql-ism, it's SQL-spec syntax. We haven't
got round to implementing it, partly because the SELECT ... UNION
ALL ... syntax provides a perfectly good substitute. It is on the
TODO list though.

Another way it's different from COPY is that the VALUES can take
expressions.

What all would need to change in order to implement this? There
appear to be things in src/backend/parser and src/bin/psql that bear
on this. Would libpq and ecpg need to change?

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 415 235 3778

Remember to vote!

Thank you very much. But what do you think of:

select foobar from table where <condition>;
if (pgsql_num_rows($result) > 15) show_pages;
show_only_15($result);

Thank you!

What happens if that query returns 5,000 rows?

Postgres does actually have to fetch all of those rows, and then php has
to allocate memory etc to store them.

It's not very efficient.

Silas Justiniano wrote: