Re: formatting of SQL sent by PHP to postgres

Le Jeudi 30 Octobre 2003 21:19, David Green a écrit :
> On Thu, 30 Oct 2003, Scott Marlowe wrote:
> > "It's far more likely that optimizing your SQL queries will yield the
> > greatest increase in performance.  Things like replacing "select max(id)
> > from table" with "select id from table order by id desc limit 1" etc..."
>
> When I first read this I was surprised that this kind of change could even
> make
> a difference. I tested it and it makes a lot of difference.
>
> Ex.
> On a table with 21,000 records I ran 2 queries. One using "Max(Num)" and
> one using the "order by num desc limit 1". The "Max(Num)" query took 51
> msec and the other took 0.09 msec. I tried the same thing on SQL Server and
> the 2 queries run in exactly the same amount of time. Why does it make so
> much of a difference in PostgreSQL? I did notice in the query plan, the
> second query  was able to use the index on the Num field - this may be the
> speed difference..

This is a good thing but remember that will run only if you have an index on
the "Num" Column ... and if you have not null value in the field !
Otherwise you will get a better result with MAX function.

regards,
--
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902