Re: Best way to know if there is a row

O Martin Marques Ýãñáøå óôéò Sep 28, 2004 :

> I have a bunch of queries in a system I'm finishing, and I bumped with a
> question on performace.
> Which is the best way to solve this:
>
> I need to know if there is at least one row in the relation that comes from a
> determinated query. Which is the best way to do this:
>
> (a) Build the query with "SELECT * ..." and after executing the query see if
> numRows()>0
> (b) Build the query with "SELECT count(*) ...", fetch the row and see if
> count>0

You could also try
(c) SELECT exists (select 1 ... limit 1) and test the boolean value
in the one and only row returned, where "..." is your clause
as in (a),(b).

>
> I'm working with (a) because I see it better in performace, but I wanted to be
> sure the numRows() will actually give me the exact amount of rows (had some
> problems in the past with Informix).
>
> The aplication is written in PHP.
>
>

--
-Achilleus


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Best way to know if there is a row

I have a bunch of queries in a system I'm finishing, and I bumped with a
question on performace.
Which is the best way to solve this:

I need to know if there is at least one row in the relation that comes from a
determinated query. Which is the best way to do this:

(a) Build the query with "SELECT * ..." and after executing the query see if
numRows()>0
(b) Build the query with "SELECT count(*) ...", fetch the row and see if
count>0

I'm working with (a) because I see it better in performace, but I wanted to be
sure the numRows() will actually give me the exact amount of rows (had some
problems in the past with Informix).

The aplication is written in PHP.

--
09:45:02 up 16 days, 3 min, 4 users, load average: 3.32, 2.69, 1.77
------------------------------------------------------------ -----
Martín Marqués | select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica | DBA, Programador, Administrador
Universidad Nacional
del Litoral
------------------------------------------------------------ -----

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: Best way to know if there is a row

Martin Marques writes:
> I need to know if there is at least one row in the relation that comes from a
> determinated query. Which is the best way to do this:

> (a) Build the query with "SELECT * ..." and after executing the query see if
> numRows()>0
> (b) Build the query with "SELECT count(*) ...", fetch the row and see if
> count>0

Either of these implies computing the entire query result set, which is
much more computation than you want. Instead do
SELECT * .... LIMIT 1
and then see if you got a row or not. Aside from not computing useless
rows, the LIMIT will bias the optimizer in favor of fast-start plans.

regards, tom lane

---------------------------(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